Loop in SQL Server without a cursor

Question

The following is the SQL Server code I'm working on. Now I know that using the cursor is a bad idea in general, but I cannot figure out how else I can do this. The indicator is terrible with the cursor. I just use some simple IF statement logic with a loop, but can't translate it to SQL. I am using SQL Server 2012.

IF [Last Employee] = [Employee] AND [Action] = '1-HR' SET [Employee Record] = @counter + 1 ELSE IF [Last Employee] != [Employee] OR [Last Employee] IS NULL SET [Employee Record] = 1 ELSE SET [Employee Record] = @counter 

Basically, how can I save this @counter without a cursor. I feel that the solution is simple, but I lost myself. Thanks for watching.

 declare curr cursor for select WORKER, SEQUENCE, ACTION FROM [DB].[Transactional History] order by WORKER ,SEQUENCE asc declare @EmployeeID as nvarchar(max); declare @SequenceNum as nvarchar(max); declare @LastEEID as nvarchar(max); declare @action as nvarchar(max); declare @currentEmpRecord int declare @counter int; open curr fetch next from curr into @EmployeeID, @SequenceNum, @action; while @@FETCH_STATUS=0 begin if @ LastEEID=@EmployeeID and @action='1-HR' begin set @sql = concat('update [DB].[Transactional History] set EMPRECORD=',+ @currentEmpRecord, '+1 where WORKER=', @EmployeeID, ' and SEQUENCE=', @SequenceNum) EXECUTE sp_executesql @sql set @ counter=@counter +1; set @ LastEEID=@EmployeeID ; set @ currentEmpRecord=@currentEmpRecord +1; end else if @LastEEID is null or @LastEEID<>@EmployeeID begin set @sql = concat('update [DB].[Transactional History] set EMPRECORD=1 where WORKER=', @EmployeeID, ' and SEQUENCE=', @SequenceNum) EXECUTE sp_executesql @sql set @ counter=@counter +1; set @ LastEEID=@EmployeeID ; set @currentEmpRecord=1 end else begin set @sql = concat('update [DB].[Transactional History] set EMPRECORD=', @currentEmpRecord, ' where WORKER=', @EmployeeID, ' and SEQUENCE=', @SequenceNum) EXECUTE sp_executesql @sql set @ counter=@counter +1; end fetch next from curr into @EmployeeID, @SequenceNum, @action; end close curr; deallocate curr; 

Below is the code to create a sample table. I want to increase EMPRECORD every time the record is "1-HR", but reset for each new WORKER. Before this code is executed, EMPRECORD is NULL for all records. This table shows the target result.

 CREATE TABLE [DB].[Transactional History-test]( [WORKER] [nvarchar](255) NULL, [SOURCE] [nvarchar](50) NULL, [TAB] [nvarchar](25) NULL, [EFFECTIVE_DATE] [date] NULL, [ACTION] [nvarchar](5) NULL, [SEQUENCE] [numeric](26, 0) NULL, [EMPRECORD] [numeric](26, 0) NULL, [MANAGER] [nvarchar](255) NULL, [PAYRATE] [nvarchar](20) NULL, [SALARY_PLAN] [nvarchar](1) NULL, [HOURLY_PLAN] [nvarchar](1) NULL, [LAST_MANAGER] [nvarchar](255) NULL ) ON [PRIMARY] GO INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'1', NULL, N'EMP-Position Mgt', CAST(N'2004-01-01' AS Date), N'1-HR', CAST(1 AS Numeric(26, 0)), CAST(1 AS Numeric(26, 0)), N'3', N'Hourly', NULL, NULL, NULL) GO INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'1', NULL, N'Change Job', CAST(N'2004-05-01' AS Date), N'5-JC', CAST(2 AS Numeric(26, 0)), CAST(1 AS Numeric(26, 0)), N'4', NULL, NULL, NULL, N'3') GO INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'1', NULL, N'EMP-Terminations', CAST(N'2005-01-01' AS Date), N'6-TR', CAST(3 AS Numeric(26, 0)), CAST(1 AS Numeric(26, 0)), N'4', NULL, NULL, NULL, N'4') GO INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'1', NULL, N'Change Job', CAST(N'2010-05-01' AS Date), N'5-JC', CAST(4 AS Numeric(26, 0)), CAST(1 AS Numeric(26, 0)), N'3', NULL, NULL, NULL, N'4') GO INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'1', NULL, N'EMP-Position Mgt', CAST(N'2011-05-01' AS Date), N'1-HR', CAST(5 AS Numeric(26, 0)), CAST(2 AS Numeric(26, 0)), N'3', N'Hourly', NULL, NULL, NULL) GO INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'1', NULL, N'CWR-Position Mgt', CAST(N'2012-01-01' AS Date), N'1-HR', CAST(6 AS Numeric(26, 0)), CAST(3 AS Numeric(26, 0)), NULL, NULL, NULL, NULL, NULL) GO INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'1', NULL, N'Organizations', CAST(N'2015-01-01' AS Date), N'3-ORG', CAST(7 AS Numeric(26, 0)), CAST(3 AS Numeric(26, 0)), NULL, NULL, NULL, NULL, NULL) GO INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'1', NULL, N'Organizations', CAST(N'2015-01-01' AS Date), N'3-ORG', CAST(8 AS Numeric(26, 0)), CAST(3 AS Numeric(26, 0)), NULL, NULL, NULL, NULL, NULL) GO INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'2', NULL, N'EMP-Terminations', CAST(N'2001-01-01' AS Date), N'6-TR', CAST(9 AS Numeric(26, 0)), CAST(1 AS Numeric(26, 0)), NULL, NULL, NULL, NULL, NULL) GO INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'2', NULL, N'EMP-Terminations', CAST(N'2001-05-01' AS Date), N'6-TR', CAST(10 AS Numeric(26, 0)), CAST(1 AS Numeric(26, 0)), NULL, NULL, NULL, NULL, NULL) GO INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'2', NULL, N'Change Job', CAST(N'2004-01-01' AS Date), N'5-JC', CAST(11 AS Numeric(26, 0)), CAST(1 AS Numeric(26, 0)), N'3', NULL, NULL, NULL, NULL) GO INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'2', NULL, N'Change Job', CAST(N'2004-01-01' AS Date), N'5-JC', CAST(12 AS Numeric(26, 0)), CAST(1 AS Numeric(26, 0)), N'3', NULL, NULL, NULL, N'3') GO INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'2', NULL, N'EMP-Position Mgt', CAST(N'2014-01-01' AS Date), N'1-HR', CAST(13 AS Numeric(26, 0)), CAST(2 AS Numeric(26, 0)), N'4', N'Salary', NULL, NULL, NULL) GO INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'3', NULL, N'EMP-Terminations', CAST(N'2012-01-01' AS Date), N'6-TR', CAST(14 AS Numeric(26, 0)), CAST(1 AS Numeric(26, 0)), NULL, NULL, NULL, NULL, NULL) GO INSERT [DB].[Transactional History-test] ([WORKER], [SOURCE], [TAB], [EFFECTIVE_DATE], [ACTION], [SEQUENCE], [EMPRECORD], [MANAGER], [PAYRATE], [SALARY_PLAN], [HOURLY_PLAN], [LAST_MANAGER]) VALUES (N'4', NULL, N'EMP-Position Mgt', CAST(N'2012-01-01' AS Date), N'1-HR', CAST(15 AS Numeric(26, 0)), CAST(1 AS Numeric(26, 0)), NULL, NULL, NULL, NULL, NULL) GO select * from DB.[Transactional History-test] 

Answer 1

This should better reproduce cursor logic.

 WITH T AS (SELECT *, IIF(FIRST_VALUE([ACTION]) OVER (PARTITION BY WORKER ORDER BY [SEQUENCE] ROWS UNBOUNDED PRECEDING) = '1-HR', 0, 1) + COUNT(CASE WHEN [ACTION] = '1-HR' THEN 1 END) OVER (PARTITION BY WORKER ORDER BY [SEQUENCE] ROWS UNBOUNDED PRECEDING) AS _EMPRECORD FROM DB.[Transactional History-test]) UPDATE T SET EMPRECORD = _EMPRECORD; 

Answer 2

I think you need a windows function with a case argument. It is simpler and should perform much better than your cursor, especially if you have good indexes.

 WITH CTE AS ( SELECT *, CASE WHEN [action] = '1-HR' OR [Sequence] = MIN([sequence]) OVER (PARTITION BY worker) THEN 1 --cnter increases by 1 whether the action is 1-HR OR the sequence is the first for that worker ELSE 0 END cnter FROM [Transactional History-test] ) SELECT empRecord, --can add any columns you want here SUM(cnter) OVER (PARTITION BY worker ORDER BY [SEQUENCE]) AS new_EMPRECORD --just a cumalative sum of cnter per worker FROM CTE 

Results (mine match yours):

 empRecord new_EMPRECORD --------------------------------------- ------------- 1 1 1 1 1 1 1 1 2 2 3 3 3 3 3 3 1 1 1 1 1 1 1 1 2 2 1 1 1 1 

Answer 3

You did not specify which version of TSQL - this solution applies to SQL 2008 in advance.

Based on your cursor, my guess in the query would look like this:

 WITH worker1hr as (select WORKER, SEQUENCE FROM [DBO].[Transactional History] WHERE Action = '1-HR') , workerStart as (select WORKER, min(SEQUENCE) as StartSeq FROM [DBO].[Transactional History] group by worker) SELECT th.WORKER, SEQUENCE, ACTION , EMPRECORD , 1 + (select count(*) from worker1hr wh WHERE wh.WORKER = th.WORKER and wh.SEQUENCE <= th.SEQUENCE AND WH.SEQUENCE > ws.StartSeq ) as QryEmpRecord FROM [DBO].[Transactional History] th JOIN workerStart ws ON ws.WORKER = th.WORKER ORDER BY WORKER ,SEQUENCE 

NOTE. The TSQL Query WITH clause requires the semicolon to continue. Strange, but true ....

Output:

Answer 4

To implement a "pseudo-cursor" using only pure SQL, you can create a self-join (for example, a left outer join) in the same table, and for each row in the left table, specify the corresponding operations that will be performed on the right table. Hope this helps. Best wishes,