1

I have a problem I need to solve using SQL. The problem is that I have to calculate how many seconds between the date are in working hours, how many are outside working hours and how many are in weekends. Working hours are between 0800 and 1600. Some sort of function should be able to do this, but I can’t wrap my head around how. Can anyone help me?

Example data(SQL Server 2008 R2 SP2):

Opendate: 2014-10-01 10:33:57.767 Closedate: 2014-10-09 10:33:52.733

André Bondevik
  • 11
  • 2
  • can the period between open and closedate span multiple weekends? – pancho018 Oct 09 '14 at 09:40
  • yes, it can. there is no restrictions on the span. See my update on working hours. – André Bondevik Oct 09 '14 at 10:51
  • possible duplicate of [Calculate time difference (only working hours) in minutes between two dates](http://stackoverflow.com/questions/3296923/calculate-time-difference-only-working-hours-in-minutes-between-two-dates) – pancho018 Oct 10 '14 at 12:19

1 Answers1

0

I'm using these functions that return the non-weekend seconds between two dates:

CREATE FUNCTION [dbo].[DateDiff_NoWeekends](
    @date1 DATETIME,
    @date2 DATETIME
)

RETURNS INT AS BEGIN
    DECLARE @retValue INT

    SET @date1 = dbo.__CorrectDate(@date1, 1)
    SET @date2 = dbo.__CorrectDate(@date2, 0)

    IF (@date1 >= @date2)
        SET @retValue = 0
    ELSE BEGIN
        DECLARE @days INT, @weekday INT
        SET @days = DATEDIFF(d, @date1, @date2)
        SET @weekday = DATEPART(dw, @date1) - 1

        SET @retValue = DATEDIFF(s, @date1, @date2) - 2 * 24 * 3600 * ((@days + @weekday) / 7) 
    END

    RETURN @retValue
END

GO


CREATE FUNCTION [dbo].[__CorrectDate](
    @date DATETIME,
    @forward INT
)

RETURNS DATETIME AS BEGIN
    IF (DATEPART(dw, @date) > 5) BEGIN

        IF (@forward = 1) BEGIN
            SET @date = @date + (8 - DATEPART(dw, @date))
            SET @date = DateAdd(Hour, (8 - DatePart(Hour, @date)), @date)
        END ELSE BEGIN
            SET @date = @date - (DATEPART(dw, @date)- 5)
            SET @date = DateAdd(Hour, (18 - DatePart(Hour, @date)), @date)
        END
        SET @date = DateAdd(Minute, -DatePart(Minute, @date), @date)
        SET @date = DateAdd(Second, -DatePart(Second, @date), @date)
    END

    RETURN @date
END

This should help to get started to find also the solution for the other tasks.

Here's a sql-fiddle demo with your sample data:

SELECT [weekday-seconds between] =
    (dbo.DateDiff_NoWeekends('2014-10-01 10:33:57.767','2014-10-09 10:33:52.733'))

=> 518395
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • Hi, thanks for your reply. How would you incorporate working hours in to this? – André Bondevik Oct 09 '14 at 10:50
  • @AndréBondevik: i've yet not the time to investigate this further. But i guess you could use similar methods. You need one that normalizes the start and end-dates. Apart from that you can calculate the number of seconds via `SELECT DATEDIFF(s, '2014-10-09 08:00:00', '2014-10-09 16:00:00')`(28800) and multiply that with the [number of working-days between both dates](http://stackoverflow.com/questions/252519/count-work-days-between-two-dates). – Tim Schmelter Oct 09 '14 at 11:09