All geek questions in one place

Extended use of SQL DateDiff?

I need to calculate DateDiff (hours) between two dates, but only during business hours (from 8:30 to 16:00, seven days a week). This result will then be placed in the Reaction_Time column according to the example below.

ID Date Reaction_Time Overdue
1 April 29, 2003 15:00:00                      
1 April 30, 2003 11:00:00 3:30        
April 2, 2003 14:00:00                      
2 05/01/2003 14:00:00 7:30 YES

* Note. I did not check if the dates in the example were holidays.

I am using SQL Server 2005

This will be combined with a large request, but for now all I need to do is start, I will try to figure out how to put it all together on my own. Thanks for the help!

Edit: Hey, thanks everyone for the answers. But due to the obvious complexity of the decision on the SQL side, it was decided that we would do this in Excel, and not where the report would be moved anyway. Sorry to bother you, but I really realized that it will be easier than that. Be that as it may, we simply do not have time.

+3
source share
6 answers
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
+5
source

I would recommend creating a user-defined function that calculates the difference in dates in hours of operation 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 value comes from Overdue, so I left it in my example.

SQL, , -, .

. ( Holidays ) . SELECT/CASE WHEN, .

, .

: - , :

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

DATETIME, 0 ( "1900-01-01 00:00:00"). , , 8:00 "1900-01-01 08:00:00" 25 "1900-01-02 01:00:00". . / .

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

(08:30 ), @date1 , (16:00 ), @date2 .

"next/previous available" :

  • @date1 '2009-02-06 07:00:00' (Fri), '2009-02-06 08:30:00' (Fri)
  • if @date1 is '2009-02-06 19:00:00' (Fri), '2009-02-09 08:30:00' (Mon)
  • @date2 - '2009-02-09 07:00:00' (Mon), '2009-02-06 16:00:00' (Fri)
  • @date2 - '2009-02-09 19:00:00' (Mon), '2009-02-09 16:00:00' (Mon)
+7
select datediff(hh,@date1,@date2) - 16.5*(datediff(dd,@date1,@date2))

, 3:30 3,5 , .

+1

:

 (
    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
+1

, ( ), ( sql)

( " ", )

 -- 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

, ...

0

. .

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
0
source

Source: https://habr.com/ru/post/1703942/

More articles:


All Articles