begin
-- loop to insert date backward
while
@end_date>=@start_Date
begin
INSERT INTO @tb_temp

declare @start datetime, @counter int, @end int
set @start = '1/1/2007'
set @end = datediff(dd,@start,getdate())
set @counter  = 0

declare @tbl table(
	Dt SMALLDATETIME
)

while @counter <= @end
	begin
		INSERT INTO @tbl (Dt)
		Values(CONVERT(VARCHAR,dateadd(dd,-1 * @counter, GETDATE()),101))

		set @counter = @counter + 1
	end

select * 
from @tbl

while

@@Fetch_Status = 0

begin

-- loop to insert info of emp shifts

while

@current<=@EndDate  ----------------generate date from END to START ?

begin

INSERT INTO @empShifts ([empID],[ShiftDate],[ShiftType],[StartDate] ,[EndDate])

select @input_empID ,@current,shift .patternShiftValue ,@StartDate,@EndDate

from @shifts_pattern as shift where PatternId=@last_shift_id+1

INSERT INTO @empShifts ([empID],[ShiftDate],[ShiftType],[StartDate] ,[EndDate])
select @input_empID ,
         @current,
         shift.patternShiftValue ,
         @StartDate,
         dateadd(day,-1*spt.number,@EndDate)
from @shifts_pattern as shift,
        master..spt_values spt
where 
       PatternId=@last_shift_id+1 
       and spt.type='P'
       and dateadd(day,-1*spt.number,@EndDate) between @current and @enddate

INSERT INTO @empShifts ([empID],[ShiftDate],[ShiftType],[StartDate] ,[EndDate])
select @input_empID ,
         @current,
         shift.patternShiftValue ,
         @StartDate,
         dateadd(day,-1*spt.number,@EndDate)
from @shifts_pattern as shift,
        master..spt_values spt
where 
       PatternId=@last_shift_id+1 
       and spt.type='P'
       and dateadd(day,-1*spt.number,@EndDate) between @current and @enddate

DECLARE

@shifts_pattern TABLE ([PatternId] [int] IDENTITY(1,1 ) NOT NULL, [patternShiftValue] [int]NOT NULL)

declare

@I int

set

@i=0

while

@i < 5

BEGIN

INSERT INTO @shifts_pattern ([patternShiftValue] )

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL 

SELECT 4 UNION ALL

SELECT 5 UNION ALL

SELECT 6 UNION ALL

SELECT 7 UNION ALL

SELECT 8

set

@i=@i+1

end

declare

@empList

TABLE

( [empID] [numeric](18, 0) NOT NULL,[ShiftType] [int]NULL,[StartDate][datetime]NOT NULL,[EndDate] [datetime] NOT NULL) 

INSERT INTO

@empList ([empID], [ShiftType],[StartDate],[EndDate])

SELECT 111111,1,CONVERT(DATETIME, '01/01/2008', 103), CONVERT(DATETIME, '17/01/2008', 103)

-- create shifts table

declare

@empShifts

TABLE ( [empID] [numeric](18, 0) NOT NULL,[ShiftDate] [datetime]NOT NULL,[ShiftType] [int]NULL ,[StartDate] [datetime]NOT NULL,[EndDate] [datetime]NOT NULL) 

DECLARE

@StartDate datetime

DECLARE

@EndDate datetime

Declare

@current datetime

DEclare

@last_shift_id int

Declare

@input_empID int

----------------- open list table for emp with curser

DECLARE

List_of_emp CURSOR FOR 

SELECT

emp.empId,emp.ShiftType,emp.StartDate,emp.EndDate FROM @empList emp

OPEN

List_of_emp

FETCH

List_of_emp INTO @input_empID , @last_shift_id ,@StartDate,@EndDate

SET @current = @StartDate

-----------------

-- loop on all emp in the list

while

@@Fetch_Status = 0

begin

-- loop to insert info of emp shifts

while

@current<=@EndDate 

begin

INSERT INTO @empShifts ([empID],[ShiftDate],[ShiftType],[StartDate] ,[EndDate])

select @input_empID ,@current,shift .patternShiftValue ,@StartDate,@EndDate

from @shifts_pattern as shift where PatternId=@last_shift_id+1 

-- if it is Friday and we are on one of the first shift we don't move to next shift type .

if (DATENAME(dw ,@current) = 'Friday' ) and

EXISTS(select ShiftType from @empShifts where ShiftDate=@current and empID= @input_empID and ShiftType in ( 1,2,3)) 

-- do nothing

--set @last_shift_id=@last_shift_id

print ('friday first shift')

ELSE

set @last_shift_id=@last_shift_id+ 1

set @current=DATEADD( d,1, @current)

end

FETCH

List_of_emp INTO @input_empID ,@last_shift_id,@StartDate,@EndDate

-- init of start date for the next emp

set

@current = @StartDate

end

CLOSE

List_of_emp

DEALLOCATE 

List_of_emp

select

empID,shiftDate,DATENAME (dw,shift.ShiftDate ), shiftType from @empShifts as shift

RETURN

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])
SELECT	111111,
		1,
		CONVERT(DATETIME, '01/01/2008', 103), 
		CONVERT(DATETIME, '27/02/2009', 103)
UNION ALL
SELECT	222222,
		2,
		CONVERT(DATETIME, '01/01/2008', 103), 
		CONVERT(DATETIME, '27/02/2009', 103)
UNION ALL
SELECT	333333,
		3,
		CONVERT(DATETIME, '01/01/2008', 103), 
		CONVERT(DATETIME, '27/02/2009', 103)
UNION ALL
SELECT	444444,
		4,
		CONVERT(DATETIME, '01/01/2008', 103), 
		CONVERT(DATETIME, '27/02/2009', 103)
UNION ALL
SELECT	555555,
		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())