Getting all data ranges which contains a flagged day of the week in a given interval

Question

I Have a table which contains information about blockages in a operation for every day of the week in date range. Given that, I need to perform a query which brings only the records that have an active blockage in a given range. E.g.: Suppose there are three registers in the table.

START_DATE   END_DATE    MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY SUNDAY
2019-01-01   2020-12-31    1       0       0         0       0       0       0
2019-01-31   2019-02-03    0       0       0         0       0       0       1
2018-01-01   2100-12-31    0       0       0         0       0       1       1

And I want to get all the registers which have a blockage between 2019-01-31 and 2019-02-02 (Thursday, Friday and Saturday). The result should bring only the last register. Is there any way of performing this search entirely in the data base using SQL?

Use a [Calendar Table](http://www.sqlservercentral.com/articles/calendar/145206/) to normalise your data, then you can query against that and return the dates you need. Have a go and if you get stuck, please comment and add your attempt(s) to your question. — Thom A, Feb 02 '19 at 14:41
Possible duplicate of [Determine Whether Two Date Ranges Overlap](https://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap) — Dan Guzman, Feb 02 '19 at 14:42
@DanGuzman. Checking for overlapping is one little part of the problem. The main issue is checking if in all those overlapping dates, which one has a day of the week blocked AND overlapping — AlisonFahl, Feb 02 '19 at 16:09

score 1 · Accepted Answer · answered Feb 02 '19 at 18:46

At first, you will have to find intersections of the intervals in the table and the interval you are interested in. Assuming that you define the criteria using two variables @StartDate and @EndDate, it could look like this:

WHERE START_DATE <= @EndDate AND END_DATE >= @StartDate

Then, you will have to find the start and end of the intersection interval:

CASE WHEN START_DATE > @StartDate THEN START_DATE ELSE @StartDate END
CASE WHEN END_DATE < @EndDate THEN END_DATE ELSE @EndDate END

To decide, for example, if that interval contains a Saturday, I count the number of complete weeks between an initial Sunday and the start date and compare that to the respective number for the day after the end date. If both numbers differ, the interval contains a Saturday. My complete suggestion looks like this:

DECLARE @StartDate date = '20190131', @EndDate date = '20190202';

SELECT YourTable.* FROM YourTable
CROSS APPLY( VALUES (
    CASE WHEN START_DATE > @StartDate THEN START_DATE ELSE @StartDate END,
    CASE WHEN END_DATE < @EndDate THEN END_DATE ELSE @EndDate END)
) Intersection (iStart, iEnd)
CROSS APPLY(
  VALUES(DATEDIFF(day, 0, iStart), 1 + DATEDIFF(day, 0, iEnd))
) DaysCount (s, e)
WHERE START_DATE <= @EndDate AND END_DATE >= @StartDate
AND (
     (e/7 > s/7 AND SUNDAY = 1)
  OR ((e+1)/7 > (s+1)/7 AND SATURDAY = 1)
  OR ((e+2)/7 > (s+2)/7 AND FRIDAY = 1)
  OR ((e+3)/7 > (s+3)/7 AND THURSDAY = 1)
  OR ((e+4)/7 > (s+4)/7 AND WEDNESDAY = 1)
  OR ((e+5)/7 > (s+5)/7 AND TUESDAY = 1)
  OR ((e+6)/7 > (s+6)/7 AND MONDAY = 1)
);

Remark:

For the counting of days, I use the initial “date 0”, which is 1900-01-01 (a Monday) in SQL Server.

Gordon Linoff · Answer 2 · 2019-02-02T14:54:52.377

You need to generate all the days and then look for any blockages by day of week:

with days as (
      select convert(date, '2019-01-31') as dte
      union all
      select dateadd(day, 1, dte)
      from dte
      where dte < '2019-02-02'
     )
select
from blockages b cross join
     days d
where d.dte >= b.start_date and d.dte <= b.end_date and
      ( (datename(weekday, d.date) = 'Monday' and Monday = 1) or
        (datename(weekday, d.date) = 'Tuesday' and Tuesday = 1) or
        (datename(weekday, d.date) = 'Wednesday' and Wednesday = 1) or
        (datename(weekday, d.date) = 'Thursday' and Thursday = 1) or
        (datename(weekday, d.date) = 'Friday' and Friday = 1) or
        (datename(weekday, d.date) = 'Saturday' and Saturday = 1) or
        (datename(weekday, d.date) = 'Sunday' and Sunday = 1)
      );

Note that if you have more than 99 days in your range, you'll need to include option (maxrecursion 0) to generate the days. Alternatively, you can use a calendar table or tally table.

I'd personally recommend a Tally Table over a rCTE here. It's not RBAR, and it won't "break" at 100 rows. — Thom A, Feb 02 '19 at 14:49

Getting all data ranges which contains a flagged day of the week in a given interval

2 Answers2