SQL how to exclude bank Holiday and Christmas DAY on Date diff
SQL how to exclude bank Holiday and Christmas DAY on Date diff
Any advice will be highly appreciated
How to exclude Bank holidays, Christmas DAY in date datediff in the UK
I have a customer table with all the customer booking in a hotel, I would like to calculate the length of stay but I want to exclude bank holidays and other holidays in the UK.
Many thanks
SELECT [StartDate],
DATEDIFF(DAY,[StartDate],[EndDate]) AS Bookingdays
,[EndDate]
,[CustomerId]
,[BookingID]
FROM [CustomerBooking]
Elaborating Dan Guzman's comment: (1) create Calendar Table. (2) mark the unique days in the Calendar Table. (3) select the dates from the Calendar Table which are between the [StartDate] and [EndDate] and that the column for unique days is not in (Bank holidays, Christmas DAY). - this is done with simple JOIN between your table and the Calendar Table
– Ronen Ariely
Jun 30 at 18:42
Hi Ronen, Many Thanks for this, Just wondering on Join calender table does it need connect to both StartDate and EndDate on [CustomerBooking] table? This is when doing a left Join...
– SQLfun
2 days ago
1 Answer
1
To implement this, you will require a HolidayCalendar table where all the holidays date and Christmas days will be listed.
Use that calendar table to filter your booking dates WHERE StartDate NOT IN (SELECT HolidayDate from HolidayCalendar) OR EndDate NOT IN (SELECT HolidayDate from HolidayCalendar)
WHERE StartDate NOT IN (SELECT HolidayDate from HolidayCalendar) OR EndDate NOT IN (SELECT HolidayDate from HolidayCalendar)
You can filter your date by other many ways with JOINS or CTE with more optimized query. Above code is just an example.
What about
DateBetweenStartDateAndEndDate NOT IN (SELECT HolidayDate from HolidayCalendar)?– HABO
Jun 30 at 20:33
DateBetweenStartDateAndEndDate NOT IN (SELECT HolidayDate from HolidayCalendar)
When using NOT IN, the left side operand should be only one value. i.e. DateBetweenStartDateAndEndDate should return only one date. If in your case, it will return always single value, then definitely you can use this.
– Prateek Sharma
Jul 1 at 6:19
Hi Prateek, Many Thanks for this, Just wondering on Join calender table does it need connect to Both StartDate and EndDate on [CustomerBooking] table? This is when doing a left Join...
– SQLfun
2 days ago
As i am getting, if booking range is from date 1st to 3rd, then you would need to filter all the dates which belongs to that range i.e. 1st, 2nd and 3rd. If this is a case, then the logic will be complex, as you would need a CTE or intermediate table with all booking dates and apply the join calendar table on all those booking dates (not only start and end date). If this is not a case, and booking range is max to 2 days only i.e. 1st and 2nd, then YES, you will required to join Calendar table on both Start Date and End Date.
– Prateek Sharma
yesterday
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Search for Calendar Table.
– Dan Guzman
Jun 30 at 10:51