On Apr 30, 6:14=A0pm, b...@[EMAIL PROTECTED]
wrote:
> 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:
> > =A0SELECT ID,
> > =A0 =A0 =A0 =A0 =A0Part#,
> > =A0 =A0 =A0 =A0 =A0Period,
> > =A0 =A0 =A0 =A0 =A0Value,
> > =A0 =A0 =A0 =A0 =A0Dis,
> > =A0 =A0 =A0 =A0 =A0Amt,
> > =A0 =A0 =A0 =A0 =A0sum(NetSaleValue) as NetSaleValue,
> > =A0 =A0 =A0 =A0 =A0sum(NetSaleProfit) as NetSaleProfit,
> > =A0 =A0 =A0 =A0 =A00.00 =A0as Dum1,
> > =A0 =A0 =A0 =A0 =A00.00 =A0as Dum2
> > =A0 =A0 FROM RegMaster a, Parts b
> > Where a.ID =3D 1 and datepart(yy,a.perdio) =3D 2007 and regCode =3D
'148=
7'
> > and
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0a.id =3D b.id AND a.part# =3D
b.part#=
> > GROUP BY a.id,
> > =A0 =A0 =A0 =A0 =A0a.regCode,
> > =A0 =A0 =A0 =A0 =A0a.Period,
> > =A0 =A0 =A0 =A0 =A0a.Value,
> > =A0 =A0 =A0 =A0 =A0a.Dis,
> > =A0 =A0 =A0 =A0 =A0a.Amount
>
> > 1st Output:
> > =3D=3D=3D=3D=3D=3D=3D=3D
> > ID Part# =A0 =A0 =A0 =A0Date =A0 =A0 Values =A0 =A0 =A0 =A0 Dis =A0
=A0 =
Amount =A0NetSValue =A0 =A0 =A0 NetSProfit =A0 =A0 =A0 =A0 =A0Dum1 =A0 =A0
=
=A0 =A0Dum2
> > 1 1487 =A02007/01/01 85058.16 =A0 =A0 =A0 =A0 =A0 =A0 0.00 =A0
=A085058.=
16 =A0 =A0 =A0 =A0170116.32 =A0 =A0 =A0 7045370.52 =A0 =A00.00
> > 0.00
> > 1 1487 =A02007/02/01 94227.31 =A0 =A0 =A0 =A0 =A0 =A0 0.00 =A0
=A094227.=
31 =A0 =A0 =A0 =A0188454.62 =A0 =A0 =A0 6695362.70 =A0 =A00.00
> > 0.00
> > 1 1487 =A02007/03/01 107397.87 =A0 =A00.00 =A0 =A0107397.87 =A0 =A0
=A0 =
214795.74 =A0 =A0 =A0 8771624.18
> > 0.00 =A0 =A00.00
>
> >
------------------------------------------------------------------------=
---=AD----------------------------------------------------------------------=
-----
> > 2nd Select:
> > =A0SELECT ID,
> > =A0 =A0 =A0 =A0 =A0Part#,
> > =A0 =A0 =A0 =A0 =A0Period,
> > =A0 =A0 =A0 =A0 =A0Value,
> > =A0 =A0 =A0 =A0 =A0Dis,
> > =A0 =A0 =A0 =A0 =A0Amt,
> > =A0 =A0 =A0 =A0 =A00.00 =A0as Dum1,
> > =A0 =A0 =A0 =A0 =A00.00 =A0as Dum2,
> > =A0 =A0 =A0 =A0 =A0sum(NetPurchValue) as NetPurchValue,
> > =A0 =A0 =A0 =A0 =A0sum(NetPurchProfit) as NetPurchProfit
> > =A0 =A0 FROM RegMaster a, Parts b
> > Where a.ID =3D 1 and datepart(yy,a.perdio) =3D 2007 and regCode =3D
'148=
7'
> > and
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0a.id =3D b.id AND a.part# =3D
b.part#=
> > GROUP BY a.id,
> > =A0 =A0 =A0 =A0 =A0a.regCode,
> > =A0 =A0 =A0 =A0 =A0a.Period,
> > =A0 =A0 =A0 =A0 =A0a.Value,
> > =A0 =A0 =A0 =A0 =A0a.Dis,
> > =A0 =A0 =A0 =A0 =A0a.Amount
>
> > 2nd Output:
> > =3D=3D=3D=3D=3D=3D=3D=3D
> > ID Part# =A0 =A0 =A0 =A0Date =A0 =A0 Values =A0 =A0 =A0 =A0 Dis =A0
=A0 =
Amount =A0Dumm1 =A0 Dumm2 =A0 NetPValue =A0 =A0 =A0 NetPProfit
> > 1 1487 =A02007/01/01 85058.16 =A0 =A0 =A0 =A0 =A0 =A0 0.00 =A0
=A085058.=
16 =A0 =A0 =A0 =A00.00 =A0 =A00.00 =A0 =A0170116.32
> > 7045370.52
> > 1 1487 =A02007/02/01 94227.31 =A0 =A0 =A0 =A0 =A0 =A0 0.00 =A0
=A094227.=
31 =A0 =A0 =A0 =A00.00 =A0 =A00.00 =A0 =A0188454.62
> > 6695362.70
> > 1 1487 =A02007/03/01 107397.87 =A0 =A00.00 =A0 =A0107397.87 =A0 =A0
=A0 =
0.00 =A0 =A00.00 =A0 =A0214795.74
> > 8771624.18
>
> >
------------------------------------------------------------------------=
---=AD----------------------------------------------------------------------=
-----
>
> > 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? =A0It 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:
>
> =A0 SELECT ID,
> =A0 =A0 =A0 =A0 =A0 Part#,
> =A0 =A0 =A0 =A0 =A0 Period,
> =A0 =A0 =A0 =A0 =A0 Value,
> =A0 =A0 =A0 =A0 =A0 Dis, =A0 =A0 =A0 =A0 =A0Amt,
> =A0 =A0 =A0 =A0 =A0 sum(NetSaleValue) as NetSaleValue,
> =A0 =A0 =A0 =A0 =A0 sum(NetSaleProfit) as NetSaleProfit,
> =A0 =A0 =A0 =A0 =A0sum(NetPurchValue) as NetPurchValue,
> =A0 =A0 =A0 =A0 =A0sum(NetPurchProfit) as NetPurchProfit
> =A0 =A0 =A0 =A0 =A0 0.00 =A0as Dum1,
> =A0 =A0 =A0 =A0 =A0 0.00 =A0as Dum2
> =A0 =A0 =A0FROM RegMaster a, Parts b
> =A0Where a.ID =3D 1 and datepart(yy,a.perdio) =3D 2007 and regCode =3D
'14=
87'
> =A0and
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 a.id =3D b.id AND a.part# =3D
b.part#
> =A0GROUP BY a.id,
> =A0 =A0 =A0 =A0 =A0 a.regCode,
> =A0 =A0 =A0 =A0 =A0 a.Period,
> =A0 =A0 =A0 =A0 =A0 a.Value,
> =A0 =A0 =A0 =A0 =A0 a.Dis,
> =A0 =A0 =A0 =A0 =A0 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
|
