SQL DateDiff advanced usage?

问题:

I need to calculate the DateDiff (hours) between two dates, but only during business-hours (8:30 - 16:00, no weekends). This result will then be put into the Reaction_Time column as per the example below.

ID           Date           Reaction_Time   Overdue
1    29.04.2003 15:00:00                      
1    30.04.2003 11:00:00        3:30        
2    30.04.2003 14:00:00                      
2    01.05.2003 14:00:00        7:30          YES

*Note: I didn't check to see if the dates in example were holidays.

I'm using SQL Server 2005

This will be combined with a bigger query, but for now all I need is this to get started, I'll try to figure out how to put it all together on my own. Thanks for the help!

Edit: Hey, thanks everyone for the replies. But due to the obvious complexity of a solution on SQL side, it was decided we would do this in Excel instead as that's where the report will be moved anyway. Sorry for the trouble, but I really figured it would be simpler than this. As it is, we just don't have the time.

回答1:

DECLARE @BusHourStart DATETIME, @BusHourEnd DATETIME
SELECT @BusHourStart = '08:30:00', @BusHourEnd = '16:00:00'
DECLARE @BusMinutesStart INT, @BusMinutesEnd INT
SELECT @BusMinutesStart = DATEPART(minute,@BusHourStart)+DATEPART(hour,@BusHourStart)*60, 
@BusMinutesEnd = DATEPART(minute,@BusHourEnd)+DATEPART(hour,@BusHourEnd)*60 
DECLARE @Dates2 TABLE (ID INT, DateStart DATETIME, DateEnd DATETIME)
INSERT INTO @Dates2
SELECT 1, '15:00:00 04/29/2003', '11:00:00 04/30/2003' UNION
SELECT 2, '14:00:00 04/30/2003', '14:00:00 05/01/2003' UNION
SELECT 3, '14:00:00 05/02/2003', '14:00:00 05/06/2003' UNION
SELECT 4, '14:00:00 05/02/2003', '14:00:00 05/04/2003' UNION
SELECT 5, '07:00:00 05/02/2003', '14:00:00 05/02/2003' UNION
SELECT 6, '14:00:00 05/02/2003', '23:00:00 05/02/2003' UNION
SELECT 7, '07:00:00 05/02/2003', '08:00:00 05/02/2003' UNION
SELECT 8, '22:00:00 05/02/2003', '23:00:00 05/03/2003' UNION
SELECT 9, '08:00:00 05/03/2003', '23:00:00 05/04/2003' UNION
SELECT 10, '07:00:00 05/02/2003', '23:00:00 05/02/2003' 

-- SET DATEFIRST to U.S. English default value of 7.
SET DATEFIRST 7

SELECT ID, DateStart, DateEnd, CONVERT(VARCHAR, Minutes/60) +':'+ CONVERT(VARCHAR, Minutes % 60) AS ReactionTime
FROM ( 
    SELECT ID, DateStart, DateEnd, Overtime,
        CASE 
            WHEN DayDiff = 0 THEN 
                CASE 
                    WHEN (MinutesEnd - MinutesStart - Overtime) > 0 THEN (MinutesEnd - MinutesStart - Overtime) 
                    ELSE 0 
                    END
            WHEN DayDiff > 0  THEN 
                CASE 
                    WHEN (StartPart + EndPart - Overtime) > 0 THEN (StartPart + EndPart - Overtime) 
                    ELSE 0 
                    END + DayPart
            ELSE 0
        END AS Minutes 
    FROM(
        SELECT ID, DateStart, DateEnd, DayDiff, MinutesStart, MinutesEnd,
                CASE WHEN(@BusMinutesStart - MinutesStart) > 0 THEN (@BusMinutesStart - MinutesStart) ELSE 0 END +
                CASE WHEN(MinutesEnd - @BusMinutesEnd) > 0 THEN (MinutesEnd - @BusMinutesEnd) ELSE 0 END AS Overtime, 
                CASE WHEN(@BusMinutesEnd - MinutesStart) > 0 THEN (@BusMinutesEnd - MinutesStart) ELSE 0 END AS StartPart,
                CASE WHEN(MinutesEnd - @BusMinutesStart) > 0 THEN (MinutesEnd - @BusMinutesStart) ELSE 0 END AS EndPart,
                CASE WHEN DayDiff > 1 THEN (@BusMinutesEnd - @BusMinutesStart)*(DayDiff - 1) ELSE 0 END AS DayPart
        FROM (
                SELECT DATEDIFF(d,DateStart, DateEnd) AS DayDiff, ID, DateStart, DateEnd,  
                DATEPART(minute,DateStart)+DATEPART(hour,DateStart)*60 AS MinutesStart,
                DATEPART(minute,DateEnd)+DATEPART(hour,DateEnd)*60 AS MinutesEnd 
                FROM (
                        SELECT ID,
                                CASE 
                                        WHEN DATEPART(dw, DateStart) = 7 
                                        THEN DATEADD(SECOND, 1, DATEADD(DAY, DATEDIFF(DAY, 0, DateStart), 2))
                                        WHEN DATEPART(dw, DateStart) = 1 
                                        THEN DATEADD(SECOND, 1, DATEADD(DAY, DATEDIFF(DAY, 0, DateStart), 1))
                                ELSE DateStart END AS DateStart,
                                CASE 
                                        WHEN DATEPART(dw, DateEnd) = 7 
                                        THEN DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, DateEnd), 0))
                                        WHEN DATEPART(dw, DateEnd) = 1 
                                        THEN DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, DateEnd), -1))
                                ELSE DateEnd END AS DateEnd FROM @Dates2
                )Weekends
        )InMinutes
    )Overtime
)Calculation

回答2:

I would recommend building a user defined function that calculates the date difference in business hours according to your rules.

SELECT
  Id,
  MIN(Date) DateStarted,
  MAX(Date) DateCompleted,
  dbo.udfDateDiffBusinessHours(MIN(Date), MAX(Date)) ReactionTime
FROM
  Incident
GROUP BY
  Id

I'm not sure where your Overdue value comes from, so I left it off in my example.

In a function you can write way more expressive SQL than in a query, and you don't clog your query with business rules, making it hard to maintain.

Also a function can easily be reused. Extending it to include support for holidays (I'm thinking of a Holidays table here) would not be too hard. Further refinements are possible without the need to change hard to read nested SELECT/CASE WHEN constructs, which would be the alternative.

If I have time today, I'll look into writing an example function.

---

EDIT: Here is something with bells and whistles, calculating around weekends transparently:

ALTER FUNCTION dbo.udfDateDiffBusinessHours (
  @date1 DATETIME,
  @date2 DATETIME
) RETURNS DATETIME AS
BEGIN
  DECLARE @sat INT
  DECLARE @sun INT
  DECLARE @workday_s INT
  DECLARE @workday_e INT
  DECLARE @basedate1 DATETIME
  DECLARE @basedate2 DATETIME
  DECLARE @calcdate1 DATETIME
  DECLARE @calcdate2 DATETIME
  DECLARE @cworkdays INT
  DECLARE @cweekends INT
  DECLARE @returnval INT

  SET @workday_s = 510 -- work day start:  8.5 hours
  SET @workday_e = 960 -- work day end:   16.0 hours

    -- calculate Saturday and Sunday dependent on SET DATEFIRST option
  SET @sat = CASE @@DATEFIRST WHEN 7 THEN 7 ELSE 7 - @@DATEFIRST END 
  SET @sun = CASE @@DATEFIRST WHEN 7 THEN 1 ELSE @sat + 1 END 

  SET @calcdate1 = @date1
  SET @calcdate2 = @date2

  -- @date1: assume next day if start was after end of workday
  SET @basedate1 = DATEADD(dd, 0, DATEDIFF(dd, 0, @calcdate1))
  SET @calcdate1 = CASE WHEN DATEDIFF(mi, @basedate1, @calcdate1) > @workday_e
                   THEN @basedate1 + 1
                   ELSE @calcdate1
                   END

  -- @date1: if Saturday or Sunday, make it next Monday
  SET @basedate1 = DATEADD(dd, 0, DATEDIFF(dd, 0, @calcdate1))
  SET @calcdate1 = CASE DATEPART(dw, @basedate1)
                   WHEN @sat THEN @basedate1 + 2
                   WHEN @sun THEN @basedate1 + 1
                   ELSE @calcdate1
                   END

  -- @date1: assume @workday_s as the minimum start time
  SET @basedate1 = DATEADD(dd, 0, DATEDIFF(dd, 0, @calcdate1))
  SET @calcdate1 = CASE WHEN DATEDIFF(mi, @basedate1, @calcdate1) < @workday_s 
                   THEN DATEADD(mi, @workday_s, @basedate1)
                   ELSE @calcdate1
                   END

  -- @date2: assume previous day if end was before start of workday
  SET @basedate2 = DATEADD(dd, 0, DATEDIFF(dd, 0, @calcdate2))
  SET @calcdate2 = CASE WHEN DATEDIFF(mi, @basedate2, @calcdate2) < @workday_s
                   THEN @basedate2 - 1
                   ELSE @calcdate2
                   END

  -- @date2: if Saturday or Sunday, make it previous Friday
  SET @basedate2 = DATEADD(dd, 0, DATEDIFF(dd, 0, @calcdate2))
  SET @calcdate2 = CASE DATEPART(dw, @calcdate2)
                   WHEN @sat THEN @basedate2 - 0.00001
                   WHEN @sun THEN @basedate2 - 1.00001
                   ELSE @date2
                   END

  -- @date2: assume @workday_e as the maximum end time
  SET @basedate2 = DATEADD(dd, 0, DATEDIFF(dd, 0, @calcdate2))
  SET @calcdate2 = CASE WHEN DATEDIFF(mi, @basedate2, @calcdate2) > @workday_e
                   THEN DATEADD(mi, @workday_e, @basedate2)
                   ELSE @calcdate2
                   END

  -- count full work days (subtract Saturdays and Sundays)
  SET @cworkdays = DATEDIFF(dd, @basedate1, @basedate2)
  SET @cweekends = @cworkdays / 7
  SET @cworkdays = @cworkdays - @cweekends * 2

  -- calculate effective duration in minutes
  SET @returnval = @cworkdays * (@workday_e - @workday_s)
                   + @workday_e - DATEDIFF(mi, @basedate1, @calcdate1) 
                   + DATEDIFF(mi, @basedate2, @calcdate2) - @workday_e

  -- return duration as an offset in minutes from date 0
  RETURN DATEADD(mi, @returnval, 0)
END

The function returns a DATETIME value meant as an offset from date 0 (which is "1900-01-01 00:00:00"). So for example a timespan of 8:00 hours would be "1900-01-01 08:00:00" and 25 hours would be "1900-01-02 01:00:00". The function result is the time difference in business hours between two dates. No special handling/support for overtime.

SELECT dbo.udfDateDiffBusinessHours('2003-04-29 15:00:00', '2003-04-30 11:00:00')
--> 1900-01-01 03:30:00.000

SELECT dbo.udfDateDiffBusinessHours('2003-04-30 14:00:00', '2003-05-01 14:00:00')
--> 1900-01-01 07:30:00.000

The function assumes the start of the next available work day (08:30 h) when the @date1 is off-hours, and the end of the previous available work day (16:00 h) when @date2 is off-hours.

"next/previous available" means:

• if @date1 is '2009-02-06 07:00:00' (Fri), it will become '2009-02-06 08:30:00' (Fri)
• if @date1 is '2009-02-06 19:00:00' (Fri), it will become '2009-02-09 08:30:00' (Mon)
• if @date2 is '2009-02-09 07:00:00' (Mon), it will become '2009-02-06 16:00:00' (Fri)
• if @date2 is '2009-02-09 19:00:00' (Mon), it will become '2009-02-09 16:00:00' (Mon)

回答3:

select datediff(hh,@date1,@date2) - 16.5*(datediff(dd,@date1,@date2))

The only catch is that it will give you 3:30 as 3.5 hours but you can fix that easily.

回答4:

Use this code : to find out weekend in between dates

 (
    DATEDIFF(dd, open_date, zassignment_date) + 1 
    - ( (DATEDIFF(dd, open_date, zassignment_date) + 1) 
    -(DATEDIFF(wk, open_date, zassignment_date) * 2) 
    -(CASE WHEN DATENAME(dw,  open_date) = 'Sunday' THEN 1 ELSE 0 END) 
    -(CASE WHEN DATENAME(dw, zassignment_date) = 'Saturday' THEN 1 ELSE 0 END) )) wk_end 

回答5:

Assuming you have a reference-table of the working days (and their hours), then I would use a 3 stage approach (pseudo-sql)

(first preclude the "all in one day" trivial example, since that simplifies the logic)

 -- days that are neither the start nor end (full days)
 SELECT @FullDayHours = SUM(day start to day end)
 FROM   reference-calendar
 WHERE  Start >= midnight-after-start and End <= midnight-before-end

 -- time after the [query start] to the end of the first working day
 SELECT @FirstDayHours = [query start] to day end
 FROM   reference-calandar
 WHERE  start day

 -- time from the start of the last working day to the [query end]
 SELECT @LastDayHours = day start to [query end]
 FROM   reference-calendar
 WHERE  end-day

 IF @FirstDayHours < 0 SET @FirstDayHours = 0 -- starts outside working time
 IF @LastDayHours < 0 SET @LastDayHours  = 0 -- ends outside working time

 PRINT @FirstDayHours  + @FullDayHours + @LastDayHours

Obviously it is a bit hard to do properly without more context...

回答6:

This function will give you the difference in business hours between two given times. This will return the difference in minutes or hours based on the date part parameter.

CREATE FUNCTION [dbo].[fnBusinessHoursDateDiff] (@StartTime SmallDatetime, @EndTime SmallDateTime, @DatePart varchar(2)) RETURNS DECIMAL (10,2)
AS 
BEGIN

DECLARE @Minutes        bigint
    ,   @FinalNumber    Decimal(10,2)

-- // Create Minute By minute table for CTE
-- ===========================================================
;WITH  cteInputHours (StartTime, EndTime, NextTime) AS (
    SELECT  @StartTime  
        ,   @EndTime    
        ,   dateadd(mi, 1, @StartTime)
 ),
 cteBusinessMinutes (TimeOfDay, [isBusHour], NextTime) AS(
    SELECT  StartTime [TimeOfDay]
        ,   case when datepart(dw, StartTime) between 2 and 6 and convert(time,StartTime) between '08:30' and '15:59' then 1 else 0 end [isBusHour]
        ,   dateadd(mi, 1, @StartTime)  [NextTime]
    FROM    cteInputHours
    UNION ALL
    SELECT  dateadd(mi, 1, (a.TimeOfDay)) [TimeOfDay]
        ,   case when datepart(dw, a.TimeOfDay) between 2 and 6 and  convert(time,dateadd(mi, 1, (a.TimeOfDay)) ) between '08:30' and '15:59' then 1 else 0 end [isBusHour]
        ,   dateadd(mi, 2, (a.TimeOfDay)) NextTime
    FROM    cteBusinessMinutes a
    WHERE   dateadd(mi, 1, (a.TimeOfDay)) < @EndTime
) 
SELECT  @Minutes = count(*)
FROM    cteBusinessMinutes
WHERE   isBusHour = 1
OPTION (MAXRECURSION 0);

-- // Final Select
-- ===========================================================
SELECT  @FinalNumber = @Minutes / (case when @DatePart = 'hh' then 60.00 else 1 end)

RETURN @FinalNumber 

END