Aging by due date rather than doc date issue

[Former Member]

I am not understanding what I am doing wrong here.... I am using the AR aging query that I have found listed on this site many times and it works PERFECT (Thank you ). Now I have been asked to make the query go by DUE DATE not DOC DATE. So I think it should be easy and replace the aging portion with duedate instead of docdate. Now my total adds up, but the aging portion is totally OFF... Can anyone tell me where it is that I am going wrong with my aging queries below?

Thank you...

What I use for BY DOC DATE (works perfect and all buckets align to the system version)

CASE WHEN (DATEDIFF(dd, l.TaxDate, CURRENT_TIMESTAMP))

< 31 THEN CASE WHEN l.Credit <> 0 THEN l.Credit * - 1 ELSE l.Debit END END AS [0-30 days], CASE WHEN ((datediff(dd, l.TaxDate,

CURRENT_TIMESTAMP)) > 30 AND (datediff(dd, l.TaxDate, CURRENT_TIMESTAMP)) < 61)

THEN CASE WHEN l.Credit <> 0 THEN l.Credit * - 1 ELSE l.Debit END END AS [31 to 60 days], CASE WHEN ((datediff(dd, l.TaxDate,

CURRENT_TIMESTAMP)) > 60 AND (datediff(dd, l.TaxDate, CURRENT_TIMESTAMP)) < 91)

THEN CASE WHEN l.Credit <> 0 THEN l.Credit * - 1 ELSE l.Debit END END AS [61 to 90 days], CASE WHEN ((datediff(dd, l.TaxDate,

CURRENT_TIMESTAMP)) > 90 AND (datediff(dd, l.TaxDate, CURRENT_TIMESTAMP)) < 121)

THEN CASE WHEN l.Credit <> 0 THEN l.Credit * - 1 ELSE l.Debit END END AS [91 to 120 days], CASE WHEN ((datediff(dd, l.TaxDate,

CURRENT_TIMESTAMP)) > 120 AND (datediff(dd, l.TaxDate, CURRENT_TIMESTAMP)) < 181)

THEN CASE WHEN l.Credit <> 0 THEN l.Credit * - 1 ELSE l.Debit END END AS [121 to 180 days], CASE WHEN ((datediff(dd, l.TaxDate,

CURRENT_TIMESTAMP)) > 180 AND (datediff(dd, l.TaxDate, CURRENT_TIMESTAMP)) < 366)

THEN CASE WHEN l.Credit <> 0 THEN l.Credit * - 1 ELSE l.Debit END END AS [181 to 1 Year], CASE WHEN ((datediff(dd, l.TaxDate,

CURRENT_TIMESTAMP)) > 365 AND (datediff(dd, l.TaxDate, CURRENT_TIMESTAMP)) < 545)

THEN CASE WHEN l.Credit <> 0 THEN l.Credit * - 1 ELSE l.Debit END END AS [1 Year to 1.5 Years], CASE WHEN ((datediff(dd, l.TaxDate,

CURRENT_TIMESTAMP)) > 544 AND (datediff(dd, l.TaxDate, CURRENT_TIMESTAMP)) < 731)

THEN CASE WHEN l.Credit <> 0 THEN l.Credit * - 1 ELSE l.Debit END END AS [1.5 Years to 2 Years], CASE WHEN (DATEDIFF(dd, l.TaxDate,

CURRENT_TIMESTAMP)) > 730 THEN CASE WHEN l.Credit = 0 THEN l.Debit WHEN l.Debit = 0 THEN l.Credit * - 1 END END AS [2 Years and Greater]

What I try and use for DUE DATE (Total aligns, but monthly buckets are way off)

CASE WHEN (DATEDIFF(dd, l.DueDate, CURRENT_TIMESTAMP))

< 31 THEN CASE WHEN l.Credit <> 0 THEN l.Credit * - 1 ELSE l.Debit END END AS [0-30 days], CASE WHEN ((datediff(dd, l.DueDate,

CURRENT_TIMESTAMP)) > 30 AND (datediff(dd, l.DueDate, CURRENT_TIMESTAMP)) < 61)

THEN CASE WHEN l.Credit <> 0 THEN l.Credit * - 1 ELSE l.Debit END END AS [31 to 60 days], CASE WHEN ((datediff(dd, l.DueDate,

CURRENT_TIMESTAMP)) > 60 AND (datediff(dd, l.DueDate, CURRENT_TIMESTAMP)) < 91)

THEN CASE WHEN l.Credit <> 0 THEN l.Credit * - 1 ELSE l.Debit END END AS [61 to 90 days], CASE WHEN ((datediff(dd, l.DueDate,

CURRENT_TIMESTAMP)) > 90 AND (datediff(dd, l.DueDate, CURRENT_TIMESTAMP)) < 121)

THEN CASE WHEN l.Credit <> 0 THEN l.Credit * - 1 ELSE l.Debit END END AS [91 to 120 days], CASE WHEN ((datediff(dd, l.DueDate,

CURRENT_TIMESTAMP)) > 120 AND (datediff(dd, l.DueDate, CURRENT_TIMESTAMP)) < 181)

THEN CASE WHEN l.Credit <> 0 THEN l.Credit * - 1 ELSE l.Debit END END AS [121 to 180 days], CASE WHEN ((datediff(dd, l.DueDate,

CURRENT_TIMESTAMP)) > 180 AND (datediff(dd, l.DueDate, CURRENT_TIMESTAMP)) < 366)

THEN CASE WHEN l.Credit <> 0 THEN l.Credit * - 1 ELSE l.Debit END END AS [181 to 1 Year], CASE WHEN ((datediff(dd, l.DueDate,

CURRENT_TIMESTAMP)) > 365 AND (datediff(dd, l.DueDate, CURRENT_TIMESTAMP)) < 545)

THEN CASE WHEN l.Credit <> 0 THEN l.Credit * - 1 ELSE l.Debit END END AS [1 Year to 1.5 Years], CASE WHEN ((datediff(dd, l.DueDate,

CURRENT_TIMESTAMP)) > 544 AND (datediff(dd, l.DueDate, CURRENT_TIMESTAMP)) < 731)

THEN CASE WHEN l.Credit <> 0 THEN l.Credit * - 1 ELSE l.Debit END END AS [1.5 Years to 2 Years], CASE WHEN (DATEDIFF(dd, l.DueDate,

CURRENT_TIMESTAMP)) > 730 THEN CASE WHEN l.Credit = 0 THEN l.Debit WHEN l.Debit = 0 THEN l.Credit * - 1 END END AS [2 Years and Greater]

[Former Member]

This snapshot is part of your query, drawing data from table I, what is table I drawing from?