running sum cursor

  • sdhanpaul

    Right there with Babe

    Points: 787

    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

  • Sean Lange

    SSC Guru

    Points: 286442

    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/

  • Phil Parkin

    SSC Guru

    Points: 243681

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • sdhanpaul

    Right there with Babe

    Points: 787

    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:

    RawTrxId int Unchecked

    EmpName char(50) Checked

    EmpPin numeric(18, 0) Checked

    trxDate datetime Checked

    trxTime datetime Checked

    forkey char(50) Checked

    fordelete bit Checked

    forcount numeric(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.

  • Phil Parkin

    SSC Guru

    Points: 243681

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Sean Lange

    SSC Guru

    Points: 286442

    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:

    RawTrxId int Unchecked

    EmpName char(50) Checked

    EmpPin numeric(18, 0) Checked

    trxDate datetime Checked

    trxTime datetime Checked

    forkey char(50) Checked

    fordelete bit Checked

    forcount numeric(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/

  • sdhanpaul

    Right there with Babe

    Points: 787

    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...

  • Phil Parkin

    SSC Guru

    Points: 243681

    sdhanpaul (10/22/2012)


    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...

    Have a look here for a random thread which includes DDL and INSERTs.

    We need sample source data - not just the results.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • sdhanpaul

    Right there with Babe

    Points: 787

    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.

  • Sean Lange

    SSC Guru

    Points: 286442

    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/

  • sdhanpaul

    Right there with Babe

    Points: 787

    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...

  • Sean Lange

    SSC Guru

    Points: 286442

    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/

  • sdhanpaul

    Right there with Babe

    Points: 787

    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...

  • Sean Lange

    SSC Guru

    Points: 286442

    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/

  • sdhanpaul

    Right there with Babe

    Points: 787

    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 35 total)

You must be logged in to reply to this topic. Login to reply