Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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,

Former Member
replied

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

Please try below query

;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
Not what you were looking for? View more on this topic or Ask a question