SQL: Efficiently get the number of hours between 2 Datetimes while excluding weekends

Question

This is similar to the question found here...

Count work days between two dates

The big difference is that I need to calculate my range in hours AND I need to account for partial days, some of which might be weekends. For example, most of the code samples I've see take Saturday and Sunday as a whole, like this which returns the total number of non-weekend days in the range...

SELECT @WeekDays = 
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

I need something like this, but in terms of hours. Most forums/solutions would have me multiply the weekend day count by 24, which doesn't work if I enter something like "Saturday at noon" to "Monday at 6pm". I'd be subtracting 48 hours from my total hour count instead of 36.

For the record, here's how I'm doing it now to get ALL hours found in the range...

SELECT @HourCount = DATEDIFF(hh, @StartDate, @EndDate)

I'm open to either calculating it correctly in one move or using @HourCount above and subtracting a "weekend hour count" found in the range. Just not sure how to do either one accurately.

Last note, the all important efficiency thing. Technically, I could probably use a while loop and iterate from @StartDate to @EndDate while incrementing the hours (or even minutes), checking the current day, and adding 1 to a total if it's not a weekend day. This is probably my worst case scenario, but I'm looking for a solution wayyyy more efficient than this. Any suggestions or solutions are welcome.

Answer 1

Not exhaustively tested, but one version;

WITH cte AS (
  SELECT start_time, end_time,
    (@@datefirst  + datepart(weekday, start_time)) % 7 start_day,
    (@@datefirst  + datepart(weekday, end_time  )) % 7 end_day
  FROM test
), cte2 AS (
  SELECT CASE WHEN start_day < 2 THEN CAST(start_time + 2 - start_day AS DATE)
                                 ELSE start_time END start_time,
         CASE WHEN end_day   < 2 THEN CAST(end_time - end_day AS DATE)
                                 ELSE end_time END end_time
  FROM cte
)
SELECT DATEDIFF(hh, start_time, end_time) - 
       DATEDIFF(wk, start_time, end_time) * 48 diff
FROM cte2;

The first common table expression gets the day of week, 0 as saturday, 1 as sunday etc. for the start and end time.

The second common table expression adjusts the start and end time to never be on a weekend (start time is moved forward to monday, end time back to friday (or rather midnight to saturday).

The query itself then just calculates the difference between the times, adjusting for weekends.

The only obvious problems I can see with the query are;

• If both start and end are on the same weekend, the time returned will be negative, so you may want to add logic to deal with that.

• If some locale considers start of the week somewhere mid-week it may calculate the weekends wrong, so you may in that case want to adjust for that.

An SQLfiddle with some test cases.

Answer 2

Consider creating a permanent calendar table and utility numbers table for this and other date/time queries. That will allow you to use an efficient set-based query, and account for non-business days besides weekends. See http://www.dbdelta.com/calendar-table-and-datetime-functions/ for example scripts to create and load these tables.

Below is an example query that illustrates one way to these tables that you can tweak according to your needs.

DECLARE 
     @StartDate datetime = '2014-07-12 12:00:00'
    ,@EndDate datetime = '2024-07-14 18:00:00';
WITH 
    business_days AS (
        SELECT CalendarDate
        FROM dbo.Calendar
        WHERE
            CalendarDate BETWEEN CAST(@StartDate AS date) AND DATEADD(day, 1, @EndDate)
            AND BusinessDay = 1
    )
    ,business_hours AS (
        SELECT DATEADD(hour, Number, CAST(CalendarDate AS datetime)) AS BusinessHour
        FROM business_days
        CROSS JOIN dbo.Numbers
        WHERE Number BETWEEN 0 AND 23
    )
SELECT COUNT(*) AS total_business_hours
FROM business_hours
WHERE
    BusinessHour >= @StartDate
    AND BusinessHour < @EndDate;