March 24, 2016 at 9:00 am
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.
March 24, 2016 at 9:47 am
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!
March 24, 2016 at 11:09 pm
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
March 25, 2016 at 7:17 am
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 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.
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!
March 25, 2016 at 9:24 am
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
March 25, 2016 at 10:14 am
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!
March 25, 2016 at 11:28 am
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.
-- Itzik Ben-Gan 2001
March 25, 2016 at 11:34 am
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!
March 25, 2016 at 8:16 pm
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
March 25, 2016 at 8:24 pm
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
March 28, 2016 at 9:22 am
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, ...
March 28, 2016 at 9:27 am
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!
March 28, 2016 at 9:52 am
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:
-- Itzik Ben-Gan 2001
March 28, 2016 at 2:54 pm
Heh... and they say the Quirky Update method is complicated. :blink:
--Jeff Moden
Change is inevitable... Change for the better is not.
April 4, 2023 at 7:07 am
Thank you Alan!
This one really helped me a lot.
It's strange that MS haven't implemented a parameter for SUM to stop at 0.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply