Skip to Content
0
Former Member
May 12, 2016 at 09:24 PM

Collection query

29 Views

Hi all!

ANy help is really appreciated!

This query tells me which invoices I called or emailed because the invoice was past due. I basically want to see a monthly summary for our collection efforts. I would like to see something similar to the aging report result grid at the bottom of a customer receivables aging report, but instead of getting percentages in relation to the amount due on a specific day, I specifically would like to see a comparison of what I collected at the end of this month with what was my total balance due at the end of last month. Ex. On 04/30/16, the balance due was $100,000. I collected $10,000 between 05/1/16-05/31/16, $2,000 were 0-30 days; $3,000 was 31-60 days, etc. I want the query to be able to calculate these percentages in my aging columns as well as a grand total.

I would also like to add totals to the bottom of my columns called total, 0-30, 31-60,61-90, 121+ of what I collected in any given month.

This query tells me which invoices I called or emailed because the invoice was past due. I basically want to see a monthly summary for our collection efforts. I would like to see something similar to the aging report result grid at the bottom of a customer receivables aging report, but instead of getting percentages in relation to the amount due on a specific day, I specifically would like to see a comparison of what I collected at the end of this month with what was my total balance due at the end of last month. Ex. On 04/30/16, the balance due was $100,000. I collected $10,000 between 05/1/16-05/31/16, $2,000 were 0-30 days; $3,000 was 31-60 days, etc. I want the query to be able to calculate these percentages in my aging columns as well as a grand total.

I would also like to add totals to the bottom of my columns called total, 0-30, 31-60,61-90, 121+ of what I collected in any given month.

SELECT

T2.DocDate 'Pay Date'

, T0.CardName 'Customer Name'

, T0.[DocNum] 'Invoice#'

, T0.DocDate 'Invoice Date'

, T0.DocDueDate

, DateDiff(d,T0.DocDueDate,T2.DocDate) 'Overdue Days'

, T0.DocTotal 'Total'

, CASE WHEN DateDiff(d,T0.DocDueDate,T2.DocDate) <=30 THEN T0.DocTotal ELSE 0 END '0-30'

, CASE WHEN DateDiff(d,T0.DocDueDate,T2.DocDate) >30 AND DateDiff(d,T0.DocDueDate,T2.DocDate) <=60 THEN T0.DocTotal ELSE 0 END '31-60'

, CASE WHEN DateDiff(d,T0.DocDueDate,T2.DocDate) >60 AND DateDiff(d,T0.DocDueDate,T2.DocDate) <=90 THEN T0.DocTotal ELSE 0 END '61-90'

, CASE WHEN DateDiff(d,T0.DocDueDate,T2.DocDate) >90 AND DateDiff(d,T0.DocDueDate,T2.DocDate) <=120 THEN T0.DocTotal ELSE 0 END '91-120'

, CASE WHEN DateDiff(d,T0.DocDueDate,T2.DocDate) >120 THEN T0.DocTotal ELSE 0 END '121+'

, T3.Free_Text 'Remarks'

FROM

[dbo].[OINV] T0

LEFT JOIN [dbo].[RCT2] T1 ON T1.[DocEntry] = T0.[DocEntry]

LEFT JOIN [dbo].[ORCT] T2 ON T2.[DocNum] = T1.[DocNum]

INNER JOIN [dbo].[OCRD] T3 ON T3.[CardCode] = T0.[CardCode]

WHERE

T2.DocDate>=[%0]

AND T2.DocDate <=[%1]

AND T2.Canceled='N'

AND T0.Canceled='N'

Order By T2.DocDate,T0.CardCode,T0.[DocDate] Desc

SELECT

T2.DocDate 'Pay Date'

, T0.CardName 'Customer Name'

, T0.[DocNum] 'Invoice#'

, T0.DocDate 'Invoice Date'

, T0.DocDueDate

, DateDiff(d,T0.DocDueDate,T2.DocDate) 'Overdue Days'

, T0.DocTotal 'Total'

, CASE WHEN DateDiff(d,T0.DocDueDate,T2.DocDate) <=30 THEN T0.DocTotal ELSE 0 END '0-30'

, CASE WHEN DateDiff(d,T0.DocDueDate,T2.DocDate) >30 AND DateDiff(d,T0.DocDueDate,T2.DocDate) <=60 THEN T0.DocTotal ELSE 0 END '31-60'

, CASE WHEN DateDiff(d,T0.DocDueDate,T2.DocDate) >60 AND DateDiff(d,T0.DocDueDate,T2.DocDate) <=90 THEN T0.DocTotal ELSE 0 END '61-90'

, CASE WHEN DateDiff(d,T0.DocDueDate,T2.DocDate) >90 AND DateDiff(d,T0.DocDueDate,T2.DocDate) <=120 THEN T0.DocTotal ELSE 0 END '91-120'

, CASE WHEN DateDiff(d,T0.DocDueDate,T2.DocDate) >120 THEN T0.DocTotal ELSE 0 END '121+'

, T3.Free_Text 'Remarks'

FROM

[dbo].[OINV] T0

LEFT JOIN [dbo].[RCT2] T1 ON T1.[DocEntry] = T0.[DocEntry]

LEFT JOIN [dbo].[ORCT] T2 ON T2.[DocNum] = T1.[DocNum]

INNER JOIN [dbo].[OCRD] T3 ON T3.[CardCode] = T0.[CardCode]

WHERE

T2.DocDate>=[%0]

AND T2.DocDate <=[%1]

AND T2.Canceled='N'

AND T0.Canceled='N'

Order By T2.DocDate,T0.CardCode,T0.[DocDate] Desc