SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 help with running total
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

joe8079
Posting Yak Master

USA
127 Posts

Posted - 04/07/2013 :  16:44:11  Show Profile  Reply with Quote
I borrowed this example below from this blog:
http://www.sqlperformance.com/2012/07/t-sql-queries/running-totals

I understand cursors are bad and I never use them, but for this example and the one i'm using in production, cursor solution is the only way to go as the set based solution is taking way too long.
If you run the code below, it generates a running total, but I need the running total to reset for everytime it comes to a new month in that year. this works using set based logic, but its taking soo long to run, the cursor solution below works in about 3 seconds in production with a different dataset than the one below.



sample tables:
CREATE TABLE dbo.SpeedingTickets
(
[Date] DATE NOT NULL,
TicketCount INT
);
GO

ALTER TABLE dbo.SpeedingTickets ADD CONSTRAINT pk PRIMARY KEY CLUSTERED ([Date]);
GO

;WITH x(d,h) AS
(
SELECT TOP (250)
ROW_NUMBER() OVER (ORDER BY [object_id]),
CONVERT(INT, RIGHT([object_id], 2))
FROM sys.all_objects
ORDER BY [object_id]
)
INSERT dbo.SpeedingTickets([Date], TicketCount)
SELECT TOP (10000)
d = DATEADD(DAY, x2.d + ((x.d-1)*250), '19831231'),
x2.h
FROM x CROSS JOIN x AS x2
ORDER BY d;
GO

SELECT [Date], TicketCount
FROM dbo.SpeedingTickets
ORDER BY [Date];
GO




---CODE


DECLARE @st TABLE
(
[Date] DATE PRIMARY KEY,
month int,
year int,

TicketCount INT,
RunningTotal INT
);

DECLARE
@Date DATE,
@month int,
@year int,
@TicketCount INT,
@RunningTotal INT = 0;

DECLARE c CURSOR
LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT [Date], month([date]) Month,year([date]) year,TicketCount
FROM dbo.SpeedingTickets
ORDER BY [Date];

OPEN c;

FETCH NEXT FROM c INTO @Date,@month,@year, @TicketCount;


WHILE @@FETCH_STATUS = 0
BEGIN


SET @RunningTotal = @RunningTotal + @TicketCount;

INSERT @st([Date], [month], year, TicketCount, RunningTotal)
SELECT @Date,@month, @year, @TicketCount, @RunningTotal ;


FETCH NEXT FROM c INTO @Date, @month, @year, @TicketCount;
END

CLOSE c;
DEALLOCATE c;

SELECT [Date],month,year, TicketCount, RunningTotal
FROM @st
ORDER BY [Date], year, month








--desired output


1984-01-01 1 1984 22 22
1984-01-02 1 1984 63 85
1984-01-03 1 1984 88 173
1984-01-04 1 1984 61 234
1984-01-05 1 1984 6 240
1984-01-06 1 1984 56 296
1984-01-07 1 1984 9 305
1984-01-08 1 1984 4 309
1984-01-09 1 1984 95 404
1984-01-10 1 1984 29 433
1984-01-11 1 1984 58 491
1984-01-12 1 1984 2 493
1984-01-13 1 1984 73 566
1984-01-14 1 1984 12 578
1984-01-15 1 1984 47 625
1984-01-16 1 1984 6 631
1984-01-17 1 1984 33 664
1984-01-18 1 1984 90 754
1984-01-19 1 1984 28 782
1984-01-20 1 1984 8 790
1984-01-21 1 1984 72 862
1984-01-22 1 1984 15 877
1984-01-23 1 1984 88 965
1984-01-24 1 1984 8 973
1984-01-25 1 1984 73 1046
1984-01-26 1 1984 68 1114
1984-01-27 1 1984 0 1114
1984-01-28 1 1984 67 1181
1984-01-29 1 1984 29 1210
1984-01-30 1 1984 50 1260
1984-01-31 1 1984 84 1344
1984-02-01 2 1984 26 --need to reset,star new tota
1984-02-02 2 1984 68 1438
1984-02-03 2 1984 60 1498
1984-02-04 2 1984 94 1592
1984-02-05 2 1984 8 1600
1984-02-06 2 1984 83 1683
1984-02-07 2 1984 89 1772
1984-02-08 2 1984 64 1836
1984-02-09 2 1984 73 1909
1984-02-10 2 1984 54 1963
1984-02-11 2 1984 11 1974
1984-02-12 2 1984 62 2036
1984-02-13 2 1984 32 2068
1984-02-14 2 1984 91 2159
1984-02-15 2 1984 63 2222
1984-02-16 2 1984 84 2306
1984-02-17 2 1984 12 2318
1984-02-18 2 1984 26 2344
1984-02-19 2 1984 44 2388
1984-02-20 2 1984 55 2443
1984-02-21 2 1984 93 2536
1984-02-22 2 1984 63 2599
1984-02-23 2 1984 36 2635
1984-02-24 2 1984 53 2688
1984-02-25 2 1984 33 2721
1984-02-26 2 1984 3 2724
1984-02-27 2 1984 55 2779
1984-02-28 2 1984 47 2826
1984-02-29 2 1984 7 2833
1984-03-01 3 1984 44 -need to reset start new tota
1984-03-02 3 1984 8 2885
1984-03-03 3 1984 48 2933
1984-03-04 3 1984 22 2955
1984-03-05 3 1984 41 2996
1984-03-06 3 1984 69 3065

joe8079
Posting Yak Master

USA
127 Posts

Posted - 04/07/2013 :  16:46:03  Show Profile  Reply with Quote
note: i'm also using sql server 2008 and if I was on 2012, I would try the new windows functions, but thats not an option at the moment. this is the first time i've ever used a cursor, like I said, I never use them, but for this example, its the only thing thats running fast enough.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 04/07/2013 :  20:36:55  Show Profile  Reply with Quote
The quirky update probably is faster. However, it is an undocumented feature and you need to be careful - see Jeff Moden's article for a detailed analysis and explanation: http://www.sqlservercentral.com/articles/T-SQL/68467/

alter table dbo.SpeedingTickets add RunningTotal int;

declare @temp int;
set @temp = 0;
update t set
	@temp = RunningTotal = @temp+TicketCount
from
	dbo.SpeedingTickets t with (tablockx)
option (maxdop 1);
Go to Top of Page

joe8079
Posting Yak Master

USA
127 Posts

Posted - 04/07/2013 :  22:38:07  Show Profile  Reply with Quote
thanks james, i'll give that a shot
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000