Updating a table based on a date field without using loops with sql

Question

I have the following script that I created to update the schedule table in Microsoft SQL Server. The primary key field is the date and individual dates in the calendar. Workers are planned for 7 consecutive days, and I need to alternate 2 employees for seven days, from every Monday to Sunday until the end of the year. I have created the following script using loops, and I am looking for a way to execute it without using loops. Thanks!

DECLARE @z_start INT DECLARE @z_date DATETIME SET @z_date = '03/07/16' WHILE(@z_date < '12/31/16') BEGIN SET @z_start = 0 WHILE(@z_start < 7) BEGIN UPDATE EmployeeOnCall SET employeeName = 'Jones, Jim' WHERE deptID = 25 AND weekDay = @z_date SET @z_date = DATEADD(dd, 1, @z_date) SET @z_start = @z_start + 1 END SET @z_start = 0 WHILE(@z_start < 7) BEGIN UPDATE EmployeeOnCall SET employeeName = 'Penton, Andy' WHERE deptID = 25 AND weekDay = @z_date SET @z_date = DATEADD(dd, 1, @z_date) SET @z_start = @z_start + 1 END END; 

Answer 1

You can do this using DATEPART and CTE. Here's the relevant part:

 ;with wkNum as ( SELECT weekDay, EmployeeName, deptID, (DATEPART(wk, weekDay) / 2) % 2 as period FROM EmployeeOnCall ) UPDATE wkNum SET employeename = CASE WHEN wkNum.period = 0 THEN 'Jones, Jim' ELSE 'Penton, Andy' END 

The idea is to determine what week of the year is, divide it by 2, and then modulo by the number of employees you deal with. And here is the test script:

 DECLARE @test TABLE (weekDay datetime, employeename varchar(50), deptID int); DEClARE @startdate datetime = '2016-02-16'; declare @enddate datetime = '2016-12-31'; ;with N0 as (SELECT 1 as n UNION ALL SELECT 1) ,N1 as (SELECT 1 as n FROM N0 t1, N0 t2) ,N2 as (SELECT 1 as n FROM N1 t1, N1 t2) ,N3 as (SELECT 1 as n FROM N2 t1, N2 t2) ,N4 as (SELECT 1 as n FROM N3 t1, N3 t2) ,N5 as (SELECT 1 as n FROM N4 t1, N4 t2) ,N6 as (SELECT 1 as n FROM N5 t1, N5 t2) ,nums as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as num FROM N6) INSERT @test (weekDay, deptID) SELECT DATEADD(day,num-1,@startdate) as thedate, 25 FROM nums WHERE num <= DATEDIFF(day,@startdate,@enddate) + 1 ;with wkNum as ( SELECT weekDay, EmployeeName, deptID, (DATEPART(wk, weekDay) / 2) % 2 as period FROM @test ) UPDATE wkNum SET employeename = CASE WHEN wkNum.period = 0 THEN 'Jones, Jim' ELSE 'Penton, Andy' END SELECT * FROM @test 

If you have a staff table to pull from it, it can be expanded as follows:

 DECLARE @emps TABLE (Name varchar(100)); INSERT @emps VALUES ('Dan'), ('Joe'), ('Asdf') UPDATE eoc SET employeename = emp.Name FROM EmployeeOnCall eoc INNER JOIN (SELECT ROW_NUMBER() OVER(ORDER BY Name) rn, Name FROM @emps) emp ON ((DATEPART(wk, weekDay) / 2) % (SELECT COUNT(*) FROM @emps)) + 1 = emp.rn 

Answer 2

The best approach is to populate the saved Calendar table. Fill it with a list of days that covers the beginning of 10 years, mark each day as a working day or a day off, add any columns that you need. Just join it, filter by the extreme dates that you received.

In your case, it is not shown why you indicated the 7-day intervals. 7 days means every day. Say you have @startdate and @enddata . Let me create a list of dates and connect it to the destination table.

 declare @startdate datetime = '20150612', @enddate datetime = '20160122', @period_len int set @period_len = datediff(dd, @startdate, @enddate) ;with cteCounter as ( select top (@period_len) ROW_NUMBER() OVER(ORDER BY c.object_id) as counter from sys.columns c cross join sys.columns cc order by counter ), cteCalendar as ( select c.counter, dateadd(dd, c.counter-1, @startdate) as calendar_date from cteCalendar c order by calendar_date ) UPDATE e SET employeeName = 'Jones, Jim' FROM EmployeeOnCall e inner join cteCalendar c on c.calendar_date = e.weekDay WHERE e.deptID = 25