⏱ SQL Cursor Logic to Auto-Assign Employee Shifts (with Early Punch & OT Handling)

💡 Overview

In real-world manufacturing or plant attendance systems, employees often punch in early or work across multiple shifts (for overtime).
This SQL Server script automatically maps swipe entries to the correct shift (A/B/C) by analyzing punch times — even if they occur hours before or after their scheduled shift.

It does this using a multi-stage validation:

First determines a probable shift.
Then rechecks against Pay_tra_shiftnote.
If no match found, it cascades to earlier shifts — handling early arrivals and OT overlaps.

🧩 Shift Pattern Reference

Shift	Time Range	Punch Consideration Window
A	08:00 – 16:30	Checks from 05:00 – 13:30
B	16:30 – 02:00	Checks from 13:30 – 23:00
C	00:00 – 08:00	Checks from 23:00 – 05:00

⏳ The script intentionally checks 3 hours earlier than actual shift start.
If no matching shift entry exists in Pay_tra_shiftnote, it backtracks to the previous shift, then one more shift back in the second check — ensuring even edge cases are covered.

⚙️ How It Works

Fetch unassigned swipes from Pay_tra_swipedata.
Calculate punch time (@Ptime) and tentatively assign shift (A/B/C).
First IF block: Checks if this shift exists for the employee/date. If not → tries previous shift.
Second IF block: Performs one more backtrack to catch rare overlaps (OT or double-shift).
Update both tables (Pay_tra_shiftnote, Pay_tra_swipedata) with correct shift/date.

💻 Full SQL Script (Final Production Version)

set @sqlstatement = '
Declare users_cursor CURSOR FOR 
SELECT Emp_no, PDATE 
FROM Pay_tra_swipedata 
WHERE isnull(Shift_date,'''') = '''' 
ORDER BY Emp_no, PDate'

exec sp_executesql @sqlstatement

OPEN users_cursor
FETCH NEXT FROM users_cursor INTO @Emp_no, @PDATE

WHILE @@FETCH_STATUS = 0
BEGIN
    if @old_user = @Emp_no
        set @pdate_index = @pdate_index % 6 + 1
    else
    begin
        set @pdate_index = 1
        set @old_user = @Emp_no
    end

    declare @sn_shiftcode nvarchar(1)='', @sn_shift_date nvarchar(10)=''
    declare @Ptime nvarchar(5) = cast(cast(@PDATE as time(7)) as varchar(5))
    declare @SDate nvarchar(10), @SCode nvarchar(10)

    -- 🔹 Primary shift window detection
    if (@Ptime > '05:00' and @Ptime < '13:30')
    begin
        set @SDate = convert(varchar(10), @PDATE, 120)
        set @SCode = 'A'
    end
    else if (@Ptime > '13:30' and @Ptime < '23:00')
    begin
        set @SDate = convert(varchar(10), @PDATE, 120)
        set @SCode = 'B'
    end
    else if (@Ptime > '23:00' and @Ptime < '23:59')
    begin
        set @SDate = convert(varchar(10), @PDATE, 120)
        set @SCode = 'C'
    end
    else if (@Ptime > '00:00' and @Ptime < '05:00')
    begin
        set @SDate = convert(varchar(10), dateadd(dd, -1, @PDATE), 120)
        set @SCode = 'C'
    end

    -- 🔹 First validation: reassign if shift not found
    select @sn_shiftcode = Shift_code, @sn_shift_date = Shift_Dt 
    from Pay_tra_shiftnote 
    where Emp_no = @Emp_no and Shift_Dt = @SDate and Shift_code = @SCode

    if (@sn_shiftcode = '')
    begin
        if @SCode = 'C'
        begin
            set @SCode = 'B'
            set @SDate = convert(varchar(10), @SDate, 120)
        end
        else if @SCode = 'B'
        begin
            set @SCode = 'A'
            set @SDate = convert(varchar(10), dateadd(dd, 0, @SDate), 120)
        end
        else if @SCode = 'A'
        begin
            set @SCode = 'C'
            set @SDate = convert(varchar(10), dateadd(dd, -1, @SDate), 120)
        end
    end

    -- 🔹 Second validation: deeper backtrack for OT / dual-shift
    select @sn_shiftcode = Shift_code, @sn_shift_date = Shift_Dt 
    from Pay_tra_shiftnote 
    where Emp_no = @Emp_no and Shift_Dt = @SDate and Shift_code = @SCode

    if (@sn_shiftcode = '')
    begin
        if @SCode = 'B'
        begin
            set @SCode = 'A'
            set @SDate = convert(varchar(10), dateadd(dd, 0, @SDate), 120)
        end
        else if @SCode = 'A'
        begin
            set @SCode = 'C'
            set @SDate = convert(varchar(10), dateadd(dd, -1, @SDate), 120)
        end
        else if @SCode = 'C'
        begin
            set @SCode = 'B'
            set @SDate = convert(varchar(10), dateadd(dd, 0, @SDate), 120)
        end
    end

    -- 🔹 Update shift and swipe tables
    declare @QRY nvarchar(max)
    set @QRY = 'update Pay_tra_shiftnote 
                set PDate_count = ''' + cast(@pdate_index as varchar(10)) + ''',
                    PDate' + cast(@pdate_index as varchar(10)) + ' = ''' + convert(varchar(19), @PDATE, 120) + ''' 
                where Comp_Code = ''YJAT'' and Emp_no = ''' + @Emp_no + ''' and Shift_dt = ''' + @SDate + ''''
    exec sp_executesql @QRY

    set @QRY = 'update Pay_tra_swipedata 
                set Shift_date = ''' + @SDate + ''', Shift_Code = ''' + @SCode + ''' 
                where PDate = ''' + convert(varchar(19), @PDATE, 120) + ''' 
                and isnull(Shift_Date,'''') = ''''' 
    exec sp_executesql @QRY

    FETCH NEXT FROM users_cursor INTO @Emp_no, @PDATE
END

CLOSE users_cursor
DEALLOCATE users_cursor

🧮 Why Two “IF” Checks?

This dual-check pattern ensures:

First IF: Captures early or slightly misaligned punches.
Second IF: Backtracks one more shift if employee clocked extra hours or performed OT.

This makes your logic robust against shift overlaps and day boundary crossings — something real swipe systems struggle with.

🏁 Summary

✅ Handles early punches (3 hours before shift)
✅ Correctly aligns OT and multi-shift scenarios
✅ Automatically updates both swipe and shift master tables
✅ Fully dynamic and SQL-only — no manual mapping needed

You can experiment with this shift assignment logic live on SQL Fiddle:
👉 Run the SQL Script Here