Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
joe8079
Posting Yak Master
127 Posts |
Posted - 2013-04-07 : 16:44:11
|
I borrowed this example below from this blog: http://www.sqlperformance.com/2012/07/t-sql-queries/running-totalsI 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.hFROM x CROSS JOIN x AS x2ORDER BY d;GO SELECT [Date], TicketCount FROM dbo.SpeedingTickets ORDER BY [Date];GO---CODEDECLARE @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 = 0BEGIN 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 output1984-01-01 1 1984 22 221984-01-02 1 1984 63 851984-01-03 1 1984 88 1731984-01-04 1 1984 61 2341984-01-05 1 1984 6 2401984-01-06 1 1984 56 2961984-01-07 1 1984 9 3051984-01-08 1 1984 4 3091984-01-09 1 1984 95 4041984-01-10 1 1984 29 4331984-01-11 1 1984 58 4911984-01-12 1 1984 2 4931984-01-13 1 1984 73 5661984-01-14 1 1984 12 5781984-01-15 1 1984 47 6251984-01-16 1 1984 6 6311984-01-17 1 1984 33 6641984-01-18 1 1984 90 7541984-01-19 1 1984 28 7821984-01-20 1 1984 8 7901984-01-21 1 1984 72 8621984-01-22 1 1984 15 8771984-01-23 1 1984 88 9651984-01-24 1 1984 8 9731984-01-25 1 1984 73 10461984-01-26 1 1984 68 11141984-01-27 1 1984 0 11141984-01-28 1 1984 67 11811984-01-29 1 1984 29 12101984-01-30 1 1984 50 12601984-01-31 1 1984 84 13441984-02-01 2 1984 26 --need to reset,star new tota1984-02-02 2 1984 68 14381984-02-03 2 1984 60 14981984-02-04 2 1984 94 15921984-02-05 2 1984 8 16001984-02-06 2 1984 83 16831984-02-07 2 1984 89 17721984-02-08 2 1984 64 18361984-02-09 2 1984 73 19091984-02-10 2 1984 54 19631984-02-11 2 1984 11 19741984-02-12 2 1984 62 20361984-02-13 2 1984 32 20681984-02-14 2 1984 91 21591984-02-15 2 1984 63 22221984-02-16 2 1984 84 23061984-02-17 2 1984 12 23181984-02-18 2 1984 26 23441984-02-19 2 1984 44 23881984-02-20 2 1984 55 24431984-02-21 2 1984 93 25361984-02-22 2 1984 63 25991984-02-23 2 1984 36 26351984-02-24 2 1984 53 26881984-02-25 2 1984 33 27211984-02-26 2 1984 3 27241984-02-27 2 1984 55 27791984-02-28 2 1984 47 28261984-02-29 2 1984 7 28331984-03-01 3 1984 44 -need to reset start new tota1984-03-02 3 1984 8 28851984-03-03 3 1984 48 29331984-03-04 3 1984 22 29551984-03-05 3 1984 41 29961984-03-06 3 1984 69 3065 |
|
joe8079
Posting Yak Master
127 Posts |
Posted - 2013-04-07 : 16:46:03
|
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. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-07 : 20:36:55
|
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+TicketCountfrom dbo.SpeedingTickets t with (tablockx)option (maxdop 1); |
|
|
joe8079
Posting Yak Master
127 Posts |
Posted - 2013-04-07 : 22:38:07
|
thanks james, i'll give that a shot |
|
|
|
|
|
|
|