SQL Server 2012 - Running Total With Backlog & Carry Forward

Question

Good afternoon,

Hope that you're all well and wish you a happy new year.

I'm experiencing some curious behaviour with a query that I've written in that the LAG function is inconsistent.

Essentially, I have a dataset (made up of 2 CTEs) which each contain the month (in MMM-YYYY format) and then one holds a count of tickets opened, and the other contains the same but for tickets closed.

What I am then doing is adding in a 'Backlog' column (which will be 0 for the first month in all cases) and a 'Carried Forward' column. The Carried Forward amount will be the balance of that month ( Created + Backlog ) and will be reflected as the Backlog for the following month.

I had this ticking over quite nicely until I realised that negative backlogs were fudging the numbers a bit. What I mean is, for example:

10 Tickets Created
12 Tickets Resolved
0 Ticket Backlog
-2 Tickets Carried Forward

In this circumstance, I've had to zero any negative backlog for our reporting purposes.

This is seemingly where the problems come into play. For the first few months, everything will be fine - the values will be right, carrying forward the correct numbers and factoring them into the calculations accordingly. But then it will carry over a number of (seemingly) indeterminable origin which of course, has a knock-on effect on the accuracy past this point.

With the Window Functions introduced with SQL Server 2012, this should be quite basic - but evidently not!

Whilst I'm quite happy to post code (I have tried a fair few ways of skinning this cat), I feel as though if someone is able to give a high-level overview of how it should be written, I'll see where I went wrong immediately. In doing so, I'll then respond accordingly with my attempt/s for completeness.

Thank you very much in advance!

Picture of result error:

, OpenClosed AS
(
SELECT
  c.[Created Month] 'Month'
, c.Tickets 'Created'
, r.Tickets 'Resolved'
, IIF( ( c.Tickets - r.Tickets ) < 0, 0, ( c.Tickets - r.Tickets ) ) 'Balance'

FROM
  Created c

JOIN Resolved r ON
  c.[Created Month] = r.[Resolved Month]
)

, CarryForward AS
(
SELECT
  ROW_NUMBER() OVER( ORDER BY CAST( '1.' + Month AS DATETIME ) ) 'Row No'
, Month 'Month'
, Created 'Created'
, Resolved 'Resolved'
, LAG( Balance, 1, 0 ) OVER( ORDER BY CAST( '1.' + Month AS DATETIME ) ) 'Backlog'

, IIF( ( ( Created + LAG( Balance, 1, 0 ) OVER( ORDER BY CAST( '1.' + Month AS DATETIME ) ) ) - Resolved ) < 0
      , 0
      , ( ( Created + LAG( Balance, 1, 0 ) OVER( ORDER BY CAST( '1.' + Month AS DATETIME ) ) ) - Resolved )
         ) 'Carry Forward'

FROM
  OpenClosed
)

SELECT
  c1.Month 'Month'
, c1.Created 'Created'
, c1.Resolved 'Resolved'
, c2.[Carry Forward] 'Backlog'
, IIF( ( c1.Created + c2.[Carry Forward] ) - c1.Resolved < 0
      , 0
      , ( c1.Created + c2.[Carry Forward] ) - c1.Resolved
         ) 'Carried Forward'

FROM
  CarryForward c1

JOIN CarryForward c2 ON
  c2.[Row No] = c1.[Row No]-1

please give some code samples. And you can use "sum(Quantity) over (partition by ProductId order by MovementDate rows unbounded preceding) as sumIn" syntax for running sum. Please check http://www.kodyaz.com/sap-abap/stock-aging-using-sqlscript-on-sap-hana-database.aspx for samples — Eralper, Jan 02 '18 at 15:13
Thank you very much - have added the current iteration and heading over to read the link kindly provided. — HendrixAndChill, Jan 02 '18 at 15:19
Check this https://stackoverflow.com/questions/22235959/window-functions-running-total-with-reset Maybe it will be helpful — uzi, Jan 02 '18 at 15:23
If you are keeping a running balance, you need to include the *brought forward* amount also. I don't see how you can create 10 and resolve 12, unless it is implicit that 2 have been brought forward from a previous reporting period (in which case the carry forward would be 0 rather than -2). — Steve, Jan 02 '18 at 15:36
Hi Steve, thank you for replying. Exactly as you stated - apologies if the implication was vague! — HendrixAndChill, Jan 02 '18 at 15:44
@HendrixAndChill, I don't see how your figures make sense purely in terms of arithmetic. In Aug-15, 50 were created, 52 were resolved, but there was only a backlog of 1 and a carry of 0! That doesn't balance - or is that a manifestation of the problem here? I've added an answer that explains how a balance would usually be derived. — Steve, Jan 03 '18 at 15:58
@Steve Very much a manifestation. I completely understand why you're bemused as the numbers make me come across as a complete buffoon! Just reviewing your answer and tinkering - will reply accordingly in short order. Thanks again! — HendrixAndChill, Jan 03 '18 at 16:08
@HendrixAndChill, no problem! The code and results you've posted have greatly helped set the context. Incidentally, if you are wanting a derivative of the balance to highlight months where things are getting better or worse, use `Balance - LAG(Balance, 1, Balance) OVER (ORDER BY Month ASC)` — Steve, Jan 03 '18 at 16:30
@Steve Update on the curious numbers - it transpires there are 6 tickets which were created prior to 2015-01/Jan-2015. However, these tickets were resolved from Jan-2015 onwards. The inner join obviously discounts months prior to Jan-2015 as this doesn't exist in the 'resolved' CTE - explaining how there are more tickets resolved than created in certain months (which aren't explained by a balance brought forward). Phew - I thought I was going insane! Using a left join has tidied everything up. Will accept your answer below - thanks again! :) — HendrixAndChill, Jan 03 '18 at 17:03
@HendrixAndChill, Ah I see! Your next problem is if a month arises where none have been created but some have been resolved - a credible possibility if this report happened to be run right at the start of the month. For that you need a `FULL OUTER JOIN` between the Created and Resolved tables, a `COALESCE` on the `Created Month` column, and an `ISNULL(Tickets, 0)` on each of the tickets columns..As it currently stands, the `LEFT JOIN` will omit from the report any month which contained only resolutions (and no creations). — Steve, Jan 03 '18 at 17:18

score 2 · Accepted Answer · answered Jan 03 '18 at 15:47

From comments on question. Incidentally, the Created Month column should be redone somehow so that the year is placed before the month - like 2015-01. This will ensure correct ordering by default sort algorithms.

If the date must be presented as Jan-2015 in the final report, do that presentational work as the very final step in the query.

WITH ticket_account AS
(
    SELECT
         c.[Created Month]  AS Month
        ,c.Tickets          AS Created
        ,r.Tickets          AS Resolved

    FROM
      Created AS c

    INNER JOIN 
        Resolved AS r 
        ON c.[Created Month] = r.[Resolved Month]
)

SELECT
    *
    ,(SUM(Created) OVER (ORDER BY Month ASC) - SUM(Resolved) OVER (ORDER BY Month ASC)) AS Balance

FROM
    ticket_account

This answer is correct - the problem I was having owed to some errant data discounted by an incorrect join type (please see the question comments). — HendrixAndChill, Jan 03 '18 at 17:05

SQL Server 2012 - Running Total With Backlog & Carry Forward

1 Answers1