How to determine consecutive date count excluding weekends in TSQL
Hi everyone,
Thanks for looking at my query and many thanks for your help/advice.
I have a student table and I would like to know How long their session/training has lasted.
I would like to exclude weekends, but I want to count continuous days excluding weekends.
A class has Start Date and End Date, for example, student S1 a can book a class on Jan then gain on Feb and would like to know days for Jan bookings and February while excluding weekends. Basically, I am looking for continuous dates from Start Date and End date where there no break except for the weekend.
SELECT [ID] ,[StartDate] ,[EndDate] ,[DaysOff] AS Consecutive_Booking FROM StudentBooking
If the student (student classifications) has book the class for 5 days or 2 times (Start Date to End Date (Monday to Friday)) in the last 3 months they are Resident else Visitors.
Final results I would like to see, On example data given Student id 1 has been classified as Resident because the has booked the class for more than equal 5 days and the student id 2 has booked the class 2 times for last the last 3 months Monday to Friday with continuous dates while excluding weekends.
Below is the sample of the results I would like to see, student classifications will also include Visitors if they have not met the criteria for Resident
Create a date table that can identify holidays, weekends, etc. Then increment each day you want to count, exclude the weekends (so that Friday is (for example) day 7, Saturday is day 8, Sunday is also day 8, Monday is once again day 8). Optionally exclude holidays. This means that the business day integer for Monday minus one will give you Friday, not Sunday. Then just subtract the numbers from your start and end dates and you have the number of week days (optionally minus holidays) that they had classes.
