I have 2 tables in a database, the first has the following kind of information in it
|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
|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.
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.
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-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.
Answered By – Alan Schofield
Answer Checked By – Mary Flores (AngularFixing Volunteer)