Customer needs to create days in the past for new starters on TimeLOG Web / Synergy Workforce

If you need to generate days for dates in the past because a new starter was added to the system AFTER their start date edit and run the SQL script planning.

use these declares to limit the employees this applies to, the script as written does ALL employees

single employee selection via ID

declare @employeesToProcess nvarchar(max) = 'select Id from employees where Id = {ID OF EMPLOYEE FOR SCHEDULE}' example declare @employeesToProcess nvarchar(max) = 'select Id from employees where Id = 11'

multiple employee selection via Id

declare @employeesToProcess nvarchar(max) = 'select Id from employees where Id in ({EMPLOYEE IDS COMMA SEPARATED})' example declare @employeesToProcess nvarchar(max) = 'select Id from employees where Id in (13,16)'

single employee selection via Code (EMployee Number)

declare @employeesToProcess nvarchar(max) = 'select Id from employees where Code = ''{EMPLOYEE CODE}''' example declare @employeesToProcess nvarchar(max) = 'select Id from employees where Code = ''15'''

multiple employee selection via Code (Employee Number)

declare @employeesToProcess nvarchar(max) = 'select Id from employees where Id in ({ EMPLOYEE CODES COMMA SEPARATED AND PUT BETWEEN '' })' example declare @employeesToProcess nvarchar(max) = 'select Id from employees where Code in (''10'',''11'')'

REMEMBER to CHANGE THE DATE!!!!!!!!!!

----set start date in here ----if set to null, will take system values (startdate/system options) declare @startPlanningFromThisDate date = '20170101' ----set sql to get employees to process ----if set to null - applies to everyone declare @employeesToProcess nvarchar(max) = 'select Id from employees' ----do not change after here CREATE TABLE #t ( id int ) if (@employeesToProcess is null OR @employeesToProcess = '') begin INSERT into #t (id) (select id from employees) end else begin INSERT #t EXECUTE sp_executesql @employeesToProcess end ----any other stateements here if needed ----do not change after here declare @currentDay date declare @employeeId int declare @employeeName nvarchar(250) declare @currentWeeklyDetail int declare @currentDailyModel int declare @currentMorningAbsence int declare @currentAfternoonAbsence int declare @currentDailyModelName varchar(100) declare @fromDate Date declare @endDate Date set @endDate = (select dateadd(day, NumberOfDaysInAdvancedCalendar, getdate()) from dbo.Calculations) declare @swipesStartDate Date set @swipesStartDate = (select NewEmployeeSwipesStartDate from softwaremainoptions) declare @dm1 int declare @dm2 int declare @dm3 int declare @dm4 int declare @dm5 int declare @dm6 int declare @dm7 int declare emp_cur CURSOR For SELECT e.id, e.firstname + ' ' + e.lastname, e.EnterDate FROM employees e where e.id in (select id from #t) for read only open emp_cur while(0=0) begin fetch next from emp_cur into @employeeId, @employeeName, @fromDate if (@@fetch_status <> 0) break print 'Processing employee: ' + @employeeName --get daily models select @dm7 = wsd.Id from dbo.Link_Employee_WeeklyShift ews join weeklyshifts ws on ews.WeeklyShiftId = ws.Id join weeklyshiftdetails wsd on wsd.WeeklyShiftId = ws.Id where employeeid = @employeeId and DayOfWeek = 0 select @dm1 = wsd.Id from dbo.Link_Employee_WeeklyShift ews join weeklyshifts ws on ews.WeeklyShiftId = ws.Id join weeklyshiftdetails wsd on wsd.WeeklyShiftId = ws.Id where employeeid = @employeeId and DayOfWeek = 1 select @dm2 = wsd.Id from dbo.Link_Employee_WeeklyShift ews join weeklyshifts ws on ews.WeeklyShiftId = ws.Id join weeklyshiftdetails wsd on wsd.WeeklyShiftId = ws.Id where employeeid = @employeeId and DayOfWeek = 2 select @dm3 = wsd.Id from dbo.Link_Employee_WeeklyShift ews join weeklyshifts ws on ews.WeeklyShiftId = ws.Id join weeklyshiftdetails wsd on wsd.WeeklyShiftId = ws.Id where employeeid = @employeeId and DayOfWeek = 3 select @dm4 = wsd.Id from dbo.Link_Employee_WeeklyShift ews join weeklyshifts ws on ews.WeeklyShiftId = ws.Id join weeklyshiftdetails wsd on wsd.WeeklyShiftId = ws.Id where employeeid = @employeeId and DayOfWeek = 4 select @dm5 = wsd.Id from dbo.Link_Employee_WeeklyShift ews join weeklyshifts ws on ews.WeeklyShiftId = ws.Id join weeklyshiftdetails wsd on wsd.WeeklyShiftId = ws.Id where employeeid = @employeeId and DayOfWeek = 5 select @dm6 = wsd.Id from dbo.Link_Employee_WeeklyShift ews join weeklyshifts ws on ews.WeeklyShiftId = ws.Id join weeklyshiftdetails wsd on wsd.WeeklyShiftId = ws.Id where employeeid = @employeeId and DayOfWeek = 6 if (@swipesStartDate > @fromDate) begin set @fromDate = @swipesStartDate end if (@startPlanningFromThisDate is not null) begin set @fromDate = @startPlanningFromThisDate end set @currentDay = @fromDate while @currentDay <= @endDate begin --select proper daily model if datepart(weekday, '2013-04-01') = datepart(weekday, @currentDay) begin set @currentWeeklyDetail = @dm1 end if datepart(weekday, '2013-04-02') = datepart(weekday, @currentDay) begin set @currentWeeklyDetail = @dm2 end if datepart(weekday, '2013-04-03') = datepart(weekday, @currentDay) begin set @currentWeeklyDetail = @dm3 end if datepart(weekday, '2013-04-04') = datepart(weekday, @currentDay) begin set @currentWeeklyDetail = @dm4 end if datepart(weekday, '2013-04-05') = datepart(weekday, @currentDay) begin set @currentWeeklyDetail = @dm5 end if datepart(weekday, '2013-04-06') = datepart(weekday, @currentDay) begin set @currentWeeklyDetail = @dm6 end if datepart(weekday, '2013-04-07') = datepart(weekday, @currentDay) begin set @currentWeeklyDetail = @dm7 end select @currentDailyModel = DailyModelId, @currentMorningAbsence = FirstAbsenceId, @currentAfternoonAbsence = SecondAbsenceId from weeklyshiftdetails where Id = @currentWeeklyDetail --check if we have a clocking for today if ((select count(*) from clockings where date = @currentDay and employeeid = @employeeId) = 0) begin print 'adding missing swipe: ' + convert(varchar, @currentDay) INSERT INTO [Clockings] ([EmployeeId] ,[DailyModelID] ,[Date] ,[BadgeTime1GeneratedBy] ,[BadgeTime2GeneratedBy] ,[BadgeTime3GeneratedBy] ,[BadgeTime4GeneratedBy] ,[DelayQuantity] ,[DelayDuration] ,[CartNumber] ,[DurationTheoretic] ,[IsDTManualChanged] ,[BreakDurationMin] ,[BadgeTime5GeneratedBy] ,[BadgeTime6GeneratedBy] ,[BadgeTime7GeneratedBy] ,[BadgeTime8GeneratedBy] ,[BadgeTime9GeneratedBy] ,[BadgeTime10GeneratedBy] ,[BadgeTime11GeneratedBy] ,[BadgeTime12GeneratedBy]) VALUES (@employeeId ,@currentDailyModel ,@currentDay ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,'00:00:00' ,0 ,'00:00:00' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0) end --apply planning set @currentDailyModelName = (select name from dailymodels where id = @currentDailyModel) print convert(varchar, @currentDay) + ' - ' + @currentDailyModelName update dbo.Clockings set DailyModelID = @currentDailyModel, MorningAbsenceID = @currentMorningAbsence, AfternoonAbsenceID = @currentAfternoonAbsence, BreakDurationMin = (select d.BreakDurationMin from DailyModels d where d.Id = @currentDailyModel), DurationTheoretic = (select d.DurationTheoretic from DailyModels d where d.Id = @currentDailyModel), Enter1Start = (select d.Enter1Start from DailyModels d where d.Id = @currentDailyModel), Enter1End = (select d.Enter1End from DailyModels d where d.Id = @currentDailyModel), Exit1Start = (select d.Exit1Start from DailyModels d where d.Id = @currentDailyModel), Exit1End = (select d.Exit1End from DailyModels d where d.Id = @currentDailyModel), Enter2Start = (select d.Enter2Start from DailyModels d where d.Id = @currentDailyModel), Enter2End = (select d.Enter2End from DailyModels d where d.Id = @currentDailyModel), Exit2Start = (select d.Exit2Start from DailyModels d where d.Id = @currentDailyModel), Exit2End = (select d.Exit2End from DailyModels d where d.Id = @currentDailyModel), WeeklyModelDetailsId = @currentWeeklyDetail where EmployeeId = @employeeId AND Date = @currentDay set @currentDay = dateadd(day, 1, @currentDay) end print 'Processing done' print '' print '' print '' end CLOSE emp_cur DEALLOCATE emp_cur drop table #t