October 22, 2012 at 12:37 pm
Okay, I spent the last 24 hrs looking for a solution...I am totally new to server sql and after 24 hrs my eyes are killing me and i must ask this question which has so much help online but I still cant fix this.
Okay. here is the deal, I have this
sum column: forcount
sorry if this was answered already but after 24 hrs, my back is hurting and i am starting to get frustrated...
DECLARE RunningTotalCursor
CURSOR LOCAL FAST_FORWARD FOR
SELECT rawtrxid, forcount, emppin
FROM dbo.rawtrx
ORDER BY rawtrxid
OPEN RunningTotalCursor
DECLARE @rawtrxid INT
DECLARE @forcount int
declare @emppin int
DECLARE @RunningTotal int
SET @RunningTotal = 0
DECLARE @Results TABLE
(
rawtrxid INT NOT NULL PRIMARY KEY,
forcount int,
emppin int,
RunningTotal int
)
FETCH NEXT FROM RunningTotalCursor
INTO @rawtrxid, @forcount, @emppin
WHILE @@FETCH_STATUS = 0
BEGIN
SET @RunningTotal = @RunningTotal + @forcount
INSERT @Results
VALUES (@rawtrxid, @forcount, @emppin, @RunningTotal)
FETCH NEXT FROM RunningTotalCursor
INTO @rawtrxid, @forcount, @emppin
END
CLOSE RunningTotalCursor
DEALLOCATE RunningTotalCursor
SELECT *
FROM @Results
ORDER BY rawtrxid
the results:
rawtrxid forcount emppin RunningTotal
----------- ----------- ----------- ------------
7 1 2 1
8 1 2 2
9 1 2 3
11 1 1 4
12 1 1 5
13 1 2 6
I need the running sum to restart when the employee pin changes, so it should read:
rawtrxid forcount emppin RunningTotal
----------- ----------- ----------- ------------
7 1 2 1
8 1 2 2
9 1 2 3
11 1 1 1
12 1 1 2
13 1 2 1
October 22, 2012 at 12:43 pm
The running total problem is a difficult one in sql server. A cursor is one way to do this, however it is incredibly slow for this type of thing. Take a look at this article. http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]
In there it explains a process called the quirky update. It makes this type of thing a lot easier to work with. If you can't get it, You need to post ddl ( create tables scripts), sample data (insert statements) and desired output based on your sample data. Take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 22, 2012 at 12:43 pm
24 hours? Get some sleep!!
I think you can do this without using a cursor, but can't be sure without seeing more info about the source. Are you able to provide source DDL and sample data (corresponding with the desired results you have already posted)?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 22, 2012 at 12:57 pm
I read that article about 100 times.
I read about 100 articles comparing different types of running sum...of which:
clr is the best way to do it but i cant get pass cursor so how am i going to do clr?
nested subqueries are too slow...and way too slow too, i have this in ms access
update query thing...i tried but for some reason I dont seem to get it....
requested data:
table: RawTrx
Columns:
RawTrxIdintUnchecked
EmpNamechar(50)Checked
EmpPinnumeric(18, 0)Checked
trxDatedatetimeChecked
trxTimedatetimeChecked
forkeychar(50)Checked
fordeletebitChecked
forcountnumeric(1, 0)Checked
checked is allow null...
rawtrxid is primary key auto number.
I tried putting a where clause but gets a syntax error each time and every place i put it.
October 22, 2012 at 1:10 pm
Please help us by posting the info which Sean itemised.
If you take the time to do that, someone on this forum will post a working solution within an hour, I almost guarantee it. You are tired & we can help you if you help us with the fundamentals.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 22, 2012 at 1:10 pm
sdhanpaul (10/22/2012)
I read that article about 100 times.I read about 100 articles comparing different types of running sum...of which:
clr is the best way to do it but i cant get pass cursor so how am i going to do clr?
nested subqueries are too slow...and way too slow too, i have this in ms access
update query thing...i tried but for some reason I dont seem to get it....
requested data:
table: RawTrx
Columns:
RawTrxIdintUnchecked
EmpNamechar(50)Checked
EmpPinnumeric(18, 0)Checked
trxDatedatetimeChecked
trxTimedatetimeChecked
forkeychar(50)Checked
fordeletebitChecked
forcountnumeric(1, 0)Checked
checked is allow null...
rawtrxid is primary key auto number.
I tried putting a where clause but gets a syntax error each time and every place i put it.
You are doing this in ACCESS???? I wouldn't begin to know how to do this in access.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 22, 2012 at 1:19 pm
Im doing it in sql server 2005...management studio express
I used the nested subquery method in ms access before and it was slow.
You need to post ddl ( create tables scripts), sample data (insert statements) and desired output based on your sample data.
I have no idea what the ddl is.
I posted the sample results and the desired results...
I'm not sure what else I need to post...
Sorry if i seem to be hard headed...
October 22, 2012 at 1:23 pm
sdhanpaul (10/22/2012)
Im doing it in sql server 2005...management studio expressI used the nested subquery method in ms access before and it was slow.
You need to post ddl ( create tables scripts), sample data (insert statements) and desired output based on your sample data.
I have no idea what the ddl is.
I posted the sample results and the desired results...
I'm not sure what else I need to post...
Sorry if i seem to be hard headed...
Have a look here for a random thread which includes DDL and INSERTs.
We need sample source data - not just the results.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 22, 2012 at 1:36 pm
no inserts.
no ddls
sample data:
rawtrxid forcount emppin RunningTotal
----------- ----------- ----------- ------------
7 1 2 1
8 1 2 2
9 1 2 3
11 1 1 4
12 1 1 5
13 1 2 6
that is the sample data...i am running a very small database right now...the only column that is not in the sample data source is the running total.
anyways, thanks for your time.
If anyone every figures this out...i'll listen. otherwise, i going and get some sleep.
Thanks.
October 22, 2012 at 2:09 pm
sdhanpaul (10/22/2012)
no inserts.no ddls
sample data:
rawtrxid forcount emppin RunningTotal
----------- ----------- ----------- ------------
7 1 2 1
8 1 2 2
9 1 2 3
11 1 1 4
12 1 1 5
13 1 2 6
that is the sample data...i am running a very small database right now...the only column that is not in the sample data source is the running total.
anyways, thanks for your time.
If anyone every figures this out...i'll listen. otherwise, i going and get some sleep.
Thanks.
Yes you are tired. Go get some sleep. I think your ddl would look like this.
create table #rawtrxid
(
RawTrxID int,
ForCount int,
EmpPin int,
Total int
)
insert #rawtrxid
select 7, 1, 2, null union all
select 8, 1, 2, null union all
select 9, 1, 2, null union all
select 11, 1, 1, null union all
select 12, 1, 1, null union all
select 13, 1, 2, null
select * from #rawtrxid
See now how when we have something to work with we can make this happen? Without this there is nothing for anybody to write sql against.
So starting with that as your base, your cursor was so close. You just need a way to know what the previous EmpPin was as you process each agonizing row.
This is your modified cursor which now returns what you stated you want.
--Here is your cursor method
DECLARE RunningTotalCursor
CURSOR LOCAL FAST_FORWARD FOR
SELECT rawtrxid, forcount, emppin
FROM #rawtrxid
ORDER BY rawtrxid
OPEN RunningTotalCursor
DECLARE @rawtrxid INT
DECLARE @forcount int
declare @emppin int
DECLARE @RunningTotal int
SET @RunningTotal = 0
declare @PrevEmpPin int = -1
DECLARE @Results TABLE
(
rawtrxid INT NOT NULL PRIMARY KEY,
forcount int,
emppin int,
RunningTotal int
)
FETCH NEXT FROM RunningTotalCursor
INTO @rawtrxid, @forcount, @emppin
WHILE @@FETCH_STATUS = 0
BEGIN
if @emppin <> @PrevEmpPin
begin
Set @RunningTotal = 0
Set @PrevEmpPin = @emppin
end
SET @RunningTotal = @RunningTotal + @forcount
INSERT @Results
VALUES (@rawtrxid, @forcount, @emppin, @RunningTotal)
FETCH NEXT FROM RunningTotalCursor
INTO @rawtrxid, @forcount, @emppin
END
CLOSE RunningTotalCursor
DEALLOCATE RunningTotalCursor
SELECT *
FROM @Results
ORDER BY rawtrxid
However, we really aren't done here. This is not only incredibly wordy and hard to follow it will perform pretty poorly on even medium sized data sets. This is where quirky update comes in. Here we can produce the same result from the same source data. However, the code is only a few lines and it will perform WAY faster.
--Here is the quirky update method
declare @PrevEmpPin int
declare @RunningTotal int = 0
update #rawtrxid
set @RunningTotal = Total = case when EmpPin = @PrevEmpPin then @RunningTotal + ForCount Else ForCount End,
@PrevEmpPin = EmpPin
from #rawtrxid WITH (TABLOCKX)
OPTION (MAXDOP 1)
select * from #rawtrxid
Now of course we need to drop our temp table so we can run this again.
drop table #rawtrxid
Both the cursor and the quirky update produce the same results and they both match what you stated you want as output. Let me know if that helps.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 22, 2012 at 2:58 pm
Thanks for the reply...I went, took a shower n couldnt sleep till i got this thing figured...so i worked with the quirky update thing...got this:
/*************************************************************************************
Pseduo-cursor Running Total update using the "Quirky Update" takes about 4 seconds
on my box.
*************************************************************************************/
--===== Supress the auto-display of rowcounts for speed an appearance
SET NOCOUNT ON
--===== Declare the working variables
DECLARE @rawtrxid INT
DECLARE @forcount int
declare @emppin int
declare @runningtotal int
declare @trxdate datetime
--===== Update the running total for this row using the "Quirky Update"
-- and a "Pseudo-cursor"
UPDATE dbo.rawtrx
SET @RunningTotal = RunningTotal = CASE
WHEN emppin = @emppin and trxdate = @trxdate
THEN @RunningTotal+forcount
ELSE forcount
END,
@emppin = emppin
FROM dbo.rawtrx WITH (TABLOCKX)
OPTION (MAXDOP 1)
GO
got the results i wanted...
will try ur solutions now...
October 22, 2012 at 3:03 pm
sdhanpaul (10/22/2012)
Thanks for the reply...I went, took a shower n couldnt sleep till i got this thing figured...so i worked with the quirky update thing...got this:
/*************************************************************************************
Pseduo-cursor Running Total update using the "Quirky Update" takes about 4 seconds
on my box.
*************************************************************************************/
--===== Supress the auto-display of rowcounts for speed an appearance
SET NOCOUNT ON
--===== Declare the working variables
DECLARE @rawtrxid INT
DECLARE @forcount int
declare @emppin int
declare @runningtotal int
declare @trxdate datetime
--===== Update the running total for this row using the "Quirky Update"
-- and a "Pseudo-cursor"
UPDATE dbo.rawtrx
SET @RunningTotal = RunningTotal = CASE
WHEN emppin = @emppin and trxdate = @trxdate
THEN @RunningTotal+forcount
ELSE forcount
END,
@emppin = emppin
FROM dbo.rawtrx WITH (TABLOCKX)
OPTION (MAXDOP 1)
GO
got the results i wanted...
will try ur solutions now...
Not sure this is really what you want. You probably need to add @trxdate = trxdate as a third column in your update. Otherwise the @RunningTotal + forcount portion of your case expression will only evaluate to true when there is a NULL for trxdate...I suspect that is not what you want. 😉 Otherwise this looks like it should work fine.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 22, 2012 at 3:13 pm
trxdate is never null.
I modified what you did with the cursor...and it works:
--Here is your cursor method
DECLARE RunningTotalCursor
CURSOR LOCAL FAST_FORWARD FOR
SELECT rawtrxid, forcount, emppin
FROM rawtrx
ORDER BY rawtrxid
OPEN RunningTotalCursor
DECLARE @rawtrxid INT
DECLARE @forcount int
declare @emppin int
declare @PrevEmpPin int
DECLARE @RunningTotal int
SET @RunningTotal = 0
set @PrevEmpPin = -1
DECLARE @Results TABLE
(
rawtrxid INT NOT NULL PRIMARY KEY,
forcount int,
emppin int,
RunningTotal int
)
FETCH NEXT FROM RunningTotalCursor
INTO @rawtrxid, @forcount, @emppin
WHILE @@FETCH_STATUS = 0
BEGIN
if @emppin <> @PrevEmpPin
begin
Set @RunningTotal = 0
Set @PrevEmpPin = @emppin
end
SET @RunningTotal = @RunningTotal + @forcount
INSERT @Results
VALUES (@rawtrxid, @forcount, @emppin, @RunningTotal)
FETCH NEXT FROM RunningTotalCursor
INTO @rawtrxid, @forcount, @emppin
END
CLOSE RunningTotalCursor
DEALLOCATE RunningTotalCursor
SELECT *
FROM @Results
ORDER BY rawtrxid
I think I can take it from here...Thanks for the help.
I'm sure there are many others out there with this problem...it basically solves those running sum where you have to reset the running sum based on changes in a particular column,...
sales by salesperson by month...as for having it in order, well the autonumber takes care of that.
ps...in typing this last message i tried to use as much key words as i used within the last 24+ hrs trying to get it..that way, someone else can be helped...
October 22, 2012 at 3:21 pm
I modified what you did with the cursor...and it works:
I posted that to show you what you had missed. I would HIGHLY recommend you NOT use the cursor approach for this. The quirky update approach I posted provides exactly the same output but is immensely faster than a cursor.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 22, 2012 at 3:59 pm
Okay,
Well, I have to admit...i was focused on the wrong thing all the time.
managed to get both methods working...10006 rows later, update was faster..by 11 seconds!
means I can do away with the cursor for now...I will still keep it though as it might just come in handy later on...I will post the final findings of the entire thing for anyone's else future reference.
--Here is your cursor method
-- Select the source for the running sum
DECLARE RunningTotalCursor
CURSOR LOCAL FAST_FORWARD FOR
SELECT rawtrxid, forcount, emppin, trxdate
FROM rawtrx
ORDER BY rawtrxid
OPEN RunningTotalCursor --open the cursor...
--declare the fields
DECLARE @rawtrxid INT
DECLARE @forcount int
declare @emppin int
declare @PrevEmpPin int -- notes1
declare @trxdate datetime
declare @prevTrxdate as datetime --notes1
DECLARE @RunningTotal int
SET @RunningTotal = 0
set @PrevEmpPin = -1 --notes1
set @prevtrxdate = -1 --notes1
--notes1>>
--Okay, doesn't matter how hard you try, where clause or whatever will never work...cursor returns row by row
--So you need to tell the proc. what you are comparing it to...sort of like your where clause
--declare the results table
DECLARE @Results TABLE
(
rawtrxid INT NOT NULL PRIMARY KEY,
forcount int,
emppin int,
trxdate datetime,
RunningTotal int
)
--start the magic...
FETCH NEXT FROM RunningTotalCursor
INTO @rawtrxid, @forcount, @emppin, @trxdate
WHILE @@FETCH_STATUS = 0
BEGIN
if @emppin <> @PrevEmpPin or @trxdate <> @prevtrxdate --the official where clause...note the "or" operator
begin
Set @RunningTotal = 0
Set @PrevEmpPin = @emppin
set @prevtrxdate = @trxdate
end
SET @RunningTotal = @RunningTotal + @forcount
INSERT @Results
VALUES (@rawtrxid, @forcount, @emppin, @trxdate, @RunningTotal)
FETCH NEXT FROM RunningTotalCursor
INTO @rawtrxid, @forcount, @emppin, @trxdate
END
CLOSE RunningTotalCursor
DEALLOCATE RunningTotalCursor
SELECT *
FROM @Results
ORDER BY rawtrxid
--play around with the two things you are checking for...trxdate ad emppin....This can be by month and by sales person.
--very useful! runs 10006 records in 10 seconds...a nested query with these criteria would take two days...trust me. I know.
--I have one in ms access...if you want to reduce the time it takes, you can add filters
Okay, thats that...now to move on with the project!
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply