Creating Ageing report (>0), (0-30), (31-60)-CodePudding

I am trying to write an Ageing Report on SQL Server which shows the total amount of overdue invoices (later on I will have to deduct Credit Notes) that fall in the different columns depending on how many days the have been overdued. I.e (>0), (0-30), (31-60), (61-90), etc.

This is the part of the query I have written so far mostly looking at old post in this forum but it's giving me a lot of duplicates even for Accounts where there is not due balance.

Any idea what I am doing wrong?

SELECT O.cardcode      AS [Account],
       O.cardname      AS [Name],
       O.u_creditlimit AS [Credit Limit],
       O.u_onhold      AS [On Hold],
       O.balance,
       Isnull(CASE
                WHEN Datediff(day, INV.docduedate, Getdate()) >= 0 AND Datediff(day, INV.docduedate, Getdate()) < 30 
                THEN (
                       SELECT Sum(doctotal)
                       FROM   oinv
                       WHERE cardcode = INV.cardcode)
              END, 0)  AS [0 to 30 Days],
       Isnull(CASE
                WHEN Datediff(day, INV.docduedate, Getdate()) >= 31 AND Datediff(day, INV.docduedate, Getdate()) < 60 
                THEN (
                      SELECT Sum(doctotal)
                      FROM   oinv
                      WHERE cardcode = INV.cardcode)
              END, 0)  AS [31 to 60 Days],
       Isnull(CASE
                WHEN Datediff(day, INV.docduedate, Getdate()) >= 61 AND Datediff(day, INV.docduedate, Getdate()) < 90 
                THEN (
                      SELECT Sum(doctotal)
                      FROM   oinv
                      WHERE cardcode = INV.cardcode)
              END, 0)  AS [61 to 90 Days],
       Isnull(CASE
                WHEN Datediff(day, INV.docduedate, Getdate()) >= 91 AND Datediff(day, INV.docduedate, Getdate()) < 120 
                THEN (
                      SELECT Sum(doctotal)
                      FROM   oinv
                      WHERE  cardcode = INV.cardcode)
              END, 0)  AS [91 to 120 Days],
       Isnull(CASE
                WHEN Datediff(day, INV.docduedate, Getdate()) >= 121 
                THEN(
                      SELECT Sum(doctotal)
                      FROM oinv
                      WHERE cardcode = INV.cardcode)
              END, 0)  AS [121  Days]
FROM   ocrd O
       INNER JOIN oinv INV
               ON O.cardcode = INV.cardcode
WHERE  territory = 3
       AND INV.docstatus = 'O'

Thank you very much.

CodePudding user response：

You can clean this up a bit

First. use a CROSS APPLY to calculate the Days-Past-Due once, and then a conditional aggregation for the final results

Example (Untested)

  Select O.cardcode
        ,O.cardname  
        ,[Credit Limit]   = max(O.u_creditlimit)
        ,[On Hold]        = max(O.u_onhold)
        ,[0 to 30 Days]   = sum( case when DPD between  0 and  30 then doctotal else 0 end)
        ,[31 to 60 Days]  = sum( case when DPD between 31 and  60 then doctotal else 0 end)
        ,[61 to 90 Days]  = sum( case when DPD between 61 and  90 then doctotal else 0 end)
        ,[91 to 120 Days] = sum( case when DPD between 91 and 120 then doctotal else 0 end)
        ,[121  Days ]     = sum( case when DPD >=121              then doctotal else 0 end)
   From  ocrd O
   Join  oinv INV on O.cardcode = INV.cardcode
   Cross Apply (values ( Datediff(day, INV.docduedate, Getdate()) ) ) P(DPD)
   Where territory = 3
     and INV.docstatus = 'O' 
     and DPD >= 0
  Group By O.cardcode
          ,O.cardname

CodePudding user response：

when asking questions is a great idea to provide demo data, and a better one to provide it as an object we can easily re recreate:

DECLARE @table TABLE (RecordID INT IDENTITY, CardCode INT, CardName NVARCHAR(100), u_CreditLimit DECIMAL(10,2), u_onhold DECIMAL(10,2), balance DECIMAL(10,2))
INSERT INTO @table (CardCode, CardName, u_CreditLimit, u_onhold, balance) VALUES (1, 'John Smith', 10000, 0, 200),
(1, 'John Smith', 10000, 0, 400),
(1, 'John Smith', 10000, 0, 200)

This would allow someone to just run the TSQL to create and populate the object.

Now using that object we could write something like

SELECT RecordID, CardCode, CardName, U_CreditLimit, U_OnHold, Balance, COALESCE(LAG(Balance,1) OVER (PARTITION BY CardCode ORDER BY RecordID) - Balance,Balance) AS RunningTotal
  FROM @table

This psudeo code, and may need some tweaking to get exactly what you're looking for.

A quick word on LAG and its pal LEAD. You specify a column and an offset in rows. LAG looks backwards, LEAD forwards. They both use over clauses just like any other windowed function.