title: CTE and Lag query
body: <p>I am trying to achieve a running deductive total, but the problem occurs when I have a zero row.  I am using SQL Server 2012.</p>
  
  <p>Here's a batch of my current results.</p>
  
  <pre><code>SuppressionDescription  SuppressionPriority SuppressionPriorityOrder    TotalRecords    RecordsLost RunningTotal
  Deceased_Bln    1   1   1376    2   1374
  Pivotal Postcode Exclusions 9   2   1376    0   1374
  Pivotal 3 Month Decline 11  3   1376    24  1352
  Postcode exclusions (Complaints)    12  4   1376    0   1352
  Gone Away (from Barcode on returned mail)   15  5   1376    30  1346
  Pivotal prospects  with a Do Not Mail flag  16  6   1376    234 1112
  Email Suppression File  17  7   1376    7   1135
  Opt outs &amp; undeliverables from SMS system   18  8   1376    7   1362
  Generic Phone Number Suppression    19  9   1376    245 1124
  Exclude if not MR, MRS, MISS, MS, NULL  23  10  1376    0   1131
  Total Prospects 9999    11  1376    0   1376
  </code></pre>
  
  <p>With my query, the first two rows calculate.  A total of 1,376 less 2 leaves 1,374 and the second row has no RecordsLost, so the RunningTotal remains the same.  So far so good.</p>
  
  <p>But because Row 2 has a 0 count, it means row 3 (whilst it has a RecordsLost of 24) gets out of sync.</p>
  
  <p>I've tried adding various case statements, to try and get totals for different scenario's, but it never quite works.</p>
  
  <p>Here is my statement:</p>
  
  <pre><code>;WITH CTE AS (SELECT        ID, SuppressionTypeID, ContactMethodType, SupplierName, SuppressionDescription
                      ,           SuppressionCount, TotalRecords, RecordsLost, SuppressionPriority, SuppressionPriorityOrder  
                      ,           CASE WHEN SuppressionPriority = 9999
                                      THEN TotalRecords
                                      ELSE TotalRecords - RecordsLost END AS RowDiff  
                      ,           CASE WHEN RecordsLost = 0
                                      THEN LAG(RecordsLost, 1) OVER (PARTITION BY SupplierName, ContactMethodType ORDER BY SuppressionPriorityOrder) 
                                      ELSE RecordsLost END AS RecordsLostRoll
                      FROM        #tmpSup 
                      WHERE       SupplierName        = 'Freeman Grattan Holdings'
                      AND         ContactMethodType   = 'A'
                      )
      SELECT      SuppressionTypeID, ContactMethodType, SupplierName, SuppressionDescription
      ,           SuppressionPriority, SuppressionPriorityOrder
      ,           TotalRecords
      ,           RecordsLost
      ,           CASE WHEN SuppressionPriorityOrder = 1
                      THEN RowDiff 
                      ELSE    (LAG(RowDiff, 1, RowDiff)
                                  OVER (PARTITION BY SupplierName, ContactMethodType ORDER BY SuppressionPriorityOrder)) - RecordsLost
                      END AS RunningTotal
      FROM        CTE
      ORDER BY    SupplierName
      ,           ContactMethodType
      ,           SuppressionPriority
  </code></pre>
  
  <p>I'd like to see the RunningTotal as:
  1374
  1374
  1350
  1350
  1320
  1086
  1079
  1072
  827
  827
  1376 (for total Prospects)</p>
  
  <p>Any suggestions and thanks in advance.</p>