June 7, 2016 at 1:29 pm
[font="Arial"]Hi ,
***USING SQL SERVER 2008 R2****
I have used CTE to get to this partial result where I'm partitioning by column Account, but having issues generating the running remaining balances
Rules for calculating:
1. If Amount > Budget Remaining, then Budget_To_Tag = 0
2. If Amount < Budget Remaining if RN = 1, then Budget Remaining else Budget Remaining - Amount and
then tag Budget_To_Tag = Amount
[/font]
Required Output
+-------------------+------------------+--------+---------------+----+
| Account | Budget_Remaining | Amount | Budget_To_Tag | RN |
+-------------------+------------------+--------+---------------+----+
| 10-60001-603-69-0 | 12992 | 3100 | 3100 | 1 |
+-------------------+------------------+--------+---------------+----+
| 10-60001-603-69-0 | 9892 | 2750 | 2750 | 2 |
+-------------------+------------------+--------+---------------+----+
| 10-60001-603-69-0 | 7142 | 1000 | 1000 | 3 |
+-------------------+------------------+--------+---------------+----+
| 10-60001-610-69-1 | 88 | 99 | 0 | 1 |
+-------------------+------------------+--------+---------------+----+
IF OBJECT_ID (N'dbo.Account_Tagging', N'U') IS NOT NULL
AND EXISTS(SELECT * from dbo.Account_Tagging)
drop table Account_Tagging
BEGIN
CREATE TABLE Account_Tagging(
Account VARCHAR(17) NOT NULL
,Budget_Remaining INTEGER NOT NULL
,Amount NUMERIC(7,2) NOT NULL
,Budget_To_Tag BIT NOT NULL
,RN INTEGER NOT NULL
);
INSERT INTO Account_Tagging(Account,Budget_Remaining,Amount,Budget_To_Tag,RN) VALUES ('10-60001-600-65-0',5124,2500,0,1);
INSERT INTO Account_Tagging(Account,Budget_Remaining,Amount,Budget_To_Tag,RN) VALUES ('10-60001-603-69-0',12992,3100,0,1);
INSERT INTO Account_Tagging(Account,Budget_Remaining,Amount,Budget_To_Tag,RN) VALUES ('10-60001-603-69-0',0,2750,0,2);
INSERT INTO Account_Tagging(Account,Budget_Remaining,Amount,Budget_To_Tag,RN) VALUES ('10-60001-603-69-0',0,1000,0,3);
INSERT INTO Account_Tagging(Account,Budget_Remaining,Amount,Budget_To_Tag,RN) VALUES ('10-60001-604-65-0',617,700,0,1);
INSERT INTO Account_Tagging(Account,Budget_Remaining,Amount,Budget_To_Tag,RN) VALUES ('10-60001-604-69-0',3604,425,0,1);
INSERT INTO Account_Tagging(Account,Budget_Remaining,Amount,Budget_To_Tag,RN) VALUES ('10-60001-605-65-0',1182,1006.25,0,1);
INSERT INTO Account_Tagging(Account,Budget_Remaining,Amount,Budget_To_Tag,RN) VALUES ('10-60001-605-65-0',0,758,0,2);
INSERT INTO Account_Tagging(Account,Budget_Remaining,Amount,Budget_To_Tag,RN) VALUES ('10-60001-605-65-0',0,695,0,3);
END
Thanks Guys,
V
June 7, 2016 at 1:40 pm
Believe it or not, prior to SQL Server 2012 and it's enhanced Windowing Function support the most efficient way to do running-total style calculations in SQL Server is to use a cursor. This despite how horribly inefficient those are in the SQL Server engine. The quadratic performance degradation you get from the join required just crushes you as the number of rows grows.
Here is a good link (which include stuff about the "Quirky Update" but you will want to read about that here on SSC.com if you want to pursue it):
http://sqlperformance.com/2012/07/t-sql-queries/running-totals
http://sqlperformance.com/2014/01/t-sql-queries/grouped-running-totals
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 7, 2016 at 1:40 pm
While I normally avoid them, I think this may be a case where a cursor is your best bet.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 8, 2016 at 5:03 pm
Thanks Guys. I was trying to avoid cursors and see if there was a faster solution. I completely detest cursors :), but I guess I had no option since it's 2008. My code is below, let me know if someone has a better solution.
As always thank you guys for your quick responses and support.
BEGIN
CREATE TABLE #x
(
Account varchar(20),
Budget_Remaining float,
Amount float,
RunningBalance float,
Budget_To_Tag float,
RN varchar(20)
);
INSERT #x(Account,Budget_Remaining,Amount,RunningBalance,Budget_To_Tag,RN)
SELECT Account,Budget_Remaining,Amount, 0 RunningBalance,Budget_To_Tag,RN
FROM Account_Tagging
ORDER BY Account,RN;
DECLARE @account varchar(20), @Budget_Remaining float,@Amount float,@RunningBalance float,
@Budget_To_Tag float,@RN varchar(20);
--SET @RunningBalance = 0;
DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT Account,Budget_Remaining,Amount,0 RunningBalance,Budget_To_Tag, RN FROM #x
ORDER BY Account,RN;
OPEN c;
FETCH c INTO @account,@Budget_Remaining, @Amount, @RunningBalance , @Budget_To_Tag, @RN;
WHILE @@FETCH_STATUS = 0
BEGIN
--IF @RN = 1
--SET @RunningBalance = @Budget_Remaining;
--ELSE
SET @RunningBalance = @Budget_Remaining - @Amount;
UPDATE #x SET Budget_Remaining = @RunningBalance WHERE Account = @account and @RN + 1 = RN;
UPDATE #x SET RunningBalance = @RunningBalance WHERE Account = @account and @RN = RN;
FETCH c INTO @account,@Budget_Remaining, @Amount, @RunningBalance , @Budget_To_Tag, @RN;
END
CLOSE c; DEALLOCATE c;
UPDATE #x
SET Budget_To_Tag = RunningBalance
WHERE RunningBalance > Amount
select * from Account_Tagging
select * from #X
drop table #X
END
June 16, 2016 at 5:27 pm
venkyzrocks (6/8/2016)
Thanks Guys. I was trying to avoid cursors and see if there was a faster solution. I completely detest cursors :), but I guess I had no option since it's 2008. My code is below, let me know if someone has a better solution.As always thank you guys for your quick responses and support.
CREATE TABLE #x
(
Account varchar(20),
Budget_Remaining float,
Amount float,
RunningBalance float,
Budget_To_Tag float,
RN varchar(20)
);
INSERT #x(Account,Budget_Remaining,Amount,RunningBalance,Budget_To_Tag,RN)
SELECT Account,Budget_Remaining,Amount, 0 RunningBalance,Budget_To_Tag,RN
FROM Test1
ORDER BY Account,RN;
DECLARE @account varchar(20), @Budget_Remaining float,@Amount float,@RunningBalance float,
@Budget_To_Tag float,@RN varchar(20);
--SET @RunningBalance = 0;
DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT Account,Budget_Remaining,Amount,0 RunningBalance,Budget_To_Tag, RN FROM #x
ORDER BY Account,RN;
OPEN c;
FETCH c INTO @account,@Budget_Remaining, @Amount, @RunningBalance , @Budget_To_Tag, @RN;
WHILE @@FETCH_STATUS = 0
BEGIN
--IF @RN = 1
--SET @RunningBalance = @Budget_Remaining;
--ELSE
SET @RunningBalance = @Budget_Remaining - @Amount;
UPDATE #x SET Budget_Remaining = @RunningBalance WHERE Account = @account and @RN + 1 = RN;
UPDATE #x SET RunningBalance = @RunningBalance WHERE Account = @account and @RN = RN;
FETCH c INTO @account,@Budget_Remaining, @Amount, @RunningBalance , @Budget_To_Tag, @RN;
END
CLOSE c; DEALLOCATE c;
UPDATE #x
SET Budget_To_Tag = RunningBalance
WHERE RunningBalance > Amount
select * from #X
Here's what I get when I run your sample data code. See'dem'errors? If you can provide some readily consumable data that actually works, I'll show you how you can do this on a million rows in about 3 seconds. Well, unless you're happy with the cursor. 😛
Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'Account_Tagging', because it does not exist or you do not have permission.
(1 row(s) affected)
(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 11
Violation of PRIMARY KEY constraint 'PK__Account___B0C3AC47165A2CA1'. Cannot insert duplicate key in object 'dbo.Account_Tagging'. The duplicate key value is (10-60001-603-69-0).
The statement has been terminated.
Msg 2627, Level 14, State 1, Line 12
Violation of PRIMARY KEY constraint 'PK__Account___B0C3AC47165A2CA1'. Cannot insert duplicate key in object 'dbo.Account_Tagging'. The duplicate key value is (10-60001-603-69-0).
The statement has been terminated.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 16
Violation of PRIMARY KEY constraint 'PK__Account___B0C3AC47165A2CA1'. Cannot insert duplicate key in object 'dbo.Account_Tagging'. The duplicate key value is (10-60001-605-65-0).
The statement has been terminated.
Msg 2627, Level 14, State 1, Line 17
Violation of PRIMARY KEY constraint 'PK__Account___B0C3AC47165A2CA1'. Cannot insert duplicate key in object 'dbo.Account_Tagging'. The duplicate key value is (10-60001-605-65-0).
The statement has been terminated.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 17, 2016 at 7:50 am
Hi Jeff,
I have modified the original script. Here's the entire script (DDL + Cursor Script)
IF OBJECT_ID (N'dbo.Account_Tagging', N'U') IS NOT NULL
AND EXISTS(SELECT * from dbo.Account_Tagging)
drop table Account_Tagging
BEGIN
CREATE TABLE Account_Tagging(
Account VARCHAR(17) NOT NULL
,Budget_Remaining INTEGER NOT NULL
,Amount NUMERIC(7,2) NOT NULL
,Budget_To_Tag BIT NOT NULL
,RN INTEGER NOT NULL
);
INSERT INTO Account_Tagging(Account,Budget_Remaining,Amount,Budget_To_Tag,RN) VALUES ('10-60001-600-65-0',5124,2500,0,1);
INSERT INTO Account_Tagging(Account,Budget_Remaining,Amount,Budget_To_Tag,RN) VALUES ('10-60001-603-69-0',12992,3100,0,1);
INSERT INTO Account_Tagging(Account,Budget_Remaining,Amount,Budget_To_Tag,RN) VALUES ('10-60001-603-69-0',0,2750,0,2);
INSERT INTO Account_Tagging(Account,Budget_Remaining,Amount,Budget_To_Tag,RN) VALUES ('10-60001-603-69-0',0,1000,0,3);
INSERT INTO Account_Tagging(Account,Budget_Remaining,Amount,Budget_To_Tag,RN) VALUES ('10-60001-604-65-0',617,700,0,1);
INSERT INTO Account_Tagging(Account,Budget_Remaining,Amount,Budget_To_Tag,RN) VALUES ('10-60001-604-69-0',3604,425,0,1);
INSERT INTO Account_Tagging(Account,Budget_Remaining,Amount,Budget_To_Tag,RN) VALUES ('10-60001-605-65-0',1182,1006.25,0,1);
INSERT INTO Account_Tagging(Account,Budget_Remaining,Amount,Budget_To_Tag,RN) VALUES ('10-60001-605-65-0',0,758,0,2);
INSERT INTO Account_Tagging(Account,Budget_Remaining,Amount,Budget_To_Tag,RN) VALUES ('10-60001-605-65-0',0,695,0,3);
END
BEGIN
CREATE TABLE #x
(
Account varchar(20),
Budget_Remaining float,
Amount float,
RunningBalance float,
Budget_To_Tag float,
RN varchar(20)
);
INSERT #x(Account,Budget_Remaining,Amount,RunningBalance,Budget_To_Tag,RN)
SELECT Account,Budget_Remaining,Amount, 0 RunningBalance,Budget_To_Tag,RN
FROM Account_Tagging
ORDER BY Account,RN;
DECLARE @account varchar(20), @Budget_Remaining float,@Amount float,@RunningBalance float,
@Budget_To_Tag float,@RN varchar(20);
--SET @RunningBalance = 0;
DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT Account,Budget_Remaining,Amount,0 RunningBalance,Budget_To_Tag, RN FROM #x
ORDER BY Account,RN;
OPEN c;
FETCH c INTO @account,@Budget_Remaining, @Amount, @RunningBalance , @Budget_To_Tag, @RN;
WHILE @@FETCH_STATUS = 0
BEGIN
--IF @RN = 1
--SET @RunningBalance = @Budget_Remaining;
--ELSE
SET @RunningBalance = @Budget_Remaining - @Amount;
UPDATE #x SET Budget_Remaining = @RunningBalance WHERE Account = @account and @RN + 1 = RN;
UPDATE #x SET RunningBalance = @RunningBalance WHERE Account = @account and @RN = RN;
FETCH c INTO @account,@Budget_Remaining, @Amount, @RunningBalance , @Budget_To_Tag, @RN;
END
CLOSE c; DEALLOCATE c;
UPDATE #x
SET Budget_To_Tag = RunningBalance
WHERE RunningBalance > Amount
select * from Account_Tagging
select * from #X
drop table #X
END
July 15, 2016 at 7:11 pm
venkyzrocks (6/17/2016)
Hi Jeff,I have modified the original script. Here's the entire script (DDL + Cursor Script)
IF OBJECT_ID (N'dbo.Account_Tagging', N'U') IS NOT NULL
AND EXISTS(SELECT * from dbo.Account_Tagging)
drop table Account_Tagging
BEGIN
CREATE TABLE Account_Tagging(
Account VARCHAR(17) NOT NULL
,Budget_Remaining INTEGER NOT NULL
,Amount NUMERIC(7,2) NOT NULL
,Budget_To_Tag BIT NOT NULL
,RN INTEGER NOT NULL
);
INSERT INTO Account_Tagging(Account,Budget_Remaining,Amount,Budget_To_Tag,RN) VALUES ('10-60001-600-65-0',5124,2500,0,1);
INSERT INTO Account_Tagging(Account,Budget_Remaining,Amount,Budget_To_Tag,RN) VALUES ('10-60001-603-69-0',12992,3100,0,1);
INSERT INTO Account_Tagging(Account,Budget_Remaining,Amount,Budget_To_Tag,RN) VALUES ('10-60001-603-69-0',0,2750,0,2);
INSERT INTO Account_Tagging(Account,Budget_Remaining,Amount,Budget_To_Tag,RN) VALUES ('10-60001-603-69-0',0,1000,0,3);
INSERT INTO Account_Tagging(Account,Budget_Remaining,Amount,Budget_To_Tag,RN) VALUES ('10-60001-604-65-0',617,700,0,1);
INSERT INTO Account_Tagging(Account,Budget_Remaining,Amount,Budget_To_Tag,RN) VALUES ('10-60001-604-69-0',3604,425,0,1);
INSERT INTO Account_Tagging(Account,Budget_Remaining,Amount,Budget_To_Tag,RN) VALUES ('10-60001-605-65-0',1182,1006.25,0,1);
INSERT INTO Account_Tagging(Account,Budget_Remaining,Amount,Budget_To_Tag,RN) VALUES ('10-60001-605-65-0',0,758,0,2);
INSERT INTO Account_Tagging(Account,Budget_Remaining,Amount,Budget_To_Tag,RN) VALUES ('10-60001-605-65-0',0,695,0,3);
END
BEGIN
CREATE TABLE #x
(
Account varchar(20),
Budget_Remaining float,
Amount float,
RunningBalance float,
Budget_To_Tag float,
RN varchar(20)
);
INSERT #x(Account,Budget_Remaining,Amount,RunningBalance,Budget_To_Tag,RN)
SELECT Account,Budget_Remaining,Amount, 0 RunningBalance,Budget_To_Tag,RN
FROM Account_Tagging
ORDER BY Account,RN;
DECLARE @account varchar(20), @Budget_Remaining float,@Amount float,@RunningBalance float,
@Budget_To_Tag float,@RN varchar(20);
--SET @RunningBalance = 0;
DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT Account,Budget_Remaining,Amount,0 RunningBalance,Budget_To_Tag, RN FROM #x
ORDER BY Account,RN;
OPEN c;
FETCH c INTO @account,@Budget_Remaining, @Amount, @RunningBalance , @Budget_To_Tag, @RN;
WHILE @@FETCH_STATUS = 0
BEGIN
--IF @RN = 1
--SET @RunningBalance = @Budget_Remaining;
--ELSE
SET @RunningBalance = @Budget_Remaining - @Amount;
UPDATE #x SET Budget_Remaining = @RunningBalance WHERE Account = @account and @RN + 1 = RN;
UPDATE #x SET RunningBalance = @RunningBalance WHERE Account = @account and @RN = RN;
FETCH c INTO @account,@Budget_Remaining, @Amount, @RunningBalance , @Budget_To_Tag, @RN;
END
CLOSE c; DEALLOCATE c;
UPDATE #x
SET Budget_To_Tag = RunningBalance
WHERE RunningBalance > Amount
select * from Account_Tagging
select * from #X
drop table #X
END
Oh, my apologies. I lost track of this thread.
Are you all set with both functionality and performance or do you still need some help?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 15, 2016 at 8:28 pm
TheSQLGuru (6/7/2016)
Believe it or not, prior to SQL Server 2012 and it's enhanced Windowing Function support the most efficient way to do running-total style calculations in SQL Server is to use a cursor. This despite how horribly inefficient those are in the SQL Server engine. The quadratic performance degradation you get from the join required just crushes you as the number of rows grows.Here is a good link (which include stuff about the "Quirky Update" but you will want to read about that here on SSC.com if you want to pursue it):
http://sqlperformance.com/2012/07/t-sql-queries/running-totals
http://sqlperformance.com/2014/01/t-sql-queries/grouped-running-totals
Gosh, be careful with recommending those particular articles. Once again, that author has copied someone else's work and bastardized the code so that it doesn't work nearly as well. If you think for a minute that the Quirky Update could be beat by any form of RBAR, especially a Cursor, then you're thinking incorrectly. The author also didn't follow the rules for the Quirky Update.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 16, 2016 at 6:18 am
Hi Jeff,
I mean my code is working fine, but I would like to learn if there are better ways to do this without cursors in 2008. Let me know if there is ? or we can close this thread.
Thanks,
V
July 16, 2016 at 12:47 pm
Post removed.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 16, 2016 at 1:08 pm
Post removed.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 16, 2016 at 1:10 pm
Post Removed.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 16, 2016 at 1:18 pm
venkyzrocks (7/16/2016)
Hi Jeff,I mean my code is working fine, but I would like to learn if there are better ways to do this without cursors in 2008. Let me know if there is ? or we can close this thread.
Thanks,
V
As a matter of fact, there is. It's known as the "Quirky Update", a term coined by Robyn Page back in 2007 (https://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-cursor-workbench/). A lot of people consider it to be dangerous code because 1) they don't actually know how to use it properly, 2) Microsoft has explicitly stated that they don't support the method, and 3) people are afraid of what an optimizer change could do. On that note, I've been using this method since SQL Server 7 and others have been using it since the birth day of SQL Server. Thanks to the suggestion of Paul White and Tom Thompson, I added a safety feature to the method that will detect if an optimizer change ever causes it to go south, at which time you could simply change over to the new aggregates if you have 2012 and above. You could also go back to a Cursor if they ever update 2008 and break it. Again, though... this method has worked for all versions of SQL Server from the beginning of time through the current 2016 version and is almost as fast as the new aggregates available in 2012 and above.
First, this code works in just about any version of SQL Server. I kept it that way so folks using earlier versions than 2008 can still benefit if they read this post. Of course, it still works in all versions through even 2016. It's not quite as fast as the updated aggregates that became available in 2012 but it's a hell of a lot faster than any form of RBAR including Triangular Self Joins, Recursive CTEs, Cursors, While Loops, and Recursive Functions. This will normally do it's thing on a million rows in just seconds if output to disk instead of going to the "great equalizer", which is the display.
Second, let's make it so the test data code you provided actually works. There are also some interesting tips in the comments in this code pertaining to your original table and a couple of other things. I normally use a Temp Table here to be super safe with the conditional DROP TABLE but wanted to follow your original example as closely as possible.
--=================================================================================================
-- Create the test table.
-- Note that nothing in this section is a part of the solution.
--=================================================================================================
--===== Do this test in TempDB so we don't accidentally drop a production table.
USE tempdb
;
--===== If the test table exists, drop it to make reruns easier here.
-- Note the extra protection of adding tempdb to the table detection
-- to help prevent accidental dropping of a production table.
IF OBJECT_ID (N'tempdb.dbo.Account_Tagging', N'U') IS NOT NULL
DROP TABLE dbo.Account_Tagging
;
--===== Create the original given test table.
-- Note that the datatype disparity between Budget_Remaining,
-- Amount, and Budget_To_Tag make no sense to me.
-- They should probably all be DECIMAL(19,2) (9 bytes) or
-- DECIMAL(9.2) (5 bytes) but, regardless, they should all
-- have exactly the same datatype to prevent the overhead
-- of implicit conversions.
CREATE TABLE Account_Tagging(
Account VARCHAR(17) NOT NULL
,Budget_Remaining INTEGER NOT NULL
,Amount NUMERIC(7,2) NOT NULL
,Budget_To_Tag BIT NOT NULL
,RN INTEGER NOT NULL
);
--===== Populate the test table with test data.
-- This method can be used in all versions of SQL Server.
INSERT INTO dbo.Account_Tagging
(Account,Budget_Remaining,Amount,Budget_To_Tag,RN)
SELECT '10-60001-600-65-0' ,5124 ,2500 ,0,1 UNION ALL
SELECT '10-60001-603-69-0' ,12992 ,3100 ,0,1 UNION ALL
SELECT '10-60001-603-69-0' ,0 ,2750 ,0,2 UNION ALL
SELECT '10-60001-603-69-0' ,0 ,1000 ,0,3 UNION ALL
SELECT '10-60001-604-65-0' ,617 ,700 ,0,1 UNION ALL
SELECT '10-60001-604-69-0' ,3604 ,425 ,0,1 UNION ALL
SELECT '10-60001-605-65-0' ,1182 ,1006.25,0,1 UNION ALL
SELECT '10-60001-605-65-0' ,0 ,758 ,0,2 UNION ALL
SELECT '10-60001-605-65-0' ,0 ,695 ,0,3
;
--===== Display the contents of the temp table in the
-- expected order.
SELECT * FROM dbo.Account_Tagging ORDER BY Account,RN
;
Ok. Here's the "Quirky Update". It works just like you'd do it in managed code with a loop but uses the hidden loop behind every Select, Insert, Update, and Delete not-so-well-known as a "Pseudo Cursor". We have a fellow by the name of R. Barry Young to thank for that wonderfully descriptive and easy to remember term. It is anything but RBAR. It's "Set Based" code on steroids.
Like I said, this is NASTY FAST! Details are in the comments. Keep in mind that the UPDATE itself is a "Pseudo Cursor" loop behind the scenes.
--=================================================================================================
-- This section solves the problem using a "Quirky Update"
--=================================================================================================
--===== If the working table already exists, drop it to make reruns easier.
-- May be commented out if used in stored procedure because of temporary
-- session scope of Temp Tables but won't hurt anything if it stays.
IF OBJECT_ID (N'tempdb..#RunningTotal', N'U') IS NOT NULL
DROP TABLE #RunningTotal
;
--===== Create the working table as a Temp Table.
-- Read the commments here.
-- Also note the meaningful name. This isn't FORTRAN so don't just use #X!
-- The safety RN is to detect if MS ever messes up the optimizer causing this to no longer work.
CREATE TABLE #RunningTotal
(
SafetyRN INTEGER --Added this
,Account VARCHAR(17) --Changed this to match the source table
,Budget_Remaining DECIMAL(19,2) --Changed this to match my previous recommendation
,Amount DECIMAL(19,2) --Changed this to match my previous recommendation
,RunningBalance DECIMAL(19,2) --Changed this to match my previous recommendation
,Budget_To_Tag DECIMAL(19,2) --Changed this to match my previous recommendation
,RN INTEGER --Changed this to match the source table
,PRIMARY KEY CLUSTERED (Account,RN) --Having this is critical to the running total process
)
;
--===== Populate the working table. Even though this has a clustered index on it,
-- this will be fast and minimally logged because the table is empty and tempdb
-- uses the SIMPLE recovery model.
-- I also did an insert condition for RN=1 to prevent possible bad data problems
-- which may appear in the origianal table.
INSERT #RunningTotal
(SafetyRN,Account,Budget_Remaining,Amount,RunningBalance,Budget_To_Tag,RN)
SELECT SafetyRN = ROW_NUMBER() OVER (ORDER BY Account,RN)
,Account
,Budget_Remaining = CASE WHEN RN=1 THEN Budget_Remaining ELSE 0.0 END
,Amount
,RunningBalance = 0
,Budget_To_Tag = 0
,RN
FROM dbo.Account_Tagging --Get into the habit of always using 2 part naming for user objects!
; --Note that an ORDER BY isn't necessary here. The Clustered Index will take care of it during
--this single insert.
--===== Declare some obviously named variables
DECLARE @PrevAccount VARCHAR(17)
,@RunningBalance DECIMAL(19,2)
,@SafetyRN INT
;
/****** NOTE THAT EVERYTHING UP TO THIS POINT IS PRETTY MUCH THE SAME AS A CURSOR SETUP *****/
--===== Preset the safety counter to 1
SELECT @SafetyRN = 1
;
--===== Do the Quirky Update remembering that it's a loop behind the scenes.
-- It's made to process rows in the same logical order as the Cluster Index,
-- WHICH IS THE ONLY INDEX YOU SHOULD EVER TRY THIS WITH.
-- The TABLOCK helps performance a bit on larger tables.
-- MAXDOP 1 necessarily prevents parallelism, which could be deadly to this
-- inherently serial process.
UPDATE rt
SET @RunningBalance = RunningBalance --Uses @Variable = Column = Expression, just like in BOL.
= CASE --This is the "expression" part of the update
--Note that @Running Balance is the previous running balance JUST
--for this column.
WHEN Account = @PrevAccount THEN @RunningBalance - Amount
ELSE Budget_Remaining - Amount
END
,Budget_Remaining = CASE --Note that @RunningBalance is now the current balance because of above.
WHEN Account = @PrevAccount THEN @RunningBalance + Amount
ELSE Budget_Remaining
END
,Budget_To_Tag = CASE
WHEN @RunningBalance > 0 THEN @RunningBalance
ELSE 0
END
,@PrevAccount = Account
,@SafetyRN = CASE
WHEN SafetyRN = @SafetyRN THEN @SafetyRN + 1
ELSE 1/0 -- This will cause an error if the safeties ever get out of sync.
END
FROM #RunningTotal rt WITH (TABLOCK,INDEX(1))
OPTION (MAXDOP 1)
;
--===== Let's see what we have in the working table.
-- List individual columns if you don't want to include the SafetyRN in the output.
SELECT *
FROM #RunningTotal
ORDER BY Account, RN --Same as Clustered Index order so no sort will appear in execution plan
--but is still guaranteed.
;
Here's the output from the "Quirky Update".
SafetyRN Account Budget_Remaining Amount RunningBalance Budget_To_Tag RN
-------- ----------------- ---------------- ------- -------------- ------------- --
1 10-60001-600-65-0 5124.00 2500.00 2624.00 2624.00 1
2 10-60001-603-69-0 12992.00 3100.00 9892.00 9892.00 1
3 10-60001-603-69-0 9892.00 2750.00 7142.00 7142.00 2
4 10-60001-603-69-0 7142.00 1000.00 6142.00 6142.00 3
5 10-60001-604-65-0 617.00 700.00 -83.00 0.00 1
6 10-60001-604-69-0 3604.00 425.00 3179.00 3179.00 1
7 10-60001-605-65-0 1182.00 1006.25 175.75 175.75 1
8 10-60001-605-65-0 175.75 758.00 -582.25 0.00 2
9 10-60001-605-65-0 -582.25 695.00 -1277.25 0.00 3
(9 row(s) affected)
--Jeff Moden
Change is inevitable... Change for the better is not.
July 16, 2016 at 9:25 pm
Thanks Jeff, really helpful !!!
July 17, 2016 at 11:46 am
venkyzrocks (7/16/2016)
Thanks Jeff, really helpful !!!
My pleasure. Thank you for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply