On Apr 30, 2:46 am, Waleed Seada <waleed.se...@[EMAIL PROTECTED]
> wrote:
> Dear All,
>
> I have created two select statments, each produce three lines with
> this layout:
>
> 1st Select:
> SELECT ID,
> Part#,
> Period,
> Value,
> Dis,
> Amt,
> sum(NetSaleValue) as NetSaleValue,
> sum(NetSaleProfit) as NetSaleProfit,
> 0.00 as Dum1,
> 0.00 as Dum2
> FROM RegMaster a, Parts b
> Where a.ID = 1 and datepart(yy,a.perdio) = 2007 and regCode = '1487'
> and
> a.id = b.id AND a.part# = b.part#
> GROUP BY a.id,
> a.regCode,
> a.Period,
> a.Value,
> a.Dis,
> a.Amount
>
> 1st Output:
> ========
> ID Part# Date Values Dis Amount NetSValue
NetSProfit Dum1 Dum2
> 1 1487 2007/01/01 85058.16 0.00 85058.16
170116.32 7045370.52 0.00
> 0.00
> 1 1487 2007/02/01 94227.31 0.00 94227.31
188454.62 6695362.70 0.00
> 0.00
> 1 1487 2007/03/01 107397.87 0.00 107397.87 214795.74
8771624.18
> 0.00 0.00
>
>
------------------------------------------------------------------------------------------------------------------------------------------------------
> 2nd Select:
> SELECT ID,
> Part#,
> Period,
> Value,
> Dis,
> Amt,
> 0.00 as Dum1,
> 0.00 as Dum2,
> sum(NetPurchValue) as NetPurchValue,
> sum(NetPurchProfit) as NetPurchProfit
> FROM RegMaster a, Parts b
> Where a.ID = 1 and datepart(yy,a.perdio) = 2007 and regCode = '1487'
> and
> a.id = b.id AND a.part# = b.part#
> GROUP BY a.id,
> a.regCode,
> a.Period,
> a.Value,
> a.Dis,
> a.Amount
>
> 2nd Output:
> ========
> ID Part# Date Values Dis Amount Dumm1 Dumm2
NetPValue NetPProfit
> 1 1487 2007/01/01 85058.16 0.00 85058.16 0.00
0.00 170116.32
> 7045370.52
> 1 1487 2007/02/01 94227.31 0.00 94227.31 0.00
0.00 188454.62
> 6695362.70
> 1 1487 2007/03/01 107397.87 0.00 107397.87 0.00 0.00
214795.74
> 8771624.18
>
>
------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Now I want to combine then together and have aresult of three lines
> with the values of NetSaleValue, NetSaleProfit, NetPurchValue,
> NetPurchProfit in ONE line ....
>
> How can I acomplish that ...
>
> Thanks and best regards,
> Waleed
Am I missing something? It appears that your FROM, WHERE, and GROUP
BY clauses
are identical, so you should just be able to move over the sum()s from
one query to the other:
SELECT ID,
Part#,
Period,
Value,
Dis, Amt,
sum(NetSaleValue) as NetSaleValue,
sum(NetSaleProfit) as NetSaleProfit,
sum(NetPurchValue) as NetPurchValue,
sum(NetPurchProfit) as NetPurchProfit
0.00 as Dum1,
0.00 as Dum2
FROM RegMaster a, Parts b
Where a.ID = 1 and datepart(yy,a.perdio) = 2007 and regCode = '1487'
and
a.id = b.id AND a.part# = b.part#
GROUP BY a.id,
a.regCode,
a.Period,
a.Value,
a.Dis,
a.Amount
|
