0

I need to calculate business hours between to dates.

I need to exclude following from that hours.

  1. Out of working hours (6pm -9am)
  2. Weekends
  3. Holidays

For holidays I have a table HOLIDAYS as

DATE        NAME
----------------------------------
2012-01-02  New Year's Day
2012-04-06  Good Friday
2012-04-09  Easter Monday

Notes:
1. I have hours in start and finish dates ('2012/03/15 14:00' to '2012/03/22 17:30').
2. I'm using Sql Server 2005 so I don't have datetypes DATE or TIME.

davit_gri
  • 130
  • 1
  • 8
  • Possible duplicate of http://stackoverflow.com/questions/5274208/calculate-business-hours-between-two-dates – kaj Mar 15 '12 at 10:35
  • @KAJ I checked that link before asking my question .As I mentioned the solution that were provided not suits me as I need to calculate hours and I can't use DATE or TIME datetypes as I'm using Sql server 2005. – davit_gri Mar 15 '12 at 10:39
  • 1
    There is more than one solution on that question and not all of them rely on date/time data types rather than datetime which is why I suggested it – kaj Mar 15 '12 at 10:43

1 Answers1

3

Try this.

DECLARE @start_date DATETIME 
DECLARE @stop_date DATETIME 
DECLARE @days INT

SELECT @start_date = '2012/03/15 14:00', @stop_date = '2012/03/22 17:30'

SELECT @days = DATEDIFF(DAY, CAST(FLOOR(CAST(@start_date AS FLOAT)) AS DATETIME), CAST(FLOOR(CAST(@stop_date AS FLOAT)) AS DATETIME)) + 1

;WITH CTE_ALL_DAYS AS (
    SELECT TOP (@days) 
        DATEADD(DAY, rn, CAST(FLOOR(CAST(@start_date AS FLOAT)) AS DATETIME)) AS dt
    FROM (
        SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) - 1 AS rn
        FROM master.sys.all_columns AS a
        CROSS JOIN master.sys.all_columns AS b
        ) AS tally
)
SELECT SUM(
        CASE
            WHEN dt = CAST(FLOOR(CAST(@start_date AS FLOAT)) AS DATETIME) THEN 
                CASE 
                    WHEN DATEDIFF(HOUR, @start_date, DATEADD(HOUR, 18, CAST(FLOOR(CAST(@start_date AS FLOAT)) AS DATETIME))) > 9 THEN 9
                    WHEN DATEDIFF(HOUR, @start_date, DATEADD(HOUR, 18, CAST(FLOOR(CAST(@start_date AS FLOAT)) AS DATETIME))) > 0 THEN DATEDIFF(HOUR, @start_date, DATEADD(HOUR, 18, CAST(FLOOR(CAST(@start_date AS FLOAT)) AS DATETIME))) 
                    ELSE 0 
                END
            WHEN dt = CAST(FLOOR(CAST(@stop_date AS FLOAT)) AS DATETIME) THEN 
                CASE 
                    WHEN DATEDIFF(HOUR, DATEADD(HOUR, 9, CAST(FLOOR(CAST(@stop_date AS FLOAT)) AS DATETIME)), @stop_date) > 9 THEN 9
                    WHEN DATEDIFF(HOUR, DATEADD(HOUR, 9, CAST(FLOOR(CAST(@stop_date AS FLOAT)) AS DATETIME)), @stop_date) > 0 THEN DATEDIFF(HOUR, DATEADD(HOUR, 9, CAST(FLOOR(CAST(@stop_date AS FLOAT)) AS DATETIME)), @stop_date) 
                    ELSE 0 
                END 
            ELSE 9      
        END
    )
FROM CTE_ALL_DAYS   
WHERE 
    NOT DATEPART(weekday, dt) IN (1, 7)
    AND NOT dt IN (SELECT [DATE] FROM HOLIDAYS)
Andrey Gurinov
  • 2,825
  • 1
  • 20
  • 23