December 10, 2010 at 1:37 pm
Hello everyone. Got a bit of a challenge. All code for testing is below. What I am looking for is kinda a running total. For each subject (aka customer) in our system, we want to count the number of orders placed during a time frame. However, any return orders will count against you immedidately (so you can't just subract the total normal orders from the total return orders). The reason for this is that for every X order, you would get a special discount on that order. Now, I figure I need to do a running total of the OrderType (called MT in code below). Now the results for the test below are accurate. However, I would like to remove the WHILE loop completely and have it all set based. However, I cannot figure out how to do it.
Additionally, the UPDATE code with the CTE, I borrowed from the following website http://weblogs.sqlteam.com/mladenp/archive/2009/07/28/SQL-Server-2005-Fast-Running-Totals.aspx. I understand most of it. However, I don't understand the following: SET @Total = Total = @Total + MT. Can any explain to me what specifically this is doing.
Thanks for the assistance and the knowledge powerup.
Fraggle
if object_id('tempdb..#data') is not null
drop table #data
if object_id('tempdb..#Testdata') is not null
drop table #testdata
-- test data
CREATE TABLE #TestData
( Inc INT
, ID INT
, MT INT
, SubjectID INT
, OrderID INT
, Multiplier INT
, Total INT
)
INSERT INTO #testdata
SELECT 1, 1, 1, 123, 4, 1, 0 UNION ALL
SELECT 1, 2, 1, 123, 5, 1, 0 UNION ALL
SELECT 1, 3, 1, 123, 6, 1, 0 UNION ALL
SELECT 1, 4, -1, 123, 7, 1, 0 UNION ALL
SELECT 1, 5, 1, 123, 8, 1, 0 UNION ALL
SELECT 1, 6, 1, 123, 47, 1, 0 UNION ALL
SELECT 1, 7, -1, 123, 154, 1, 0 UNION ALL
SELECT 1, 8, -1, 123, 164, 1, 0 UNION ALL
SELECT 1, 9, 1, 123, 465, 1, 0 UNION ALL
SELECT 1, 10, 1, 123, 554, 1, 0 UNION ALL
SELECT 1, 11, 1, 123, 604, 1, 0 UNION ALL
SELECT 1, 12, -1, 123, 124, 1, 0 UNION ALL
SELECT 2, 1, 1, 456, 14, 1, 0 UNION ALL
SELECT 2, 2, 1, 456, 24, 1, 0 UNION ALL
SELECT 2, 3, -1, 456, 34, 1, 0 UNION ALL
SELECT 2, 4, 1, 456, 44, 1, 0 UNION ALL
SELECT 3, 1, 1, 789, 45, 1, 0 UNION ALL
SELECT 3, 2, -1, 789, 49, 1, 0
-- results tables
create table #data
(inc int
, subjectid int
, orderid int
, OrderQual int
, MT int
)
declare @Total int
, @x int
, @subjectid int
set @x = 1
-- loop for each subjectid (aka customer)
while 1=1
BEGIN
set @total = 0
if object_id('tempdb..#temp') is not null
drop table #temp
SELECT top 1 @subjectid = subjectid
FROM #testdata
WHERE inc = @x
if @@Rowcount = 0
break
-- drop results for individual into temptable
SELECT *
into #temp
FROM #testdata
WHERE subjectid = @subjectID
; with RunningTotal as
(
SELECT *
from #temp
)
update RunningTotal
SET @Total = Total = @Total + MT
output inserted.inc, inserted.subjectid, inserted.orderid, inserted.total, inserted.mt
into #data
set @x = @x + 1
END
SELECT *
FROM #data
December 10, 2010 at 1:57 pm
Fraggle-805517 (12/10/2010)
Hello everyone. Got a bit of a challenge. All code for testing is below. What I am looking for is kinda a running total. For each subject (aka customer) in our system, we want to count the number of orders placed during a time frame. However, any return orders will count against you immedidately (so you can't just subract the total normal orders from the total return orders). The reason for this is that for every X order, you would get a special discount on that order. Now, I figure I need to do a running total of the OrderType (called MT in code below). Now the results for the test below are accurate. However, I would like to remove the WHILE loop completely and have it all set based. However, I cannot figure out how to do it.Additionally, the UPDATE code with the CTE, I borrowed from the following website http://weblogs.sqlteam.com/mladenp/archive/2009/07/28/SQL-Server-2005-Fast-Running-Totals.aspx. I understand most of it. However, I don't understand the following: SET @Total = Total = @Total + MT. Can any explain to me what specifically this is doing.
Thanks for the assistance and the knowledge powerup.
Fraggle
if object_id('tempdb..#data') is not null
drop table #data
if object_id('tempdb..#Testdata') is not null
drop table #testdata
-- test data
CREATE TABLE #TestData
( Inc INT
, ID INT
, MT INT
, SubjectID INT
, OrderID INT
, Multiplier INT
, Total INT
)
INSERT INTO #testdata
SELECT 1, 1, 1, 123, 4, 1, 0 UNION ALL
SELECT 1, 2, 1, 123, 5, 1, 0 UNION ALL
SELECT 1, 3, 1, 123, 6, 1, 0 UNION ALL
SELECT 1, 4, -1, 123, 7, 1, 0 UNION ALL
SELECT 1, 5, 1, 123, 8, 1, 0 UNION ALL
SELECT 1, 6, 1, 123, 47, 1, 0 UNION ALL
SELECT 1, 7, -1, 123, 154, 1, 0 UNION ALL
SELECT 1, 8, -1, 123, 164, 1, 0 UNION ALL
SELECT 1, 9, 1, 123, 465, 1, 0 UNION ALL
SELECT 1, 10, 1, 123, 554, 1, 0 UNION ALL
SELECT 1, 11, 1, 123, 604, 1, 0 UNION ALL
SELECT 1, 12, -1, 123, 124, 1, 0 UNION ALL
SELECT 2, 1, 1, 456, 14, 1, 0 UNION ALL
SELECT 2, 2, 1, 456, 24, 1, 0 UNION ALL
SELECT 2, 3, -1, 456, 34, 1, 0 UNION ALL
SELECT 2, 4, 1, 456, 44, 1, 0 UNION ALL
SELECT 3, 1, 1, 789, 45, 1, 0 UNION ALL
SELECT 3, 2, -1, 789, 49, 1, 0
-- results tables
create table #data
(inc int
, subjectid int
, orderid int
, OrderQual int
, MT int
)
declare @Total int
, @x int
, @subjectid int
set @x = 1
-- loop for each subjectid (aka customer)
while 1=1
BEGIN
set @total = 0
if object_id('tempdb..#temp') is not null
drop table #temp
SELECT top 1 @subjectid = subjectid
FROM #testdata
WHERE inc = @x
if @@Rowcount = 0
break
-- drop results for individual into temptable
SELECT *
into #temp
FROM #testdata
WHERE subjectid = @subjectID
; with RunningTotal as
(
SELECT *
from #temp
)
update RunningTotal
SET @Total = Total = @Total + MT
output inserted.inc, inserted.subjectid, inserted.orderid, inserted.total, inserted.mt
into #data
set @x = @x + 1
END
SELECT *
FROM #data
First, the best article I know on running totals in T-SQL is here: http://www.sqlservercentral.com/articles/T-SQL/68467/
However, I prefer using a CLR function/proc to do running totals, because it doesn't have any of the caveats that one has. It just works. Slightly slower, but you don't have to worry about all kinds of weird details.
If you don't want to use CLR, then use a simple fast-forward or static cursor.
The piece of code you ask about is what's called a "quirky update" in SQL. Jeff talks about it in his article, or take a look at http://www.simple-talk.com and search for "quirky update" on there.
- 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
December 10, 2010 at 2:26 pm
GSquared,
Thanks for the input. Sadly, a CLR is not an option, even if I did know how to write one. Thanks for the note on the update and the link. I will review and see what I can come up with.
Fraggle
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply