|
|
Free Software, Source Code, and Best-Practice Documentation Index from your
Cleveland Software, Database, Web Design, Consulting, and SQL / Delphi Experts
|
SQL Server - Other:
Set-Based Running Subtotals (i.e., Row-Level Accumulators)
|
|
Have you ever wanted to perform a set-based running-subtotals operation in SQL-Server (i.e., have row-level accumulators store a running total or running subtotal?) Want the solution to not use cursors?
Well, I'm going to present one cursorless method of implementing such functionality in a SQL-Server Query, Stored-Procedure, or Function. I have seen running totals done before, with varied methods, but what I have not seen much about elsewhere is how to do running subtotals. The code I wrote that appears below can easily do both, and does. It is easily adapted to do multi-column break-value running subtotals, as noted in the code comments.
Note: for this example, I used the SQL Server 2005 Adventureworks sample database, which provided me with a test case of approximately 114,000 rows against which I perform the running-subtotal (by product level) logic and do my row-level accumulator manipulations (using SET logic). This query is relatively efficient and took only 6 seconds to execute on my dual Xeon server with RAID array.
|
|
|
DBCC DROPCLEANBUFFERS -- Clears the data cache
DBCC FREEPROCCACHE -- Clears the procedure cache
GO
--I do the above two steps to make sure run-times aren't showing faster than reality (from prev cached data, etc)
declare @start datetime
set @start = getdate()
declare @RunningTotal money
set @RunningTotal=0.0
declare @RunningSubtotal money
set @RunningSubtotal=0.0
declare @SubTotalBreakValue1 INT
set @SubTotalBreakValue1 = -1 --Some value that will NOT exist in the real data for column in question
DECLARE @Results TABLE
(
UniqueID INT IDENTITY NOT NULL PRIMARY KEY,
SubtotalBreakColumn1 INT,
ActualCost MONEY,
RunningSubtotal MONEY,
RunningTotal MONEY
)
INSERT INTO @Results(
SubtotalBreakColumn1,
ActualCost,
RunningSubtotal,
RunningTotal)
SELECT
ProductID,
ActualCost,
0,
0
FROM
Production.TransactionHistory
ORDER BY
ProductID --Insert into table in our subtotal-breaking order (IMPORTANT!)
--Now, let's set those accumulators!
--The subtotal logic relies on the break-level-variable(s) having their values updated AFTER the subtotal is calc'd, since
--they are used for comparisons to "prior row" values essentially.
--This logic can easily be extended to support subtotal-breaks that rely on multiple-column values simply by altering the
--CASE statement to compare more than one column prior to resetting the Subtotal accumulator.
-- i.e., CASE WHEN (@SubTotalBreakValue1 = SubtotalBreakColumn1 AND @SubTotalBreakValue2 = SubtotalBreakColumn2 AND...) THEN @RunningSubtotal + ActualCost ELSE 0
UPDATE
@Results
SET
@RunningTotal = RunningTotal = @RunningTotal + ActualCost,
@RunningSubtotal = RunningSubtotal =
CASE
WHEN @SubTotalBreakValue1 = SubtotalBreakColumn1
THEN @RunningSubtotal + ActualCost
ELSE ActualCost
END,
@SubTotalBreakValue1= SubtotalBreakColumn1
--OUTPUT THE RESULTS...
select * from @results
--USING ADVENTUREWORKS, YOU'LL SEE A LARGE SET WITH DATA LIKE THIS:
--UniqueID SubtotalBreakColumn1 ActualCost RunningSubtotal RunningTotal
------------- -------------------- --------------------- --------------------- ---------------------
--1 1 50.2635 50.2635 50.2635
--2 1 50.2635 100.527 100.527
--3 1 50.2635 150.7905 150.7905
--4 1 50.2635 201.054 201.054
--5 1 50.2635 251.3175 251.3175
--...
--...
--44 1 50.2635 2211.594 2211.594
--45 1 50.2635 2261.8575 2261.8575
--46 2 41.916 41.916 2303.7735
--47 2 41.916 83.832 2345.6895
--...
--...
--113439 999 388.7928 286669.8912 27306553.5681
--113440 999 388.7928 287058.684 27306942.3609
--113441 999 388.7928 287447.4768 27307331.1537
--113442 999 0.00 287447.4768 27307331.1537
--113443 999 0.00 287447.4768 27307331.1537
---Show how long this all took!
print 'time taken = ' + cast(datediff(ms, @start, getdate()) as varchar(20)) |
|
