Query Aging Report 50 days past due

[former_member587037]

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

[KennedyT21 · Accepted solution]

Hi Jeff,

Try this

Select TT.CardCode,TT.CardName,sUM(TT.INV+TT.CN) aS 'Amount 50 Days Past Due'   FROM  (
SELECT distinct T0.CardCode, T0.[CardName], sum(T0.[DocTotal]-T0.PaidToDate) AS 'INV',0 AS 'CN' FROM OINV T0 WHERE datediff (day,T0.DocDate,getdate()) >50 Group By T0.CardCode, T0.CardName
UNION
SELECT distinct ORIN.CardCode, ORIN.[CardName],0, sum((ORIN.[DocTotal]-ORIN.PaidToDate)*-1) AS 'CN' FROM ORIN ORIN WHERE datediff (day,ORIN.DocDate,getdate()) >50 Group By ORIN.CardCode, ORIN.CardName ) as TT
GROUP BY TT.CardCode,TT.CardName

Cheers

Kennedy

[former_member587037 · Accepted solution]

Thank you Kennedy, this works perfectly!

[KennedyT21 · Accepted solution]

can i get ur mail id

[Former Member · Accepted solution]

Dear Sir,

In the above query can you please few changes if possible

1.we want exclude few customers(our inter branch)we use for internal stock transfer.

2.Sales Person and if possible please add golden arrow

[KennedyT21 · Accepted solution]

As per forum rules open a new thread to get experts answers . dont post on the close thread