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.