How To Calculate Tables Across SELECT Statements

[Former Member]

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

[kvbalakumar · Accepted solution]

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

[Former Member · Accepted solution]

Thanks Bala, but that's a no go.

[kvbalakumar · Accepted solution]

What error message you are getting???

Regards,

Bala

[Former Member · Accepted solution]

Incorrect syntax near ']'

[kvbalakumar · Accepted solution]

Hi,

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 [SOKG], 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 [INVKG], 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

[Former Member · Accepted solution]

Thank you Bala, interesting re-working of the query, it is yielding the same result as my query. It is adding a new column for "your text", but not filling in the calculation, the field is empty. Seems very close, thank you.

[kvbalakumar · Accepted solution]

Hi Justice,

Here is the query....

Just try this too...

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 sum(A.[INV KG]) [INV KG], sum(A.[INV JPY]) [INV JPY], sum(A.[INVKG]) [INVKG],sum(A.[SOKG]) [SOKG], (sum(A.INVKG) / sum(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], 0 as [SOKG], 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', 0 as [INVKG], 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

[Former Member · Accepted solution]

Thank you Bala.

It's still not doing the final calculation, results in zero.

[kvbalakumar · Accepted solution]

Hi Justice,

Can you please post your output of the query here.....

Regards,

Bala

[Former Member · Accepted solution]

Hi Bala, this is the result, i changed some of the numbers )

Notice 'Your Text' is 0.

INV KG INV JPY INVKG SOKG Your Text

525 458 8 7 0

Thank you,

Justice

Edited by: Justice Nerenberg on May 6, 2011 9:01 AM

[Former Member · Accepted solution]

Hi Bala,

I changed some of your code a bit, seems to be working now.

Again, not sure how you made this, but it's good.

Is this standard SQL or.....?

This worked, note main difference is (sum(A.INVKG) * sum(A.SOKG)) + sum(A.[INV JPY]) as 'Estimated Turnover'

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 sum(A.[INV KG]) [INV KG], sum(A.[INV JPY]) [INV JPY], sum(A.[INVKG]) [INV JPY / KG],sum(A.[SOKG]) [Open SO KG], (sum(A.INVKG) * sum(A.SOKG)) + sum(A.[INV JPY]) as 'Estimated Turnover' from (SELECT sum(T0.Quantity) as 'INV KG', sum(T0.LineTotal) as 'INV JPY', (sum(T0.LineTotal) / sum(T0.Quantity)) [INVKG], 0 as [SOKG], 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', 0 as [INVKG], 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.[DocStatus] = 'O' AND T2.CANCELED = 'N')

A

Many thanks,

Justice