Determine if a class falls on a holiday, given the start and end dates of the class and the dates of all holidays

Question

I have 2 tables in a database, the first has the following kind of information in it

SECTION_NUMBER	SECTION_ID	MEETING_ID	DAY_TYPE	MEETING_NUMBER	DATE_TIME_BEGIN	DATE_TIME_END
390	166316	102451	1	1	2023-01-23 9:30:00	2023-05-17 10:50:00
390	166316	102451	3	1	2023-01-23 9:30:00	2023-05-17 10:50:00
655	166314	102452	3	1	2023-01-23 12:00:00	2023-05-20 12:00:00
655	166314	102452	7	1	2023-01-23 12:00:00	2023-05-20 12:00:00
283	166315	102453	7	1	2023-01-23 12:00:00	2023-05-20 12:00:00

Of note, a section will have 1 entry for each day the section meets, 1 being monday, 2 tuesday, etc. In the example screenshot, section 390 meets monday and wednesday, and the first meeting day is 1/23/23 with the end being 5/17/23.

I have a second table with holidays in it

description	DATE_VALUE	DayOfWeek	Day
Winter Break	2023-01-02 0:00:00	1	M
MLK Day	2023-01-16 0:00:00	1	M
Lincoln's Day	2023-02-17 0:00:00	5	F
Non-Teaching Day	2023-02-18 0:00:00	6	S
Washington's Day	2023-02-20 0:00:00	1	M

I have a third table which, for a section number, shows its meeting days. However, it does not take into account holidays.

section_number	NbrOfDays
360	33
655	16

I tried solving the issue in python but then found out that SSRS only supports python scripts in the 2017 version and at my work we are using 2016.

What kind of SQL Server 2016 queries exist that would somehow allow me to iterate through the section rows, and check if a holiday falls between the start and end date, and falls on the day the section meets, and decrement the meeting days by 1 for each holiday which meets that criteria?

Using the data as an example, there are 2 Monday holidays between the section 390 begin and end date, so the NbrOfDays from the third table needs to be updated to 31 from 33 since it has a meeting day type of 1 = Monday.

Answer 1

I don't think the numbers you state quite add up based on your sample data. e.g. in your holiday table, there is only one date that falls between 2023-01-23 and 2023-05-17, not two as you stated.

I'm not sure how you get 16 as the total for section 655 either unless you are not counting weekends?

anyway... Don't worry about the length of this answer the actual answer bit is only a few lines lines of code.

Anyway, I think that you can just create a view that will work this out for you. There is no need for the third table, the view will replace the third table.

Apologies if I'm adding comments that assume you are not familiar with basic querying, I'm only assuming this from the approach you wanted to take.

---

Set up data to replicate your sample

---

I first created your sample data with the following using temp tables called #meets and #hols.

CREATE TABLE #meets(SECTION_NUMBER int, SECTION_ID int, MEETING_ID int, DAY_TYPE int, MEETING_NUMBER int, DATE_TIME_BEGIN DATETIME, DATE_TIME_END datetime)
INSERT INTO #meets VALUES
(390, 166316, 102451, 1, 1, '2023-01-23 9:30:00', '2023-05-17 10:50:00'),
(390, 166316, 102451, 3, 1, '2023-01-23 9:30:00', '2023-05-17 10:50:00'),
(655, 166314, 102452, 3, 1, '2023-01-23 12:00:00', '2023-05-20 12:00:00'),
(655, 166314, 102452, 7, 1, '2023-01-23 12:00:00', '2023-05-20 12:00:00'),
(283, 166315, 102453, 7, 1, '2023-01-23 12:00:00', '2023-05-20 12:00:00')

CREATE TABLE #hols([description] varchar(30), DATE_VALUE date, DayOfWeek int, Day char(1))
INSERT INTO #hols VALUES
('Winter Break'     , '2023-01-02', 1, 'M'),
('MLK Day'          , '2023-01-16', 1, 'M'),
('Lincoln''s Day'   , '2023-02-17', 5, 'F'),
('Non-Teaching Day' , '2023-02-18', 6, 'S'),
('Washington''s Day'    , '2023-02-20', 1, 'M')

---

Add a date/calendar table

---

Then I created a date table. You may already have one so use that if you do but if not, create one in your database as they are incredibly useful for things like this.

This post shows how to create one CreatingADateTable

I've included the code here in case the link is dead.

-- prevent set or regional settings from interfering with 
-- interpretation of dates / literals
SET DATEFIRST  1 -- 1 = Monday, 7 = Sunday

DECLARE @StartDate  date = '20100101'; -- << change this if required

DECLARE @CutoffDate date = DATEADD(DAY, -1, DATEADD(YEAR, 30, @StartDate));

;WITH seq(n) AS 
(
  SELECT 0 UNION ALL SELECT n + 1 FROM seq
  WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
),
d(d) AS 
(
  SELECT DATEADD(DAY, n, @StartDate) FROM seq
),
src AS
(
  SELECT
    TheDate         = CONVERT(date, d),
    TheDay          = DATEPART(DAY,       d),
    TheDayName      = DATENAME(WEEKDAY,   d),
    TheWeek         = DATEPART(WEEK,      d),
    TheISOWeek      = DATEPART(ISO_WEEK,  d),
    TheDayOfWeek    = DATEPART(WEEKDAY,   d),
    TheMonth        = DATEPART(MONTH,     d),
    TheMonthName    = DATENAME(MONTH,     d),
    TheQuarter      = DATEPART(Quarter,   d),
    TheYear         = DATEPART(YEAR,      d),
    TheFirstOfMonth = DATEFROMPARTS(YEAR(d), MONTH(d), 1),
    TheLastOfYear   = DATEFROMPARTS(YEAR(d), 12, 31),
    TheDayOfYear    = DATEPART(DAYOFYEAR, d)
  FROM d
)
SELECT * 
INTO myDateTable -- << CHANGE TABLE NAME HERE IF YOU NEED TO
FROM src
  ORDER BY TheDate
  OPTION (MAXRECURSION 0);

---

Now the answer!

---

The following will give you each section_number, the day of the week for the meeting and the number of days

SELECT 
      SECTION_NUMBER
    , TheDayName
    , NbrOfDays = COUNT(*)
    FROM #meets m 
        JOIN myDateTable d on d.TheDate BETWEEN CAST(m.DATE_TIME_BEGIN as date) AND CAST(m.DATE_TIME_END as date) and m.DAY_TYPE = d.TheDayOfWeek
        LEFT JOIN #hols h on d.TheDate = h.DATE_VALUE
    WHERE h.DATE_VALUE IS NULL
        and d.TheDate >=CAST(GETDATE() as Date) -- optionaly if you want ignore past meetings
    GROUP BY SECTION_NUMBER, DayOfWeek, TheDayName
    ORDER BY SECTION_NUMBER, DayOfWeek

All this does is join every date in the myDateTable table to the #meets table where the dates fall between the start and end dates in the #meets table, it also joins on the day_type so only matching days are returned. It then left joins to the #hols table and then we only include dates where no match was found in the #hols table. Then we simply group the results and count how many records are in each group.

gives us this

If you just want results to look like your example, we can just remove the DayOfWeek grouping like this.

SELECT 
      SECTION_NUMBER
    , NbrOfDays = COUNT(*)
    FROM #meets m 
        JOIN myDateTable d on d.TheDate BETWEEN CAST(m.DATE_TIME_BEGIN as date) AND CAST(m.DATE_TIME_END as date) and m.DAY_TYPE = d.TheDayOfWeek
        LEFT JOIN #hols h on d.TheDate = h.DATE_VALUE
    WHERE h.DATE_VALUE IS NULL
        and d.TheDate >=CAST(GETDATE() as Date) -- optionaly if you want ignore past meetings
    GROUP BY SECTION_NUMBER
    ORDER BY SECTION_NUMBER

which gives us this...

I've left a line in there to filter out past meetings but you can comment that out if you don't need it.

If you want to turn these queries into permanent views then you can do that with something like

CREATE VIEW MeetingCountBySectionAndDay AS 
[copy query from above  here]

Then you can just query the view like a table with something like

SELECT * FROM MeetingCountBySectionAndDay 

If holidays are added/removed or meetings are added/edited, the view will automatically reflect the changes without you needing to do any work.