Skip to Content
0
Dec 08, 2015 at 01:39 AM

Query Aging Report 50 days past due

39 Views

Hi Everyone,

The query below returns two lines per business partner, one line for the OINV total and one line for the ORIN total (like table one below).


I would like the query to instead return one line per business partner, summing the OINV and ORIN totals (like table two below).

Does anyone know how to do this?

Thanks!

Jeff

TABLE ONE

P Code BP Name Amount 50 Days Past Due 10000 Test BP 17,000.00 10000 Test BP -2,000.00

TABLE TWO

BP Code BP Name Amount 50 Days Past Due 10000 Test BP 15,000.00

Query:

/*AR Invoice 50 Days Past Due 2.0*/

SELECT distinct T0.CardCode, T0.[CardName], sum(T0.[DocTotal]-T0.PaidToDate) AS 'Amount 50 Days Past Due' FROM OINV T0 WHERE datediff (day,T0.DocDate,getdate()) >50 Group By T0.CardCode, T0.CardName

UNION

SELECT distinct ORIN.CardCode, ORIN.[CardName], sum((ORIN.[DocTotal]-ORIN.PaidToDate)*-1) AS 'Amount 50 Days Past Due' FROM ORIN ORIN WHERE datediff (day,ORIN.DocDate,getdate()) >50 Group By ORIN.CardCode, ORIN.CardName