cancel
Showing results for 
Search instead for 
Did you mean: 

Collection query

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (1)

Answers (1)

julie_jamieson2
Active Contributor
0 Kudos

If the query is giving you the data you want, try using it as a command in a Crystal Report, then you can format the report as you wish