On Apr 30, 6:14*pm, b...@sybase.com wrote:
> On Apr 30, 2:46 am, Waleed Seada <waleed.se...@yahoo.com> 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- Hide quoted text -
>
> - Show quoted text -

Dear All,
I am sorry for the missleading ... the second select should join
Regmaster with Spars b not Parts
as I mention at the begining, we have three tables ... RagMaster,
Parts, Spars

Bets regards,
Waleed