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

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

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

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.

CodePudding user response：

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

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.