troubleshooting Question

SQL Recursion schedule

Avatar of Leo Torres
Leo TorresFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
13 Comments1 Solution152 ViewsLast Modified:
I have a table of dates here are my column names

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
ASKER CERTIFIED SOLUTION
Kent Olsen
Data Warehouse / Database Architect

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros