Try this

if object_ID('tempdb..#emplist','U')<>0

Drop Table #emplist

if object_ID('tempdb..#empshifts','U')<>0

Drop Table #empshifts

go

declare @g datetime

select @g=getdate()

CREATE table #empList (

[empID] int NOT NULL,

[ShiftType] int NULL,

[StartDate] datetime NOT NULL,

[EndDate] datetime NOT NULL

)

INSERT INTO #empList ([empID], [ShiftType],[StartDate],[EndDate])

SELECT111111,

1,

CONVERT(DATETIME, '01/01/2008', 103),

CONVERT(DATETIME, '27/02/2009', 103)

UNION ALL

SELECT222222,

2,

CONVERT(DATETIME, '01/01/2008', 103),

CONVERT(DATETIME, '27/02/2009', 103)

UNION ALL

SELECT333333,

3,

CONVERT(DATETIME, '01/01/2008', 103),

CONVERT(DATETIME, '27/02/2009', 103)

UNION ALL

SELECT444444,

4,

CONVERT(DATETIME, '01/01/2008', 103),

CONVERT(DATETIME, '27/02/2009', 103)

UNION ALL

SELECT555555,

5,

CONVERT(DATETIME, '01/01/2008', 103),

CONVERT(DATETIME, '27/02/2009', 103)

-- create shifts table

CREATE table #empShifts (

[empID] numeric(18, 0) NOT NULL,

[ShiftDate] datetime NOT NULL,

[ShiftType] int NULL ,

[startingShiftType] int not null

)

create unique clustered index uc_empshifts on #empshifts(empid,shiftdate)

declare @curr_employee int

declare @shift_id int

declare @dummyShift int

declare @dummyEmp int

--start by populating the dates into the @empshifts table

insert #empshifts(

empid,

shiftdate,

[startingShiftType]

)

select

empid,

dateadd(day,spt.number,startdate),

shifttype

from#empList cross join

master..spt_values spt

where

spt.type='P'

and spt.number<=datediff(day, startdate,enddate)

--now set up the shifts as the cursor solution did

select @shift_id=0, @curr_employee=0

update e

set

@shift_ID=shiftType=(case when @curr_employee=empid then @shift_ID else startingShiftType end -1 +

CASE WHEN @shift_id in ( 1,2,3) and DATENAME (dw,ShiftDate )='Friday' then 0 else 1 end)%8+1,

@dummyshift=@shift_ID,

@curr_employee =empid,

@dummyemp=@curr_employee

from #empshifts e WITH (index(uc_empshifts),TABLOCK) OPTION (MAXDOP 1)

--show the results

select empid,shiftdate, DATENAME (dw,ShiftDate ),shifttype from #empshifts

select datediff(ms,@g,getdate())