DECLARE RunningTotalCursorCURSOR LOCAL FAST_FORWARD FOR SELECT rawtrxid, forcount, emppin FROM dbo.rawtrx ORDER BY rawtrxid OPEN RunningTotalCursorDECLARE @rawtrxid INTDECLARE @forcount intdeclare @emppin intDECLARE @RunningTotal intSET @RunningTotal = 0DECLARE @Results TABLE( rawtrxid INT NOT NULL PRIMARY KEY, forcount int, emppin int, RunningTotal int)FETCH NEXT FROM RunningTotalCursorINTO @rawtrxid, @forcount, @emppinWHILE @@FETCH_STATUS = 0BEGIN SET @RunningTotal = @RunningTotal + @forcount INSERT @Results VALUES (@rawtrxid, @forcount, @emppin, @RunningTotal) FETCH NEXT FROM RunningTotalCursor INTO @rawtrxid, @forcount, @emppinENDCLOSE RunningTotalCursorDEALLOCATE RunningTotalCursorSELECT *FROM @ResultsORDER BY rawtrxid
rawtrxid forcount emppin RunningTotal----------- ----------- ----------- ------------7 1 2 18 1 2 29 1 2 311 1 1 412 1 1 513 1 2 6
rawtrxid forcount emppin RunningTotal----------- ----------- ----------- ------------7 1 2 18 1 2 29 1 2 311 1 1 112 1 1 213 1 2 1
RawTrxId int UncheckedEmpName char(50) CheckedEmpPin numeric(18, 0) CheckedtrxDate datetime CheckedtrxTime datetime Checkedforkey char(50) Checkedfordelete bit Checkedforcount numeric(1, 0) Checked
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, nullselect * from #rawtrxid
--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
--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
drop table #rawtrxid