Invoice Aging based on Term

there is no attachment.

oh & please note that currently there is a 40 character limit on filenames (including extension)

Hi Paul,

I probably forgot to put a name.

Thanks.
SalesData.xlsx

Sales Table
Columns: CustomerID, Due Amount, Date, Term

Expected Output: Current, over15, Over30, Over60, Over75, Over90, Over120

Thanks.

Hi  ScottPletcher,

That looks good and very clean. Can you revise it to consider the age counting after the Term (Number of days)?


Thanks.

>>"consider the age counting after the Term"

can you explain what you mean by providing an example?
(don't assume we understand your words)

and, don't forget to tell use where we get "after the term" from?
(i.e. in the sample data I dob't see anything more then one date column)

Hi Paul,

If the customer has a term of 30 days, that means the aging will only start on the 31st day and counting onward. However, if the term is zero (o) day, the aging starts immediately. So the current is equals to Term + 15 days from date of Sales transaction.

I hope that makes sense.

Thanks.

There really is a reason for asking you to explain "using examples"
words alone are never as good for an explanation as fully worked examples

try amending the code by Scott by deducting term. i.e.

CROSS APPLY (
    SELECT DATEDIFF(DAY, tn.Date, GETDATE()) - [Term] AS days_old
) AS assign_alias_names

But somehow I doubt that is what you really want.

EXAMPLES please

Hi Paul,

Please check on the attached sample db data script.
SampleSales-0818.sql

ScottPletcher,


I tried your solution with the Test table sample and it has an error as shown in the attachment.

Any idea?

Thanks.SampleSales-0818.sql

Great! Thank you very much Paul &  ScottPletcher for a very professional solution.