# Running Totals with window function

• I have a business requirement to produce a running total, but with a twist ... If the running total drops below zero, then it resets to zero.

In SQL 2008R2, I used a recursive CTE to get a running total

`DECLARE @TranTable TABLE (`

` AccountID INT NOT NULL`

` , TranDate DATETIME NOT NULL`

` , TranValue DECIMAL(18,2) NOT NULL`

`);`

`INSERT INTO @TranTable ( AccountID, TranDate, TranValue )`

`VALUES ( 1, '2016/01/21 03:58:12', 23.05 )`

` , ( 1, '2016/01/23 11:02:15', 24.05 )`

` , ( 1, '2016/02/14 14:08:13',-40.00 )`

` , ( 1, '2016/02/16 07:25:08', 25.00 )`

` , ( 1, '2016/03/17 23:18:25', 25.05 )`

` ---------------------------------------`

` , ( 2, '2016/01/21 03:58:12', 23.05 )`

` , ( 2, '2016/01/23 11:02:15', 24.05 )`

` , ( 2, '2016/02/14 14:08:13',-50.00 )`

` , ( 2, '2016/02/16 07:25:08', 25.00 )`

` , ( 2, '2016/03/17 23:18:25', 25.05 )`

` ---------------------------------------`

` , ( 3, '2016/01/21 03:58:12', 23.05 )`

` , ( 3, '2016/01/23 11:02:15',-30.00 )`

` , ( 3, '2016/02/14 14:08:13', 24.05 )`

` , ( 3, '2016/02/16 07:25:08',-30.00 )`

` , ( 3, '2016/03/17 23:18:25', 25.05 )`

` , ( 3, '2016/03/19 06:03:17', 12.50 );`

`;WITH cteBaseData AS (`

` SELECT AccountID, TranDate, TranValue = CAST(TranValue AS DECIMAL(18,2))`

` , seq = ROW_NUMBER() OVER (PARTITION BY AccountID ORDER BY TranDate)`

` FROM @TranTable`

`)`

`, cteRecurs AS (`

` SELECT AccountID, TranDate, TranValue, seq`

` , runTot = CAST(TranValue AS DECIMAL(18,2))`

` FROM cteBaseData`

` WHERE seq = 1`

` UNION ALL`

` SELECT cte1.AccountID, cte1.TranDate, cte1.TranValue, cte1.seq`

` , runTot = CAST(CASE WHEN cte2.runTot + ISNULL(cte1.TranValue, 0.0) < 0 THEN 0`

` ELSE cte2.runTot + ISNULL(cte1.TranValue, 0.0)`

` END AS DECIMAL(18,2))`

` FROM cteBaseData AS cte1`

` INNER JOIN cteRecurs AS cte2`

` ON cte1.AccountID = cte2.AccountID`

` AND cte1.seq = cte2.seq + 1`

`)`

`SELECT AccountID, TranDate, TranValue, runTot`

`FROM cteRecurs`

`ORDER BY AccountID, TranDate`

`OPTION (MAXRECURSION 10000);`

In SQL 2014, I would like to try and do the same using a window function. However, the reset part is giving me a headache.

`DECLARE @TranTable TABLE (`

` AccountID INT NOT NULL`

` , TranDate DATETIME NOT NULL`

` , TranValue DECIMAL(18,2) NOT NULL`

`);`

`INSERT INTO @TranTable ( AccountID, TranDate, TranValue )`

`VALUES ( 1, '2016/01/21 03:58:12', 23.05 )`

` , ( 1, '2016/01/23 11:02:15', 24.05 )`

` , ( 1, '2016/02/14 14:08:13',-40.00 )`

` , ( 1, '2016/02/16 07:25:08', 25.00 )`

` , ( 1, '2016/03/17 23:18:25', 25.05 )`

` ---------------------------------------`

` , ( 2, '2016/01/21 03:58:12', 23.05 )`

` , ( 2, '2016/01/23 11:02:15', 24.05 )`

` , ( 2, '2016/02/14 14:08:13',-50.00 )`

` , ( 2, '2016/02/16 07:25:08', 25.00 )`

` , ( 2, '2016/03/17 23:18:25', 25.05 )`

` ---------------------------------------`

` , ( 3, '2016/01/21 03:58:12', 23.05 )`

` , ( 3, '2016/01/23 11:02:15',-30.00 )`

` , ( 3, '2016/02/14 14:08:13', 24.05 )`

` , ( 3, '2016/02/16 07:25:08',-30.00 )`

` , ( 3, '2016/03/17 23:18:25', 25.05 )`

` , ( 3, '2016/03/19 06:03:17', 12.50 );`

`SELECT AccountID, TranDate, TranValue`

` , RunTot = SUM(TranValue) OVER (PARTITION BY AccountID ORDER BY TranDate ROWS UNBOUNDED PRECEDING)`

` --------------------------------------------`

` -- The addition of the values in the case statement below, is to simulate the result that I am looking for`

` , RunTot_Expected = SUM(TranValue) OVER (PARTITION BY AccountID ORDER BY TranDate ROWS UNBOUNDED PRECEDING)`

` + CASE WHEN AccountID = 2 AND TranDate >= '2016-02-14 14:08:13' THEN 2.90`

` WHEN AccountID = 3 AND TranDate >= '2016-02-16 07:25:08' THEN 12.90`

` WHEN AccountID = 3 AND TranDate >= '2016-01-23 11:02:15' THEN 6.95`

` ELSE 0`

` END`

`FROM @TranTable`

`ORDER BY AccountID, TranDate;`

Please note that I may not be able to access this board again until after the long weekend.

• Not sure that this is the most efficient method, but I think it does what you're looking for:

`WITH CTE AS`

`(`

`SELECT AccountID,`

` TranDate,`

` TranValue,`

` basic_running_total=SUM(TranValue) OVER (PARTITION BY AccountID ORDER BY TranDate ROWS UNBOUNDED PRECEDING)`

`FROM @TranTable`

`)`

`SELECT AccountID,`

` TranDate,`

` TranValue,`

` basic_running_total,`

` twisty_running_total=basic_running_total-MIN(CASE WHEN basic_running_total<0 THEN basic_running_total ELSE 0 END) OVER (PARTITION BY AccountID ORDER BY TranDate ROWS UNBOUNDED PRECEDING)`

`FROM CTE`

`ORDER BY AccountID,TranDate;`

Cheers!

• ` DECLARE @TranTable TABLE (`

` AccountID INT NOT NULL`

` , TranDate DATETIME NOT NULL`

` , TranValue DECIMAL(18,2) NOT NULL`

`);`

`INSERT INTO @TranTable ( AccountID, TranDate, TranValue )`

`VALUES ( 1, '2016/01/21 03:58:12', 23.05 )`

` , ( 1, '2016/01/23 11:02:15', 24.05 )`

` , ( 1, '2016/02/14 14:08:13',-40.00 )`

` , ( 1, '2016/02/16 07:25:08', 25.00 )`

` , ( 1, '2016/03/17 23:18:25', 25.05 )`

` ---------------------------------------`

` , ( 2, '2016/01/21 03:58:12', 23.05 )`

` , ( 2, '2016/01/23 11:02:15', 24.05 )`

` , ( 2, '2016/02/14 14:08:13',-50.00 )`

` , ( 2, '2016/02/16 07:25:08', 25.00 )`

` , ( 2, '2016/03/17 23:18:25', 25.05 )`

` ---------------------------------------`

` , ( 3, '2016/01/21 03:58:12', 23.05 )`

` , ( 3, '2016/01/23 11:02:15',-30.00 )`

` , ( 3, '2016/02/14 14:08:13', 24.05 )`

` , ( 3, '2016/02/16 07:25:08',-30.00 )`

` , ( 3, '2016/03/17 23:18:25', 25.05 )`

` , ( 3, '2016/03/19 06:03:17', 12.50 );`

`SELECT accountid,`

` trandate,`

` tranvalue,`

` CASE`

` WHEN b < 0`

` AND a < 0 THEN 0.00`

` WHEN b < 0.00`

` AND a > 0.00 THEN a + Abs(b)`

` ELSE a`

` END AS RunningTotal`

`FROM (SELECT *,`

` Min(a)`

` OVER (`

` partition BY accountid`

` ORDER BY trandate ) b`

` FROM (SELECT *,`

` Sum(tranvalue)`

` OVER (`

` partition BY accountid`

` ORDER BY trandate)a`

` FROM @TranTable)T)T1 `

I never could understand the reason for using unbouded preceding clause that does not have the current row or following predicate along with it.

The MIN aggregate function in this case is going to return the min value Partitioned by AccountId and sorted by Transdate and as soon as it finds a Min value lesser than the previous min value for that particular partition it will ignore the previous one and return the current min value for the partition against the current row.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

• Sachin Nandanwar (3/24/2016)

` DECLARE @TranTable TABLE ( AccountID INT NOT NULL , TranDate DATETIME NOT NULL , TranValue DECIMAL(18,2) NOT NULL);INSERT INTO @TranTable ( AccountID, TranDate, TranValue )VALUES ( 1, '2016/01/21 03:58:12', 23.05 ) , ( 1, '2016/01/23 11:02:15', 24.05 ) , ( 1, '2016/02/14 14:08:13',-40.00 ) , ( 1, '2016/02/16 07:25:08', 25.00 ) , ( 1, '2016/03/17 23:18:25', 25.05 ) --------------------------------------- , ( 2, '2016/01/21 03:58:12', 23.05 ) , ( 2, '2016/01/23 11:02:15', 24.05 ) , ( 2, '2016/02/14 14:08:13',-50.00 ) , ( 2, '2016/02/16 07:25:08', 25.00 ) , ( 2, '2016/03/17 23:18:25', 25.05 ) --------------------------------------- , ( 3, '2016/01/21 03:58:12', 23.05 ) , ( 3, '2016/01/23 11:02:15',-30.00 ) , ( 3, '2016/02/14 14:08:13', 24.05 ) , ( 3, '2016/02/16 07:25:08',-30.00 ) , ( 3, '2016/03/17 23:18:25', 25.05 ) , ( 3, '2016/03/19 06:03:17', 12.50 );SELECT accountid, trandate, tranvalue, CASE WHEN b < 0 AND a < 0 THEN 0.00 WHEN b < 0.00 AND a > 0.00 THEN a + Abs(b) ELSE a END AS RunningTotalFROM (SELECT *, Min(a) OVER ( partition BY accountid ORDER BY trandate ) b FROM (SELECT *, Sum(tranvalue) OVER ( partition BY accountid ORDER BY trandate)a`

` FROM @TranTable)T)T1 `

I never could understand the reason for using unbouded preceding clause that does not have the current row or following predicate along with it.

The MIN aggregate function in this case is going to return the min value Partitioned by AccountId and sorted by Transdate and as soon as it finds a Min value lesser than the previous min value for that particular partition it will ignore the previous one and return the current min value for the partition against the current row.

Careful, that CASE statement seems incorrect for the requirements.

Once b (the min of the basic running total so far) goes negative, that absolute value has to be added to the basic running total whether the basic running total is positive or not.

Take a simple example where you first get 10 dollars, then lose 20. The basic running total is then -10, and MIN of the basic running total is negative, so you add in the absolute value of -10 (or, as I did, since you know it's a negative number, you just subtract it, and skip the absolute value function) to get the adjusted running total of 0.

Let's say you next get 5 dollars. The basic running total is still negative at -5, and the adjusted running total should show that you now have 0+5 dollars, which you'd get by adding ABS(-10) to -5. Your case statement would see that both the MIN of the basic running total so far and the basic running total are negative, and just report a zero for that row, when it should show 5.

Here's a simple code snippet to illustrate:

` DECLARE @TranTable TABLE (`

` AccountID INT NOT NULL`

` , TranDate DATETIME NOT NULL`

` , TranValue DECIMAL(18,2) NOT NULL`

`);`

`INSERT INTO @TranTable ( AccountID, TranDate, TranValue )`

`VALUES ( 1, '2016/01/21 03:58:12', 23.05 )`

` , ( 1, '2016/01/23 11:02:15', -50.00 )`

` , ( 1, '2016/02/14 14:08:13',20.00 )`

`SELECT accountid,`

` trandate,`

` tranvalue,`

` CASE`

` WHEN b < 0`

` AND a < 0 THEN 0.00`

` WHEN b < 0.00`

` AND a > 0.00 THEN a + Abs(b)`

` ELSE a`

` END AS RunningTotal`

`FROM (SELECT *,`

` Min(a)`

` OVER (`

` partition BY accountid`

` ORDER BY trandate ) b`

` FROM (SELECT *,`

` Sum(tranvalue)`

` OVER (`

` partition BY accountid`

` ORDER BY trandate)a`

` FROM @TranTable)T)T1 `

You could fix that easily enough, just by switching the case statement to add the absolute value of B to A when B is negative, ignoring the sign of A, since that doesn't seem relevant (or again, as I did in my solution, since the condition is that B is negative just subtract it from A and skip the ABS function).

Cheers!

• Jacob Wilkins (3/25/2016)

Careful, that CASE statement seems incorrect for the requirements.

Once b (the min of the basic running total so far) goes negative, that absolute value has to be added to the basic running total whether the basic running total is positive or not.

Take a simple example where you first get 10 dollars, then lose 20. The basic running total is then -10, and MIN of the basic running total is negative, so you add in the absolute value of -10 (or, as I did, since you know it's a negative number, you just subtract it, and skip the absolute value function) to get the adjusted running total of 0.

Let's say you next get 5 dollars. The basic running total is still negative at -5, and the adjusted running total should show that you now have 0+5 dollars, which you'd get by adding ABS(-10) to -5. Your case statement would see that both the MIN of the basic running total so far and the basic running total are negative, and just report a zero for that row, when it should show 5.

Here's a simple code snippet to illustrate:

` DECLARE @TranTable TABLE (`

` AccountID INT NOT NULL`

` , TranDate DATETIME NOT NULL`

` , TranValue DECIMAL(18,2) NOT NULL`

`);`

`INSERT INTO @TranTable ( AccountID, TranDate, TranValue )`

`VALUES ( 1, '2016/01/21 03:58:12', 23.05 )`

` , ( 1, '2016/01/23 11:02:15', -50.00 )`

` , ( 1, '2016/02/14 14:08:13',20.00 )`

`SELECT accountid,`

` trandate,`

` tranvalue,`

` WHEN b < 0.00 AND a < 0.00 and TranValue<0.00 THEN 0.00`

` WHEN b < 0.00 AND a < 0.00 and TranValue>0.00 THEN TranValue`

` WHEN b < 0.00 AND a > 0.00 THEN a + ABS(b)`

` ELSE a`

` END AS RunningTotal`

`FROM (SELECT *,`

` Min(a)`

` OVER (`

` partition BY accountid`

` ORDER BY trandate ) b`

` FROM (SELECT *,`

` Sum(tranvalue)`

` OVER (`

` partition BY accountid`

` ORDER BY trandate)a`

` FROM @TranTable)T)T1 `

You could fix that easily enough, just by switching the case statement to add the absolute value of B to A when B is negative, ignoring the sign of A, since that doesn't seem relevant (or again, as I did in my solution, since the condition is that B is negative just subtract it from A and skip the ABS function).

Cheers!

Very good catch indeed....

This code should fix the problem you highlighted

` DECLARE @TranTable TABLE (`

` AccountID INT NOT NULL`

` , TranDate DATETIME NOT NULL`

` , TranValue DECIMAL(18,2) NOT NULL`

`);`

`INSERT INTO @TranTable ( AccountID, TranDate, TranValue )`

`VALUES ( 1, '2016/01/21 03:58:12', 23.05 )`

` , ( 1, '2016/01/23 11:02:15', -50.00 )`

` , ( 1, '2016/02/14 14:08:13',20.00 )`

`SELECT accountid,`

` trandate,`

` tranvalue,CASE`

` WHEN b < 0.00 AND a < 0.00 and TranValue<0.00 THEN 0.00`

` WHEN b < 0.00 AND a > 0.00 THEN a + Abs(b)`

` WHEN b < 0.00 AND a < 0.00 and TranValue>0.00 THEN TranValue`

` ELSE a`

` END AS RunningTotal`

`FROM (SELECT *,`

` Min(a)`

` OVER (`

` partition BY accountid`

` ORDER BY trandate ) b`

` FROM (SELECT *,`

` Sum(tranvalue)`

` OVER (`

` partition BY accountid`

` ORDER BY trandate)a`

` FROM @TranTable)T)T1 `

But again it looks to cumbersome with all those conditions in the CASE expression.Your original solution is more subtle.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

• Sachin Nandanwar (3/25/2016)

...But again it looks to cumbersome with all those conditions in the CASE expression.Your original solution is more subtle.

Your version doesn't have to be that complicated.

It could just be this:

`SELECT accountid,`

` trandate,`

` tranvalue,`

` CASE`

` WHEN b < 0`

` THEN a + Abs(b) --equivalent to a-b when b<0`

` ELSE a`

` END AS RunningTotal`

`FROM (SELECT *,`

` Min(a)`

` OVER (`

` partition BY accountid`

` ORDER BY trandate ) b`

` FROM (SELECT *,`

` Sum(tranvalue)`

` OVER (`

` partition BY accountid`

` ORDER BY trandate)a`

` FROM @TranTable)T)T1 `

Cheers!

• Jacob Wilkins (3/25/2016)

Sachin Nandanwar (3/25/2016)

...But again it looks to cumbersome with all those conditions in the CASE expression.Your original solution is more subtle.

Your version doesn't have to be that complicated.

It could just be this:

`SELECT accountid,`

` trandate,`

` tranvalue,`

` CASE`

` WHEN b < 0`

` THEN a + Abs(b) --equivalent to a-b when b<0`

` ELSE a`

` END AS RunningTotal`

`FROM (SELECT *,`

` Min(a)`

` OVER (`

` partition BY accountid`

` ORDER BY trandate ) b`

` FROM (SELECT *,`

` Sum(tranvalue)`

` OVER (`

` partition BY accountid`

` ORDER BY trandate)a`

` FROM @TranTable)T)T1 `

Cheers!

Brillian Jacob. There is still a huge performance tuning opportunity here. This solution does not take advantage of the T-SQL window framing functionality introduced in SQL 2012. If you don't frame your window aggregate functions using ROWS BETWEEN UNBOUNDED... you are essentially doing a triangular join for each subquery. Here's three queries with STATISTICS IO ON. The first is your solution, the second includes framing on the "a" subquery, the third includes framing on the both the "a" and "b" subqueries.

`SET STATISTICS IO ON;`

`PRINT 'Original Solution:'`

`SELECT accountid,`

` trandate,`

` tranvalue,`

` CASE`

` WHEN b < 0THEN a + Abs(b) --equivalent to a-b when b<0`

` ELSE a`

` END AS RunningTotal`

`FROM`

`(`

` SELECT *, Min(a) OVER (partition BY accountid ORDER BY trandate) b`

` FROM`

` (`

` SELECT *, Sum(tranvalue) OVER (partition BY accountid ORDER BY trandate)a`

` FROM #TranTable`

` )T`

`)T1;`

`PRINT 'Original Solution + Window framing specifications on subquery a:'`

`SELECT accountid,`

` trandate,`

` tranvalue,`

` CASE`

` WHEN b < 0THEN a + Abs(b) --equivalent to a-b when b<0`

` ELSE a`

` END AS RunningTotal`

`FROM`

`(`

` SELECT *, Min(a) OVER (partition BY accountid ORDER BY trandate) b`

` FROM`

` (`

` SELECT *, Sum(tranvalue) OVER`

` (`

` partition BY accountid ORDER BY trandate`

` ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`

` )a`

` FROM #TranTable`

` )T`

`)T1;`

`PRINT 'Original Solution + Window framing specifications on subquery a and subquery b:'`

`SELECT accountid,`

` trandate,`

` tranvalue,`

` CASE`

` WHEN b < 0THEN a + Abs(b) --equivalent to a-b when b<0`

` ELSE a`

` END AS RunningTotal`

`FROM`

`(`

` SELECT *, Min(a) OVER`

` (`

` partition BY accountid ORDER BY trandate`

` ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`

` ) b`

` FROM`

` (`

` SELECT *, Sum(tranvalue) OVER`

` (`

` partition BY accountid ORDER BY trandate`

` ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`

` ) a`

` FROM #TranTable`

` )T`

`)T1;`

`SET STATISTICS IO OFF;`

All three generate the exact same query plan and create worktables in the tempdb but let's see what's happening under the hood. Here's the results (with "0 read" stuff omitted for brevity):

`Original Solution:`

`Table 'Worktable'. Scan count 38, logical reads 194...`

`Table '#TranTable__00000000003A'. Scan count 1, logical reads 2...`

`Original Solution + Window framing specifications on subquery a:`

`Table 'Worktable'. Scan count 19, logical reads 97...`

`Table '#TranTable__00000000003A'. Scan count 1, logical reads 2...`

`Original Solution + Window framing specifications on subquery a and subquery b:`

`Table 'Worktable'. Scan count 0, logical reads 0...`

`Table '#TranTable__00000000003A'. Scan count 1, logical reads 2...`

Note that, for 16 rows, the original solution does 38 worktable scans and 194 reads. Add framing to "a" and we cut those numbers in half. Add framing to both and the optimizer still creates a work table but does not use it.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

• Alan.B (3/25/2016)

Jacob Wilkins (3/25/2016)

Sachin Nandanwar (3/25/2016)

...But again it looks to cumbersome with all those conditions in the CASE expression.Your original solution is more subtle.

Your version doesn't have to be that complicated.

It could just be this:

`SELECT accountid,`

` trandate,`

` tranvalue,`

` CASE`

` WHEN b < 0`

` THEN a + Abs(b) --equivalent to a-b when b<0`

` ELSE a`

` END AS RunningTotal`

`FROM (SELECT *,`

` Min(a)`

` OVER (`

` partition BY accountid`

` ORDER BY trandate ) b`

` FROM (SELECT *,`

` Sum(tranvalue)`

` OVER (`

` partition BY accountid`

` ORDER BY trandate)a`

` FROM @TranTable)T)T1 `

Cheers!

Brillian Jacob. There is still a huge performance tuning opportunity here. This solution does not take advantage of the T-SQL window framing functionality introduced in SQL 2012. If you don't frame your window aggregate functions using ROWS BETWEEN UNBOUNDED... you are essentially doing a triangular join for each subquery. Here's three queries with STATISTICS IO ON. The first is your solution, the second includes framing on the "a" subquery, the third includes framing on the both the "a" and "b" subqueries.

`SET STATISTICS IO ON;`

`PRINT 'Original Solution:'`

`SELECT accountid,`

` trandate,`

` tranvalue,`

` CASE`

` WHEN b < 0THEN a + Abs(b) --equivalent to a-b when b<0`

` ELSE a`

` END AS RunningTotal`

`FROM`

`(`

` SELECT *, Min(a) OVER (partition BY accountid ORDER BY trandate) b`

` FROM`

` (`

` SELECT *, Sum(tranvalue) OVER (partition BY accountid ORDER BY trandate)a`

` FROM #TranTable`

` )T`

`)T1;`

`PRINT 'Original Solution + Window framing specifications on subquery a:'`

`SELECT accountid,`

` trandate,`

` tranvalue,`

` CASE`

` WHEN b < 0THEN a + Abs(b) --equivalent to a-b when b<0`

` ELSE a`

` END AS RunningTotal`

`FROM`

`(`

` SELECT *, Min(a) OVER (partition BY accountid ORDER BY trandate) b`

` FROM`

` (`

` SELECT *, Sum(tranvalue) OVER`

` (`

` partition BY accountid ORDER BY trandate`

` ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`

` )a`

` FROM #TranTable`

` )T`

`)T1;`

`PRINT 'Original Solution + Window framing specifications on subquery a and subquery b:'`

`SELECT accountid,`

` trandate,`

` tranvalue,`

` CASE`

` WHEN b < 0THEN a + Abs(b) --equivalent to a-b when b<0`

` ELSE a`

` END AS RunningTotal`

`FROM`

`(`

` SELECT *, Min(a) OVER`

` (`

` partition BY accountid ORDER BY trandate`

` ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`

` ) b`

` FROM`

` (`

` SELECT *, Sum(tranvalue) OVER`

` (`

` partition BY accountid ORDER BY trandate`

` ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`

` ) a`

` FROM #TranTable`

` )T`

`)T1;`

`SET STATISTICS IO OFF;`

All three generate the exact same query plan and create worktables in the tempdb but let's see what's happening under the hood. Here's the results (with "0 read" stuff omitted for brevity):

`Original Solution:`

`Table 'Worktable'. Scan count 38, logical reads 194...`

`Table '#TranTable__00000000003A'. Scan count 1, logical reads 2...`

`Original Solution + Window framing specifications on subquery a:`

`Table 'Worktable'. Scan count 19, logical reads 97...`

`Table '#TranTable__00000000003A'. Scan count 1, logical reads 2...`

`Original Solution + Window framing specifications on subquery a and subquery b:`

`Table 'Worktable'. Scan count 0, logical reads 0...`

`Table '#TranTable__00000000003A'. Scan count 1, logical reads 2...`

Note that, for 16 rows, the original solution does 38 worktable scans and 194 reads. Add framing to "a" and we cut those numbers in half. Add framing to both and the optimizer still creates a work table but does not use it.

Thanks! I occasionally stumble onto things 🙂

At any rate, none of those are my original solution (just variations on Sachin's). If I remember correctly, my original solution also just did a single scan of the table and created the worktable without using it, but I'll have to test that when I have access to a server later.

It is impressive how much adding the frame specifications helps for that version. Good catch!

Cheers!

• Jacob Wilkins (3/25/2016)

Your version doesn't have to be that complicated.

It could just be this:

`SELECT accountid,`

` trandate,`

` tranvalue,`

` CASE`

` WHEN b < 0`

` THEN a + Abs(b) --equivalent to a-b when b<0`

` ELSE a`

` END AS RunningTotal`

`FROM (SELECT *,`

` Min(a)`

` OVER (`

` partition BY accountid`

` ORDER BY trandate ) b`

` FROM (SELECT *,`

` Sum(tranvalue)`

` OVER (`

` partition BY accountid`

` ORDER BY trandate)a`

` FROM @TranTable)T)T1 `

Cheers!

Yes..Makes sense because when b<0 then ABS would ignore the negative value of b and instead return -a+b if a<0 and a+b if a>0.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

• Alan.B (3/25/2016)

`Original Solution:`

`Table 'Worktable'. Scan count 38, logical reads 194...`

`Table '#TranTable__00000000003A'. Scan count 1, logical reads 2...`

`Original Solution + Window framing specifications on subquery a:`

`Table 'Worktable'. Scan count 19, logical reads 97...`

`Table '#TranTable__00000000003A'. Scan count 1, logical reads 2...`

`Original Solution + Window framing specifications on subquery a and subquery b:`

`Table 'Worktable'. Scan count 0, logical reads 0...`

`Table '#TranTable__00000000003A'. Scan count 1, logical reads 2...`

Wow..Quite impressive indeed..Didn't knew that using the humble ROWS UNBOUNDED PRECEDING would make such huge performance impact..

Thanks for highlighting it..

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

• Thank you Sachin and Jacob. From my initial dev testing, this appears to do the trick. Now we see if the QA team can break it ...

I am not sure how to mark multiple contributors as having provided the solution. I have selected Sachin's answer as he provided the initial query, which Jacob helped to tweak.

Thank You Alan. The performance of the framing is a good win as well.

`-- Results FROM the original Recursive CTEs`

`Table 'Worktable'. Scan count 2, logical reads 93, ...`

`Table '#276EDEB3'. Scan count 17, logical reads 17, ...`

`-- Results FROM the framed Window functions`

`Table 'Worktable'. Scan count 0, logical reads 0, ...`

`Table '#A4E6492B'. Scan count 1, logical reads 1, ...`

• DesNorton (3/28/2016)

Thank you Sachin and Jacob. From my initial dev testing, this appears to do the trick. Now we see if the QA team can break it ...

I am not sure how to mark multiple contributors as having provided the solution. I have selected Sachin's answer as he provided the initial query, which Jacob helped to tweak.

Thank You Alan. The performance of the framing is a good win as well.

`-- Results FROM the original Recursive CTEs`

`Table 'Worktable'. Scan count 2, logical reads 93, ...`

`Table '#276EDEB3'. Scan count 17, logical reads 17, ...`

`-- Results FROM the framed Window functions`

`Table 'Worktable'. Scan count 0, logical reads 0, ...`

`Table '#A4E6492B'. Scan count 1, logical reads 1, ...`

I'm glad it's working for you! So long as it works it doesn't really matter, but for the record I also provided a logically equivalent query in my initial post. The perils of too many posts, I suppose 🙂

Cheers!

• Jacob Wilkins (3/28/2016)

DesNorton (3/28/2016)

Thank you Sachin and Jacob. From my initial dev testing, this appears to do the trick. Now we see if the QA team can break it ...

I am not sure how to mark multiple contributors as having provided the solution. I have selected Sachin's answer as he provided the initial query, which Jacob helped to tweak.

Thank You Alan. The performance of the framing is a good win as well.

`-- Results FROM the original Recursive CTEs`

`Table 'Worktable'. Scan count 2, logical reads 93, ...`

`Table '#276EDEB3'. Scan count 17, logical reads 17, ...`

`-- Results FROM the framed Window functions`

`Table 'Worktable'. Scan count 0, logical reads 0, ...`

`Table '#A4E6492B'. Scan count 1, logical reads 1, ...`

I'm glad it's working for you! So long as it works it doesn't really matter, but for the record I also provided a logically equivalent query in my initial post. The perils of too many posts, I suppose 🙂

Cheers!

I just noticed that (I did not scroll all the way to the right and look at the code from the original post). I guess it doesn't hurt to explain what the whole ROWS UNBOUNDED... is all about. :hehe:

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

• Heh... and they say the Quirky Update method is complicated. :blink:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.