Dear Friends,
Does anybody know the secret to calculating tables across SELECT statements? For example, if I need to divide T0 in the first SELECT by T3 in the second SELECT?
This is the actual query i'm working on:
SELECT sum(T0.[Quantity]) as 'INV KG', sum(T0.[LineTotal]) as 'INV JPY', (sum(T0.[LineTotal]) / sum(T0.[Quantity])) as 'INV JPY/KG', NULL as 'SO KG', NULL as 'SO JPY' FROM INV1 T0 inner join OINV T3 on T0.[DocEntry]
= T3.[DocEntry] WHERE T0.[DocDate] >=[%0] and T0.[DocDate] <= [%1] AND T3.[CANCELED] = 'N' AND T0.[Quantity] > .001 union all
SELECT NULL as 'INV KG', NULL as 'INV JPY', NULL as 'INV JPY/KG', sum(T1.[Quantity]) as 'SO KG', sum(T1.[LineTotal]) as 'SO JPY' FROM RDR1 T1 inner join ORDR T2 on T1.[DocEntry]
= T2.[DocEntry] WHERE T2.[DocDate] >=[%0] and T2.[DocDate] <= [%1] AND T2.[CANCELED] = 'N'
In the second SELECT, i need to be able to perform this calculation, but i get an error:
(sum(T0.[LineTotal]) / sum(T0.[Quantity]) * sum(T1.[Quantity]))
Any help or advice is greatly appreciated.
Thank you,
Justice
Try this
Declare @from as datetime Declare @to as datetime Set @from = /* Select min(T5.DocDate) from OINM T5 where T5.DocDate = */ '[%0]' Set @to = /* Select max(T6.DocDate) from OINM T6 where T6.DocDate = */ '[%1]' Select *, (A.INVKG) / (A.SOKG) as 'Your Text' from (SELECT sum(T0.Quantity) as 'INV KG', sum(T0.LineTotal) as 'INV JPY', (sum(T0.LineTotal) / sum(T0.Quantity)) [INVKG], NULL as 'SO KG', NULL as 'SO JPY' FROM INV1 T0 inner join OINV T3 on T0.DocEntry = T3.DocEntry WHERE T0.DocDate >=@from and T0.DocDate <=@to AND T3.CANCELED = 'N' AND T0.Quantity > .001 union all SELECT NULL as 'INV KG', NULL as 'INV JPY', NULL as 'INV JPY/KG', sum(T1.Quantity) [SOKG], sum(T1.LineTotal) as 'SO JPY' FROM RDR1 T1 inner join ORDR T2 on T1.DocEntry = T2.DocEntry WHERE T2.DocDate >=@from and T2.DocDate <=@to] AND T2.CANCELED = 'N') A
Regards,
Bala
Add a comment