Loop in SQL Server without a Cursor

Question

Below is some SQL Server code I have been working on. I know now that using a cursor is a bad idea in general, but I cannot figure out how else I can make this work. The performance is terrible with the cursor. I'm really just using some simple IF statement logic with a loop, but can't translate it to SQL. I'm 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 keep this @counter going without a cursor. I feel like the solution is simple, but I've lost myself. Thanks for looking.

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 code to build a sample table. I want to increase EMPRECORD every time a record is '1-HR', but reset it for each new WORKER. Before this code is executed, EMPRECORD is null for all records. This table shows the target output.

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 reproduce the logic of the cursor in a more efficient way

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 what you need is a Windows function with a case statement. This is simpler and should perform significantly 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 matches 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 indicate what version of TSQL - this solution applies to SQL 2008 forward.

Based on your cursor, my guess at the query would be:

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 a semicolon proceed it. Strange, but true....

The output:

Answer 4

To implement the "pseudo-cursor" using just pure SQL, you can create a self-join (e.g. left outer join) to the same table and for each row in the left table specify the corresponding operations to be performed on the right table. Hope this may help. Best regards,