title: SQL Server 2012 - Running Total With Backlog & Carry Forward
body: <p>Good afternoon,</p>
  
  <p>Hope that you're all well and wish you a happy new year.</p>
  
  <p>I'm experiencing some curious behaviour with a query that I've written in that the LAG function is inconsistent.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <ul>
  <li>10 Tickets Created</li>
  <li>12 Tickets Resolved</li>
  <li>0 Ticket Backlog</li>
  <li>-2 Tickets Carried Forward</li>
  </ul>
  
  <p>In this circumstance, I've had to zero any negative backlog for our reporting purposes.  </p>
  
  <p>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.</p>
  
  <p>With the Window Functions introduced with SQL Server 2012, this should be quite basic - but evidently not!</p>
  
  <p>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.</p>
  
  <p>Thank you very much in advance!</p>
  
  <p>Picture of result error:</p>
  
  <p><a href="https://i.stack.imgur.com/6uC5L.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/6uC5L.png" alt="This is where the error occurs"></a></p>
  
  <pre><code>, OpenClosed AS
  (
  SELECT
    c.[Created Month] 'Month'
  , c.Tickets 'Created'
  , r.Tickets 'Resolved'
  , IIF( ( c.Tickets - r.Tickets ) &lt; 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 ) &lt; 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 &lt; 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
  </code></pre>