Leo Torres
asked on
SQL Recursion schedule
I have a table of dates here are my column names
sample
My second Table OnCallSupport here is a sample
I need 1 person from each team to be on call. How could you join these tables to create a schedule out for a year.
Here is the code to create the Dates view
sample
WeekID StartDate EndDate
1 2017-01-06 2017-01-13
2 2017-01-13 2017-01-20
My second Table OnCallSupport here is a sample
Team SequenceID TechName
Team1 1 Joe
Team1 2 jr
Team1 3 Leo
Team1 4 Julian
Team2 1 Sam
Team2 2 David
Team2 3 Cary
Team2 4 Stepnahie
Team2 5 Nicole
Team3 1 Noel
Team3 2 Candice
Team3 3 Jose
Team3 4 Rafael
Team4 1 Jessica
Team4 2 Alice
Team4 3 Yanet
Team4 4 Richard
I need 1 person from each team to be on call. How could you join these tables to create a schedule out for a year.
Here is the code to create the Dates view
WITH cteIDate AS (
SELECT 1 AS WeekID,
dateadd(day, (@@datefirst - datepart(weekday, dateadd(year, YEAR(Getdate()) - 1900, 0)) + (10 - @@datefirst) * 2) % 7, dateadd(year, YEAR(Getdate()) - 1900, 0) ) as StartDate
, dateadd(day, (@@datefirst - datepart(weekday, dateadd(year, YEAR(Getdate()) - 1900, 0)) + (10 - @@datefirst) * 2) % 7, dateadd(year, YEAR(Getdate()) - 1900, 0) ) + 7 as EndDate
UNION ALL
SELECT WeekID + 1 AS ID,
dateadd(day , 7, StartDate) AS StartDate, dateadd(day , 7, EndDate) AS EndDate
FROM cteIDate
WHERE dateadd(day , 7, EndDate) < convert(varchar(4),Year(getdate())+1)+'-12-01'
)
SELECT * from cteIDate ci
Before providing a solution I want to know how you want to deal with those people vacations?
Hi Leo,
I'd probably join OnCallSupport to the CTE 4 times, once for each team, and select the row where mod (WeekId, <number of team mambers>) = SequenceID
Kent
I'd probably join OnCallSupport to the CTE 4 times, once for each team, and select the row where mod (WeekId, <number of team mambers>) = SequenceID
Kent
ASKER
Vacation? Damn didn't really think about we just swap or someone usually or volunteers to take that place.. I am open to any suggestion you may provide.
I think the best option is to have a table with all vacations so the algorithm can skip those weeks to the new technician available.
Or you can just generate the on call schedule without the vacation weeks and then make any kind of adjustment manually.
Or you can just generate the on call schedule without the vacation weeks and then make any kind of adjustment manually.
ASKER
For now manually will do.
ASKER
Kent:
I dont understand your join there is no relationship between my team table and schedule I am trying to create one. Can you elaborate on how you would create this join?
I dont understand your join there is no relationship between my team table and schedule I am trying to create one. Can you elaborate on how you would create this join?
Hi Leo,
A full join doesn't require a join key, it just joins on every row. That may not always be the most efficient. You can do the same thing with sub-queries, though.
Expanding upon your CTE...
A full join doesn't require a join key, it just joins on every row. That may not always be the most efficient. You can do the same thing with sub-queries, though.
Expanding upon your CTE...
WITH cteIDate AS (
SELECT 1 AS WeekID,
dateadd(day, (@@datefirst - datepart(weekday, dateadd(year, YEAR(Getdate()) - 1900, 0)) + (10 - @@datefirst) * 2) % 7, dateadd(year, YEAR(Getdate()) - 1900, 0) ) as StartDate
, dateadd(day, (@@datefirst - datepart(weekday, dateadd(year, YEAR(Getdate()) - 1900, 0)) + (10 - @@datefirst) * 2) % 7, dateadd(year, YEAR(Getdate()) - 1900, 0) ) + 7 as EndDate
UNION ALL
SELECT WeekID + 1 AS ID,
dateadd(day , 7, StartDate) AS StartDate, dateadd(day , 7, EndDate) AS EndDate
FROM cteIDate
WHERE dateadd(day , 7, EndDate) < convert(varchar(4),Year(getdate())+1)+'-12-01'
)
SELECT ci.*,
(SELECT TechName FROM OnCallSupport WHERE TeamId = 1 AND SequenceId = mod (ci.WeekId, 4) + 1) Member1,
(SELECT TechName FROM OnCallSupport WHERE TeamId = 2 AND SequenceId = mod (ci.WeekId, 5) + 1) Member2,
(SELECT TechName FROM OnCallSupport WHERE TeamId = 3 AND SequenceId = mod (ci.WeekId, 4) + 1) Member3,
(SELECT TechName FROM OnCallSupport WHERE TeamId = 4 AND SequenceId = mod (ci.WeekId, 4) + 1) Member4
FROM cteIDate ci
I guess the 4 teams with 4 members was only an example. What happen if the number of team changes? Or if one team has less or more members than others?
ASKER
Kent your query has Syntax errors once fixed query still fails
query fixed here mod is not modulus in SQL
Error
query fixed here mod is not modulus in SQL
SELECT ci.*,
(SELECT * FROM [OnCallSupport] WHERE Team = '1' AND SequenceId = (ci.WeekId % 4) + 1) Member1,
(SELECT * FROM [OnCallSupport] WHERE Team = '2' AND SequenceId = (ci.WeekId % 5) + 1) Member2,
(SELECT * FROM [OnCallSupport] WHERE Team = '3 AND SequenceId = (ci.WeekId % 4) + 1) Member3,
(SELECT * FROM [OnCallSupport] WHERE Team = '4' AND SequenceId = (ci.WeekId % 4) + 1) Member4
FROM dbo.vw_OnCallWeeks ci
Error
Msg 116, Level 16, State 1, Line 2
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
ASKER
Vitor:
The number of teams should not change(Has not changed in 5 years). That can remain static if it makes it easier. This is not for a mission critical app its just to create a calendar schedule so people have an idea when they are up. As far as new users to each team rotation yes that does happen but even that does not change frequently. Last change was an add to a team and that add was over a year ago.
The number of teams should not change(Has not changed in 5 years). That can remain static if it makes it easier. This is not for a mission critical app its just to create a calendar schedule so people have an idea when they are up. As far as new users to each team rotation yes that does happen but even that does not change frequently. Last change was an add to a team and that add was over a year ago.
In that case I would create an Excel worksheet for this. Too much complex to loose time with SQL Server when in Excel you can do it fast.
ASKER
Well I still need create notifications out of it and an Excel file some where is not best idea. This is the other extreme i need to be in the middle some where.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.