@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 
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!