Former Member
May 20, 2015 at 10:12 AM

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

248 Views

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,