Search
Search

# One line in UNION ALL Query for sum total UDF in AR and AR CR

Hi,

I have the following query:

SELECT T0.[U_Test] , Sum(T0.[DocTotal]) as "Total Value by UDF" FROM OINV T0 WHERE T0.[DocDate] >= [%0]  and T0.[DocDate] <=[%1] Group by T0.[U_Testl]

UNION ALL

SELECT T0.[U_Test] , -Sum(T0.[DocTotal]) as "Total Value by UDF" FROM OINV T0 WHERE T0.[DocDate] >= [%0]  and T0.[DocDate] <=[%1] Group by T0.[U_Testl] order by sum(T0.DocTotal)

Now they work fine but i would want to it not to have a separate positive and negative figure because of AR and AR credit.  Ideally if I has total of 100 in AR under UDF and -25 in AR CR, then it should return 75 in one line in same UDF.

I have also a similar issue with Average value over period:

SELECT avg(T0.[DocTotal]) as "Average Order Value over period" FROM OINV T0 WHERE T0.[DocDate] >= [%0]  and T0.[DocDate] <=[%1]

which works fine but I want to UNION/include ORIN in this calculation and have an average in one line of the DocTotal taken into account any AR Credits.

If anyone can advise that will be greatly appreciated.  If there is no way to combine two different document totals it with one line then please also let me know.

Thanks,

##### Unnikrishnan Balanreplied

Can i suggest one thing,

in the credit memo part make count as 0 and then try, i guess then you will get desired result

;WITH InvSummary AS

(

SELECT Sum(T0.DocTotal)[TotalInv],Count(T0.DocEntry)[Count] FROM OINV T0 WHERE T0.DocDate >= [%0]  and T0.DocDate <=[%1]

UNION ALL

SELECT -Sum(T0.DocTotal) [TotalInv],0[Count] FROM ORIN T0 WHERE T0.DocDate >= [%0]  and T0.DocDate <=[%1]

)

Select Sum(A.TotalInv)/Sum(A.Count) [Average]

from InvSummary A

Thanks

Unnikrishnan

1 View this answer in context