SQL Date Range between a week by week range

Question

we are making a date range picker for employee vacations

for example i will user January 2016 as an example and our system goes from sunday through saturday

if an employee takes a vacation from January 14-20 they should be in the date range 10-16 and also 17-23

I just cant think of how to write an sql query that will limit it to those 2 dates.

What i have is:

DECLARE @WeekRangeStart DATETIME ='2016/01/10';
DECLARE @WeekRangeEnd DATETIME = '2016/01/16';

SELECT [ID], 
       [EmpName], 
       [EmpType] 
FROM Vacations
WHERE VacationStartDate >= @WeekRangeStart OR VacationEndDate >= @WeekRangeStart 

--OUTPUT
--ALL DAYS BEFORE THIS WOULD BE TRUE...
--1/14/2016 >= 1/10/2016 TRUE
--1/20/2016 >= 1/10/2016 TRUE
-- NEXT WEEK 
--1/14/2016 >= 1/17/2016 FALSE
--1/20/2016 >= 1/17/2016 TRUE
-- NEXT WEEK 
--1/14/2016 >= 1/24/2016 FALSE
--1/20/2016 >= 1/24/2016 FALSE
--ALL DAYS AFTER THIS DAY WOULD BE FALSE...

but this only works for for a things that have passed, But if i were to book a day in march i would always show on the schedules because my startdate would greater than today. How should i go about limiting it to that range only?

Your range overlap logic in WHERE clause is incorrect. Take a look at this question: http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap — jrook, Feb 04 '16 at 21:14

score 0 · Answer 1 · answered Feb 04 '16 at 19:56

0

DECLARE @WeekRangeStart DATETIME ='2016/01/10';
DECLARE @WeekRangeEnd DATETIME = '2016/01/16';

SELECT [ID], 
   [EmpName], 
   [EmpType] 
FROM Vacations
WHERE VacationStartDate between @WeekRangeStart and @WeekRangeEnd
     or VacationEndDate Between @WeekRangeStart and @WeekRangeEnd

answered Feb 04 '16 at 19:56

Dave Kelly

129
1
15

this could fail if they take a 2 month vacation thou, need to look up overlapping date ranges – Dave Kelly Feb 04 '16 at 19:57

score 0 · Answer 2 · answered Feb 04 '16 at 20:44

This might be useful. Using a recursive CTE I got all the dates the vacations spans. Then return the weeks it spans based on the week ranges provided.

DECLARE @WeekRangeStart DATETIME ='2016/01/10';
DECLARE @WeekRangeEnd DATETIME = '2016/01/16';
DECLARE @VacationStartDate DATETIME = '2016-01-14'
DECLARE @VacationEndDate DATETIME = '2016-01-20'


;WITH cte AS
  (
    SELECT @VacationStartDate AS Dates
    UNION ALL
    SELECT DATEADD(dd, 1, dates)
    FROM cte
    WHERE dates < @VacationEndDate
  ) 

SELECT DISTINCT
 DATEADD(dd, -(DATEPART(dw, dates)-1), dates) AS WeekStartDate,
 DATEADD(dd, 7-(DATEPART(dw, dates)), dates) AS WeekEndDate
FROM cte
WHERE dates BETWEEN @WeekRangeStart AND @WeekRangeEnd

SQL Date Range between a week by week range

2 Answers2