SQLTeam.com | Weblogs | Forums

Select employee from table on bi-weekly rotating schedule


#1

I have a group of 7 employees who rotate being in charge of a specific process for two weeks. I'd like to create an agent job that sends an email every Monday with the person who is in charge for that week.

MondayDate User
2017-01-02 Jim
2017-01-09 Jim
2017-01-16 Mike
2017-01-23 Mike
2017-01-30 Susan
2017-02-06 Susan
2017-02-13 Chris
2017-02-20 Chris
2017-02-27 Lee
2017-03-06 Lee
2017-03-13 Mika
2017-03-20 Mika
2017-03-27 Al
2017-04-03 Al
2017-04-10 Schedule resets and back to Jim
2017-04-17 Jim

I have a full calendar table, and a table of ALL employees that I need to restrict down to the 7 people. I don't think I can even put some sample SQL in this question because it's the SQL algorithm to determine who's turn it is that's the issue.

My current solution is to make an Excel sheet and enter all the Monday's in and then paste and fill down the employees and then create a new table in the database and insert the values for the next couple years which seems like a crummy way, but would work.


#2

Initial table setup:

CREATE TABLE dbo.employee_assignments (
    assignment_id smallint NOT NULL DEFAULT 1,
    emp_name varchar(50) NOT NULL,
    is_available bit NOT NULL DEFAULT 1,
    assignment_sequence tinyint NULL,
    assignment_date date NULL
    CONSTRAINT employee_assignments__UQ1 UNIQUE(assignment_id, emp_name, assignment_sequence)
    )
CREATE CLUSTERED INDEX employee_assignments__CL ON dbo.employee_assignments ( assignment_id, assignment_sequence ) WITH ( FILLFACTOR = 99 );
INSERT INTO dbo.employee_assignments ( emp_name, assignment_sequence )
VALUES('Jim', 1),('Mike', 2),('Susan', 3),('Chris', 4),('Lee', 5),('Mika', 6),('Al', 7)

Weekly code:

DECLARE @assignment_id smallint
DECLARE @assignment_order tinyint /*0=random;1=alpha_order;2=use "assignment_sequence" from table;...*/
DECLARE @emp_name varchar(50)
DECLARE @start_date date
DECLARE @end_date date

SET @assignment_id = 1
SET @assignment_order = 2
SET @start_date = DATEADD(DAY, -DATEDIFF(DAY, 0, GETDATE() + 6) % 7, GETDATE() + 6)
SET @end_date = DATEADD(DAY, 6, GETDATE())

IF NOT EXISTS(SELECT 1 FROM dbo.employee_assignments ea WHERE ea.assignment_id = 1 AND ea.is_available = 1 AND ea.assignment_date IS NULL)
BEGIN
    /*All emps have been assigned for the last group of weeks, so reset everyone who's currently available.*/
    UPDATE dbo.employee_assignments
    SET assignment_date = NULL
    WHERE assignment_id = @assignment_id AND
        is_available = 1
END /*IF*/

SELECT TOP (1) @emp_name = ea.emp_name
FROM dbo.employee_assignments ea
WHERE ea.assignment_date IS NULL
ORDER BY CASE @assignment_order
    WHEN 1 THEN emp_name 
    WHEN 2 THEN RIGHT('00' + CAST(ea.assignment_sequence AS varchar(3)), 3)
    ELSE CAST(NEWID() AS varchar(40)) /*random*/ END

UPDATE dbo.employee_assignments
SET assignment_date = @start_date
WHERE emp_name = @emp_name

/*You must add code here to send the email for @emp_name in charge for @start_date thru @end_date.*/

#3

@ScottPletcher Awesome. I replaced the single date field with a begin and end and upped the @end_date to +13 from 6 so on Monday when it runs, it will set the end_date to be the 2nd Sunday to it will have the full 2 week dates populated.

I also added an assignment_week field and set it to 0 on the initial insert and then update it on the next Monday to a 1 so I can change the email body to remind them that it's their turn still. (just flavor text to email notice)

Super helpful, exactly what I needed with a new way of doing it. Thanks for your help :slight_smile:

New table:
CREATE TABLE dbo.employee_assignments (
assignment_id smallint NOT NULL DEFAULT 1,
emp_name varchar(50) NOT NULL,
is_available bit NOT NULL DEFAULT 1,
assignment_sequence tinyint NULL,
assignment_begindate date NULL,
assignment_enddate date NULL,
assignment_week int NULL
CONSTRAINT employee_assignments__UQ1 UNIQUE(assignment_id, emp_name, assignment_sequence)
)
CREATE CLUSTERED INDEX employee_assignments__CL ON dbo.employee_assignments ( assignment_id, assignment_sequence ) WITH ( FILLFACTOR = 99 );

New Proc that an agent job runs every Monday:
CREATE PROCEDURE SendEmployeeReminderEmail
AS
BEGIN

DECLARE @assignment_id smallint
DECLARE @assignment_order tinyint /*0=random;1=alpha_order;2=use "assignment_sequence" from table;...*/
DECLARE @emp_name varchar(50)
DECLARE @start_date date
DECLARE @end_date date
DECLARE @SamePerson int

SET @assignment_id = 1
SET @assignment_order = 2
SET @start_date = DATEADD(DAY, -DATEDIFF(DAY, 0, GETDATE() + 6) % 7, GETDATE() + 6)
SET @end_date = DATEADD(DAY, 13, GETDATE())

IF NOT EXISTS(SELECT 1 FROM dbo.employee_assignments ea WHERE ea.assignment_id = 1 AND ea.is_available = 1 AND ea.assignment_begindate IS NULL)
BEGIN
    /*All emps have been assigned for the last group of weeks, so reset everyone who's currently available.*/
    UPDATE dbo.employee_assignments
    SET assignment_begindate = NULL, assignment_enddate = NULL
    WHERE assignment_id = @assignment_id AND
        is_available = 1
END /*IF*/

SELECT @SamePerson = COUNT(*) FROM (select * from employee_assignments where GETDATE() BETWEEN assignment_begindate and assignment_enddate) mid

IF @SamePerson = 0 --No one within the date range so set new employee
BEGIN

SELECT TOP (1) @emp_name = ea.emp_name
FROM dbo.employee_assignments ea
WHERE ea.assignment_begindate IS NULL
ORDER BY CASE @assignment_order
    WHEN 1 THEN emp_name 
    WHEN 2 THEN RIGHT('00' + CAST(ea.assignment_sequence AS varchar(3)), 3)
    ELSE CAST(NEWID() AS varchar(40)) /*random*/ END


UPDATE dbo.employee_assignments
SET assignment_begindate = @start_date, assignment_enddate = @end_date, assignment_week = 1
WHERE emp_name = @emp_name

END


IF @SamePerson > 0 --Middle of the two week schedule for current employee
BEGIN

SELECT TOP (1) @emp_name = ea.emp_name
FROM dbo.employee_assignments ea
where GETDATE() BETWEEN assignment_begindate and assignment_enddate


UPDATE dbo.employee_assignments
SET assignment_week = 2
WHERE emp_name = @emp_name

END



DECLARE @EmailBody varchar(5000)
SELECT @EmailBody = 'To All,' + '</br></br>' + 'Congratulations to: ' + emp_name + '</br></br>' + 
CASE WHEN assignment_week = 1 
	THEN 'You are in charge of the project this week!'  + '</br>' + 'Good Luck!'
	ELSE 'You are in charge of the project again this week.'  + '</br>' + 'Thanks for doing a great job so far  :)' END
from employee_assignments where GETDATE() BETWEEN assignment_begindate and assignment_enddate



EXEC msdb.dbo.sp_send_dbmail @recipients='Team@company.biz;',
    @subject = 'Project Assignment',
    @body = @EmailBody,
    @body_format = 'HTML',
    @profile_name = 'SQLMailer';


END

Again, great idea, thanks!


#4

You're welcome!

I like the way you adjusted the code to handle bi-weekly notifications, sorry I did only weekly in my first post.

I did notice one probably issue though. I think the "same person" check needs to happen before the check for all-people-have-been-assigned. Otherwise the last week for the last person might get cleared out.

CREATE PROCEDURE ...
...
SET @assignment_id = 1
SET @assignment_order = 2
SET @start_date = DATEADD(DAY, -DATEDIFF(DAY, 0, GETDATE() + 6) % 7, GETDATE() + 6)
SET @end_date = DATEADD(DAY, 13, GETDATE())

/*move the @SamePerson to be first*/
SELECT @SamePerson = COUNT(*) FROM (select * from employee_assignments where GETDATE() BETWEEN assignment_begindate and assignment_enddate) mid

IF @SamePerson = 0 --No one within the date range so set new employee
BEGIN
    /*move the "no one left to assign" to inside the @SamePerson not active code*/
    IF NOT EXISTS(SELECT 1 FROM dbo.employee_assignments ea WHERE ea.assignment_id = 1 AND ea.is_available = 1 AND ea.assignment_begindate IS NULL)
    BEGIN
        /*All emps have been assigned for the last group of weeks, so reset everyone who's currently available.*/
        UPDATE dbo.employee_assignments
        SET assignment_begindate = NULL, assignment_enddate = NULL
        WHERE assignment_id = @assignment_id AND
            is_available = 1
    END /*IF*/
--...rest of proc as above...