How to find consecutive days without weekends or holidays?
-
Hi everyone, I'm hoping someone can help me with some sql statements. I have a temp table that contains 30 dates that a student has missed in the last year. I also have a holiday table of when training was not available. I want to find out if there are 6 consecutive days missed excluding weekends and holidays (from the holiday table). I know this is some nasty looping statement but I can't get my brain around it. I would like do this in a stored proc but I could use C# if necessary. :^) Thanks, Jessica
-
Hi everyone, I'm hoping someone can help me with some sql statements. I have a temp table that contains 30 dates that a student has missed in the last year. I also have a holiday table of when training was not available. I want to find out if there are 6 consecutive days missed excluding weekends and holidays (from the holiday table). I know this is some nasty looping statement but I can't get my brain around it. I would like do this in a stored proc but I could use C# if necessary. :^) Thanks, Jessica
Why don't you give it a try, post your code, and we will help you where you are struggling. What you have provided here sounds too much like a school assignment. People aren't likely to do your assignment for you - but they will help you figure out the solution, provided you put forth an effort. That's just my suggestion. --EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
-
Hi everyone, I'm hoping someone can help me with some sql statements. I have a temp table that contains 30 dates that a student has missed in the last year. I also have a holiday table of when training was not available. I want to find out if there are 6 consecutive days missed excluding weekends and holidays (from the holiday table). I know this is some nasty looping statement but I can't get my brain around it. I would like do this in a stored proc but I could use C# if necessary. :^) Thanks, Jessica
Nasty requirements - here is a first attempt using a cursor. I think I would change the EndDate calculation to a function so that you could remove the cursor.
DECLARE @StartDate DATETIME DECLARE @EndDate DATETIME DECLARE @cnt INTEGER DECLARE cur CURSOR FOR SELECT AbsentDate FROM #tmpTable OPEN cur FETCH NEXT FROM cur INTO @StartDate WHILE @@FETCH_STATUS = 0 BEGIN SET @cnt = 0 SET @EndDate = @StartDate WHILE @cnt < 6 BEGIN SET @cnt = @cnt + 1 SET @EndDate = DATEADD(d,1,@EndDate) WHILE (DATEPART(dw,@EndDate) IN (1,7)) OR (EXISTS(SELECT * FROM Holidays WHERE HolidayDate = @EndDate)) BEGIN SET @EndDate = DATEADD(d,1,@EndDate) END END IF ((SELECT COUNT(*) FROM #tmpTable WHERE AbsentDate BETWEEN @StartDate AND @EndDate) > 5) BEGIN -- Save Date -- or do other desired processing END FETCH NEXT FROM cur INTO @StartDate END CLOSE cur DEALLOCATE cur