Invoice Aging based on Term

Dear Experts,

I am looking for a new and efficient way of dynamic reporting of Aging Invoices using SQL 2012 -up.
I would appreciate anyone who can share one that can be applied on the attached sample.

Thanks.
JimiJ13I T ConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
there is no attachment.

oh & please note that currently there is a 40 character limit on filenames (including extension)
JimiJ13I T ConsultantAuthor Commented:
Hi Paul,

I probably forgot to put a name.

Thanks.
SalesData.xlsx
JimiJ13I T ConsultantAuthor Commented:
Sales Table
Columns: CustomerID, Due Amount, Date, Term

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

Thanks.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Scott PletcherSenior DBACommented:
SELECT CustomerID,
    SUM(CASE WHEN days_old < 16 THEN [Due Amount] ELSE 0 END) AS Current,
    SUM(CASE WHEN days_old BETWEEN 16 AND 30 THEN [Due Amount] ELSE 0 END) AS Over15,
    SUM(CASE WHEN days_old BETWEEN 31 AND 60 THEN [Due Amount] ELSE 0 END) AS Over30,
    SUM(CASE WHEN days_old BETWEEN 61 AND 90 THEN [Due Amount] ELSE 0 END) AS Over60,
    SUM(CASE WHEN days_old BETWEEN 91 AND 120 THEN [Due Amount] ELSE 0 END) AS Over90,
    SUM(CASE WHEN days_old >= 121 THEN [Due Amount] ELSE 0 END) AS Over120
FROM dbo.table_name tn
CROSS APPLY (
    SELECT DATEDIFF(DAY, tn.Date, GETDATE()) AS days_old
) AS assign_alias_names
GROUP BY CustomerID
ORDER BY CustomerID
JimiJ13I T ConsultantAuthor Commented:
Hi  ScottPletcher,

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


Thanks.
PortletPaulEE Topic AdvisorCommented:
>>"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)
JimiJ13I T ConsultantAuthor Commented:
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.
PortletPaulEE Topic AdvisorCommented:
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
JimiJ13I T ConsultantAuthor Commented:
Hi Paul,

Please check on the attached sample db data script.
SampleSales-0818.sql
JimiJ13I T ConsultantAuthor Commented:
ScottPletcher,


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

Any idea?

Thanks.ErrorSampleSales-0818.sql
PortletPaulEE Topic AdvisorCommented:
"Current" or [Current] needs to be used, see Reserved Keywords (Transact-SQL)

Also adjusting for the table & column names:
SELECT
      CustomerID
    , SUM(CASE
            WHEN days_old < 16 THEN [AmountDue]
            ELSE 0
      END) AS [Current]
    , SUM(CASE
            WHEN days_old BETWEEN 16 AND 30 THEN [AmountDue]
            ELSE 0
      END) AS Over15
    , SUM(CASE
            WHEN days_old BETWEEN 31 AND 60 THEN [AmountDue]
            ELSE 0
      END) AS Over30
    , SUM(CASE
            WHEN days_old BETWEEN 61 AND 90 THEN [AmountDue]
            ELSE 0
      END) AS Over60
    , SUM(CASE
            WHEN days_old BETWEEN 91 AND 120 THEN [AmountDue]
            ELSE 0
      END) AS Over90
    , SUM(CASE
            WHEN days_old >= 121 THEN [AmountDue]
            ELSE 0
      END) AS Over120
FROM dbo.sales tn
CROSS APPLY (
      SELECT
            DATEDIFF(DAY, tn.SDate, GETDATE()) - tn.term AS days_old
) AS assign_alias_names
GROUP BY CustomerID
ORDER BY CustomerID
;

Open in new window

produces this result:
| CustomerID | Current | Over15 | Over30 | Over60 | Over90 | Over120 |
|------------|---------|--------|--------|--------|--------|---------|
|          1 |    3000 |      0 |      0 |      0 |      0 |    3000 |
|          2 |       0 |      0 |   3000 |      0 |      0 |    5000 |
|          3 |    3000 |      0 |      0 |      0 |      0 |    5000 |
|          4 |       0 |      0 |   3000 |      0 |      0 |    5000 |

Open in new window

Note I have included my suggestion to deduct [Term] in the query.

see: http://sqlfiddle.com/#!3/659792/1

sample data:
CREATE TABLE [dbo].[Sales]
    (
    [SalesID] [int] NOT NULL IDENTITY(1, 1),
    [CustomerID] [int] NULL,
    [SDate] [date] NULL,
    [AmountDue] [money] NULL,
    [Term] [tinyint] NULL
    )
;

ALTER TABLE [dbo].[Sales] ADD CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED  ([SalesID])
;


SET IDENTITY_INSERT [dbo].[Sales] ON
;
INSERT INTO [dbo].[Sales] ([SalesID], [CustomerID], [SDate], [AmountDue], [Term]) VALUES (1, 1, '2014-01-25', 1000.0000, 90)
INSERT INTO [dbo].[Sales] ([SalesID], [CustomerID], [SDate], [AmountDue], [Term]) VALUES (2, 1, '2014-05-02', 1000.0000, 90)
INSERT INTO [dbo].[Sales] ([SalesID], [CustomerID], [SDate], [AmountDue], [Term]) VALUES (3, 1, '2014-06-15', 1000.0000, 90)
INSERT INTO [dbo].[Sales] ([SalesID], [CustomerID], [SDate], [AmountDue], [Term]) VALUES (4, 1, '2015-06-05', 3000.0000, 90)
INSERT INTO [dbo].[Sales] ([SalesID], [CustomerID], [SDate], [AmountDue], [Term]) VALUES (5, 2, '2014-01-25', 3000.0000, 15)
INSERT INTO [dbo].[Sales] ([SalesID], [CustomerID], [SDate], [AmountDue], [Term]) VALUES (6, 2, '2014-05-02', 1000.0000, 15)
INSERT INTO [dbo].[Sales] ([SalesID], [CustomerID], [SDate], [AmountDue], [Term]) VALUES (7, 2, '2014-06-15', 1000.0000, 15)
INSERT INTO [dbo].[Sales] ([SalesID], [CustomerID], [SDate], [AmountDue], [Term]) VALUES (8, 2, '2015-06-05', 3000.0000, 15)
INSERT INTO [dbo].[Sales] ([SalesID], [CustomerID], [SDate], [AmountDue], [Term]) VALUES (9, 3, '2014-01-25', 3000.0000, 60)
INSERT INTO [dbo].[Sales] ([SalesID], [CustomerID], [SDate], [AmountDue], [Term]) VALUES (10, 3, '2014-05-02', 1000.0000, 60)
INSERT INTO [dbo].[Sales] ([SalesID], [CustomerID], [SDate], [AmountDue], [Term]) VALUES (11, 3, '2014-06-15', 1000.0000, 60)
INSERT INTO [dbo].[Sales] ([SalesID], [CustomerID], [SDate], [AmountDue], [Term]) VALUES (12, 3, '2015-06-05', 3000.0000, 60)
INSERT INTO [dbo].[Sales] ([SalesID], [CustomerID], [SDate], [AmountDue], [Term]) VALUES (13, 4, '2014-01-25', 3000.0000, 30)
INSERT INTO [dbo].[Sales] ([SalesID], [CustomerID], [SDate], [AmountDue], [Term]) VALUES (14, 4, '2014-05-02', 1000.0000, 30)
INSERT INTO [dbo].[Sales] ([SalesID], [CustomerID], [SDate], [AmountDue], [Term]) VALUES (15, 4, '2014-06-15', 1000.0000, 30)
INSERT INTO [dbo].[Sales] ([SalesID], [CustomerID], [SDate], [AmountDue], [Term]) VALUES (16, 4, '2015-06-05', 3000.0000, 30)
;
SET IDENTITY_INSERT [dbo].[Sales] OFF
;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JimiJ13I T ConsultantAuthor Commented:
Great! Thank you very much Paul &  ScottPletcher for a very professional solution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.