sql calculate business hours for weekdays by opening and closing hours range

Question

I want to calculate the business hour between given date range (Opening day and closing day)

for example: Business working hours

ID WeekName OpeningHour ClosingHour

01 Monday 8.00AM 4.00PM

02 Tuesday 8.00AM 4.00PM

03 Wednesday 8.00AM 4.00PM

04 Thursday 8.00AM 4.00PM

05 Friday 8.00AM 4.00PM

If I give a date range for example
Opening day: 01/04/2014 09.00AM
Closing day: 03/04/2014 14.00 AM

I want to calculate the business hour from given date range there are 3 days between given range

01/04/2014

02/04/2014

03/04/2014

first I want to check the day name and calculate the hour from business hour (from above table) .

01/04/2014 - Tuesday

02/04/2014 - Wednesday

03/04/2014 - Thursday

Results I expecting:

GivenDate DayID DayName DateDiff

01/04/2014 01 Tuesday 7

02/04/2014 02 Wednesday 8

03/04/2014 03 Thursday 6

Did you do some research on the web? I think you can find plenty of articles related to this problem. — Maverick, Mar 19 '14 at 13:45
i did the research i didn't reach the expected result !!! if i am getting result i didn't ask question here !!! — LittleDragon, Mar 19 '14 at 13:50
Will the start and end of the range that you're querying for always fall within a single week? Or could they span (one or more?) weekends also? — Damien_The_Unbeliever, Mar 19 '14 at 13:51
@Damien_The_Unbeliever the days can be span thought more week and can also include the weekends — LittleDragon, Mar 19 '14 at 13:53

Dan · Accepted Answer · 2015-09-28T09:40:58.857

Here on Stackoverflow, it is generally frowned upon to just ask a question, expecting others to do your work for you. You should at least show some minimum effort, for example some SQL that you have tried out so far.

That said, here's the query that will return what you want. This query works even if you don't have a Date (Calendar) table, as it generates a sequence of numbers from a system table. These numbers are then, in turn, added to the startdate, to return one record for each day in the interval:

SELECT * INTO #WorkingHours 
FROM (VALUES (1, 'Monday', '08:00', '16:00')
    ,(2, 'Tuesday', '08:00', '16:00')
    ,(3, 'Wednesday', '08:00', '16:00')
    ,(4, 'Thursday', '08:00', '16:00')
    ,(5, 'Friday', '08:00', '16:00'))
E(DayId, DayName, OpeningHour, ClosingHour)

DECLARE @StartDate DATETIME = '2014-04-01 09:00:00'
DECLARE @EndDate DATETIME = '2014-04-03 14:00:00'

SELECT [GivenDate], [DayID], [DayName],
    DATEDIFF(HOUR, CASE WHEN @StartDate > [OpeningDateTime] THEN @StartDate ELSE [OpeningDateTime] END, 
        CASE WHEN @EndDate < [ClosingDateTime] THEN @EndDate ELSE [ClosingDateTime] END) AS [DateDiff]
FROM (
    SELECT CAST(@StartDate + n - 1 AS DATE) AS [GivenDate]
        , n AS [DayID]
        , DATENAME(dw, @StartDate + n - 1) AS [DayName]
        , CAST(CAST(CAST(@StartDate + n - 1 AS DATE) AS VARCHAR) + ' ' + OpeningHour AS DateTime) AS OpeningDateTime
        , CAST(CAST(CAST(@StartDate + n - 1 AS DATE) AS VARCHAR) + ' ' + ClosingHour AS DateTime) AS ClosingDateTime
    FROM    
        -- Numbers, for expanding the date range:
        (SELECT ROW_NUMBER() OVER (ORDER BY object_id) n FROM sys.all_objects) Numbers
    INNER JOIN #WorkingHours ON DayName = DATENAME(dw, @StartDate + n - 1)
    WHERE Numbers.n <= DATEDIFF(d, @StartDate, @EndDate) + 1
) SubQuery

And here is the resulting output:

enter image description here

thnx for very much for help me its working and have new SQL stuff for learn i cant add reputation point for you because i dont have enough points sorry for that — LittleDragon, Mar 20 '14 at 03:48
@dan i got error Invalid column name 'number'. how i can fix it — Amrik, Sep 24 '15 at 22:04
Replace `ROW_NUMBER() OVER (ORDER BY number)` with `ROW_NUMBER() OVER (ORDER BY object_id)`. I've edited my answer accordingly. — Dan, Sep 28 '15 at 09:40

score 0 · Answer 2 · 2014-03-20T11:46:29.480

This is very simple if you have Date(Calendar) table. First thing you join your start and end date to calendar table to get days. Than you just compare those DATENAME to what you have in your table. Below is the sample code using table variable and DATE (calendar) table.

DECLARE @BusHours TABLE
    (
     ID INT
    ,DayOfWeekName VARCHAR(10)
    ,OpeningHour VARCHAR(7)
    ,ClosingHOur VARCHAR(7)
    )

INSERT INTO @BusHours
        ( ID, DayOfWeekName, OpeningHour, ClosingHour )
    VALUES  ( 1, 'Monday', '08:00AM', '04:00PM' )
    ,       ( 2, 'Tuesday', '08:00AM', '04:00PM' )
    ,       ( 3, 'Wednesday', '08:00AM', '04:00PM' )
    ,       ( 4, 'Thursday', '08:00AM', '04:00PM' )
    ,       ( 5, 'Friday', '08:00AM', '04:00PM' )

DECLARE @StartDate DATETIME = '04/01/2014 09:00AM'
   ,@EndDate DATETIME = '04/03/2014 14:00PM'

SELECT d.Date_Dt AS GivenDate
       ,b.ID AS DayID
       ,b.DayOfWeekName AS DayName
       ,CASE WHEN DATENAME(WEEKDAY, @StartDate) = b.DayOfWeekName
             THEN DATEDIFF(hh, @StartDate,
                           CONVERT(DATETIME, CONVERT(VARCHAR(12), @StartDate, 101) + ' ' + b.ClosingHour))
             WHEN DATENAME(WEEKDAY, @EndDate) = b.DayOfWeekName
             THEN DATEDIFF(hh, CONVERT(DATETIME, CONVERT(VARCHAR(12), @EndDate, 101) + ' ' + b.OpeningHour), @EndDate)
             ELSE DATEDIFF(hh, CONVERT(DATETIME, '01/01/2014' + ' ' + b.OpeningHour),
                           CONVERT(DATETIME, '01/01/2014' + ' ' + b.ClosingHour))
        END AS HoursOperation
    FROM dbo.Date AS d
    JOIN @BusHours AS b
        ON DATENAME(WEEKDAY, d.Date_Dt) = b.DayOfWeekName
    WHERE d.Date_Dt >= CONVERT(DATE, @startDate)
        AND d.Date_Dt <= CONVERT(DATE, @EndDate)

Table definition for [Date] table at my work. Not everyone has the same implementation of it, but if you search around you can find many scripts to generate it.

CREATE TABLE [dbo].[Date]
    (
     [Date_Id] [int] NOT NULL
    ,[Date_Dt] [date] NULL
    ,[Date_Nm] [varchar](9) NULL
    ,[DayType_Cd] [varchar](7) NULL
    ,[DayofWeek_Nbr] [int] NULL
    ,[DayofWeek_Nm] [varchar](10) NULL
    ,[DayofMonth_Nbr] [int] NULL
    ,[DayofYear_Nbr] [int] NULL
    ,[Week_Nbr] [int] NULL
    ,[Week_Nm] [varchar](5) NULL
    ,[WeekFirstDay_Dt] [date] NULL
    ,[WeekLastDay_Dt] [date] NULL
    ,[WeekofYear_Nbr] [int] NULL
    ,[Month_Nbr] [int] NULL
    ,[Month_6_Nbr] [int] NULL
    ,[Month_Nm] [varchar](6) NULL
    ,[MonthFull_Nm] [varchar](9) NULL
    ,[MonthLastDay_Dt] [date] NULL
    ,[MonthofYear_Nbr] [int] NULL
    ,[Quarter_Nbr] [int] NULL
    ,[Quarter_Nm] [varchar](5) NULL
    ,[QuarterofYear_Nbr] [int] NULL
    ,[Year_Nbr] [int] NULL
    ,[Year_Nm] [varchar](4) NULL
    ,[FYDayofWeek_Nbr] [int] NULL
    ,[FYWeek_Nbr] [int] NULL
    ,[FYWeek_Nm] [varchar](5) NULL
    ,[FYWeekFirstDay_Dt] [date] NULL
    ,[FYWeekLastDay_Dt] [date] NULL
    ,[FYWeekofYear_Nbr] [int] NULL
    )

It is just a table that has random date information. Like having every day listed from Jan 1 to Dec 31 for many years. Plus it has columns populated for `DAY()` , `YEAR()`, `MONTH()` and many other date function. This way instead of generating those values on the fly you are able to just look up from column. I updated answer with `Date` table definition. — , Mar 20 '14 at 11:43
@DjAdmin here is question on SO that has scripts for generating Calendar table http://stackoverflow.com/questions/5635594/how-to-create-a-calender-table-for-100-years-in-sql — , Mar 20 '14 at 11:52
thnx everyone for the answer i could write my own calculation function table — LittleDragon, Apr 08 '14 at 09:36
@littledragon can you share your table schema? i am on the same boat and need help to design database. Thanks in advance — Amrik, Sep 24 '15 at 22:02

sql calculate business hours for weekdays by opening and closing hours range

2 Answers2