tiempo - sql server diferencia entre 2 fechas

Calcule el horario comercial entre dos fechas

Question

¿Cómo puedo calcular el horario comercial entre dos fechas? Por ejemplo, tenemos dos fechas; 01/01/2010 15:00 y 01/04/2010 12:00 Y tenemos horario de trabajo de 09:00 a 17:00 de lunes a viernes. ¿Cómo puedo calcular las horas de trabajo con sql?

Answer 1

¿Qué piensas de esta solución?

Sin usar el bucle "While".

create function dbo.WorkingHoursBetweenDates ( @StartDate datetime, @EndDate datetime, @StartTime time, @EndTime time )
returns decimal ( 10, 2 )
as
begin

  return
    case
      when @EndTime < @StartTime or @EndDate < @StartDate then
        0
      else
        round
        ( ( dbo.WorkingDaysBetweenDates(@StartDate, @EndDate) -
            ( dbo.WorkingDaysBetweenDates(@StartDate, @StartDate) *
              case
                when cast ( @StartDate as time ) > @EndTime then
                  1
                else
                  datediff
                  ( mi,
                    @StartTime
                    , case
                        when @StartTime > cast ( @StartDate as time ) then
                          @StartTime
                        else
                          cast ( @StartDate as time )
                      end
                  ) /
                  ( datediff ( mi, @StartTime, @EndTime ) + 0.0 )
              end
            ) -
            ( dbo.WorkingDaysBetweenDates(@EndDate, @EndDate) *
              case
                when cast ( @EndDate as time ) < @StartTime then
                  1
                else
                  datediff
                  ( mi,
                    case
                      when @EndTime < cast ( @EndDate as time ) then
                        @EndTime
                      else
                        cast ( @EndDate as time )
                    end,
                    @EndTime
                  ) /
                  ( datediff ( mi, @StartTime, @EndTime ) + 0.0 )
              end
            )
          ) *
          ( datediff ( mi, @StartTime, @EndTime ) / 60.0 ), 2
        )
    end

end
------

create function dbo.WorkingDaysBetweenDates ( @StartDate date, @EndDate date )
returns int
as
begin

  return 
    ( datediff(dd, @StartDate, @EndDate) + 1 ) -
    ( datediff(wk, @StartDate, @EndDate) * 2 ) -
    ( case when datename(dw, @StartDate) = ''Sunday'' then 1 else 0 end ) -
    ( case when datename(dw, @EndDate) = ''Saturday'' then 1 else 0 end ) -
    ( select
        count ( 1 )
      from
        dbo.Tb_Holidays
      where
        HDate between @StartDate and @EndDate
        and datename(dw, HDate) not in ( ''Sunday'', ''Saturday'' )
    )

end

Answer 2

Aquí hay una solución alternativa, sin el uso de una función. Tenga en cuenta que esto depende de la existencia de una tabla de números , poblada con al menos el número máximo de días que pueden llevar las tareas que realiza el seguimiento.

Esto no toma en cuenta los días festivos. Si no trabaja los fines de semana, establecer las horas de apertura y cierre a medianoche en la variable de tabla @OpeningHours debería hacer el trabajo.

Probé esto con 8500 filas de datos del "mundo real" y descubrí que funcionaba.

DECLARE @OpeningHours TABLE ([DayOfWeek] INTEGER, OpeningTime TIME(0), ClosingTime TIME(0));

INSERT
    @OpeningHours ([DayOfWeek], OpeningTime, ClosingTime)
VALUES
    (1, ''10:00'', ''16:00'') -- Sun
    , (2, ''06:30'', ''23:00'') -- Mon
    , (3, ''06:30'', ''23:00'') -- Tue
    , (4, ''06:30'', ''23:00'') -- Wed
    , (5, ''06:30'', ''23:00'') -- Thu
    , (6, ''06:30'', ''23:00'') -- Fri
    , (7, ''08:00'', ''20:00''); -- Sat

DECLARE @Tasks TABLE ([Description] VARCHAR(50), CreatedDateTime DATETIME, CompletedDateTime DATETIME);

INSERT
    @Tasks ([Description], CreatedDateTime, CompletedDateTime)
VALUES
    (''Make tea'', ''20170404 10:00'', ''20170404 10:12'')
    , (''Make coffee'', ''20170404 23:35'', ''20170405 06:32'')
    , (''Write complex SQL query'', ''20170406 00:00'', ''20170406 23:32'')
    , (''Rewrite complex SQL query'', ''20170406 23:50'', ''20170410 10:50'');

SELECT
    WorkingMinutesToRespond =
        SUM(CASE WHEN CAST(Tasks.CreatedDateTime AS DATE) = CAST(Tasks.CompletedDateTime AS DATE) THEN
        CASE WHEN CAST(Tasks.CreatedDateTime AS TIME) < OpeningHours.OpeningTime THEN
            -- Task created before opening time
            DATEDIFF(MINUTE, OpeningHours.OpeningTime, CAST(Tasks.CompletedDateTime AS TIME))
        ELSE
            DATEDIFF(MINUTE, Tasks.CreatedDateTime, Tasks.CompletedDateTime)
        END
    ELSE
        CASE WHEN Tasks.CoveredDate = CAST(Tasks.CreatedDateTime AS DATE) THEN 
            -- This is the day the task was created
            CASE WHEN CAST(Tasks.CreatedDateTime AS TIME(0)) > OpeningHours.ClosingTime THEN
                0 -- after working hours
            ELSE
                -- during or before working hours
                CASE WHEN CAST(Tasks.CreatedDateTime AS TIME(0)) < OpeningHours.OpeningTime THEN
                    -- before opening time; take the whole day into account
                    DATEDIFF(MINUTE, OpeningHours.OpeningTime, OpeningHours.ClosingTime)
                ELSE
                    -- during opening hours; take part of the day into account
                    DATEDIFF(MINUTE, CAST(Tasks.CreatedDateTime AS TIME), OpeningHours.ClosingTime)
                END
            END
        ELSE
            -- This is the day the task was completed
            CASE WHEN Tasks.CoveredDate = CAST(Tasks.CompletedDateTime AS DATE) THEN 
                CASE WHEN CAST(Tasks.CompletedDateTime AS TIME(0)) < OpeningHours.OpeningTime THEN
                    0 -- before working hours (unlikely to occur)
                ELSE
                    -- during or after working hours
                    CASE WHEN CAST(Tasks.CompletedDateTime AS TIME(0)) > OpeningHours.ClosingTime THEN
                        -- after closing time (also unlikely); take the whole day into account
                        DATEDIFF(MINUTE, OpeningHours.OpeningTime, OpeningHours.ClosingTime)
                    ELSE
                        -- during opening hours; take part of the day into account
                        DATEDIFF(MINUTE, OpeningHours.OpeningTime, CAST(Tasks.CompletedDateTime AS TIME(0)))
                    END
                END
        ELSE
            DATEDIFF(MINUTE, OpeningHours.OpeningTime, OpeningHours.ClosingTime)
        END 
        END
    END)
    , Tasks.Description
    , Tasks.CreatedDateTime
    , Tasks.CompletedDateTime
FROM
    (
        SELECT
        Tasks.Description
        , Tasks.CreatedDateTime
        , Tasks.CompletedDateTime
        , CoveredDate = CAST(DATEADD(DAY, Numbers.Number, Tasks.CreatedDateTime) AS DATE)
    FROM
        @Tasks Tasks
        INNER JOIN (SELECT * FROM Numbers WHERE Number >= 0) Numbers ON DATEDIFF(DAY, Tasks.CreatedDateTime, Tasks.CompletedDateTime) >= Numbers.Number
) Tasks
INNER JOIN @OpeningHours OpeningHours ON DATEPART(WEEKDAY, Tasks.CoveredDate) = OpeningHours.[DayOfWeek]
GROUP BY
    Tasks.Description
    , Tasks.CreatedDateTime
    , Tasks.CompletedDateTime
ORDER BY
    Tasks.CompletedDateTime;

Answer 3

Aquí hay una versión en línea Start / EndDateTime como 2015-03-16 09: 52: 24.000 Start / EndTime (businesshours) como 07:00:00 Es abultada pero funciona en su declaración seleccionada

Lo publicaré en la versión de Function también.

Case when  <StartDate>= <EndDate> then 0
    When Convert(date,<StartDate>) = Convert(date,<EndDate>) Then 
         IIF( DATEPART(Dw,<StartDate>)  in(1,7)
                  or Convert(time,<StartDate>) > Convert(time,<EndTime>)
                  or Convert(time,<EndDate>) < Convert(time,<StartTime>),0, 
        DateDiff(S,IIF(Convert(time,<StartDate>) < Convert(time,<StartTime>),Convert(time,<StartTime>),Convert(time,<StartDate>))
                ,IIF(Convert(time,<EndDate>) > Convert(time,<EndTime>), Convert(time,<EndTime>), Convert(time,<EndDate>))))
    when  Convert(date,<StartDate>) <> Convert(date,<EndDate>) then 
        IIF(DATEPART(Dw,<StartDate>) in(1,7) or Convert(time,<StartDate>) >  Convert(time,<EndTime>),0 ,DateDiff(S,IIF(Convert(time,<StartDate>) < Convert(time,<StartTime>),Convert(time,<StartTime>),Convert(time,<StartDate>)), Convert(time,<EndTime>)))
        + IIF(DATEPART(Dw,<EndDate>) in(1,7) or  Convert(time,<EndDate>) <  Convert(time,<StartTime>),0,DateDiff(S,Convert(time,<StartTime>),IIF(Convert(time,<EndDate>) > Convert(time,<EndTime>), Convert(time,<EndTime>), Convert(time,<EndDate>))))
    else -333
    end --as pday

+IIF(DatePart(wEEk,<StartDate>)  = DatePart(wEEk,<EndDate>) 
,0, (DateDiff(wk,dateadd(d,-datepart(dw,<StartDate>),dateadd(ww,1,<StartDate>)),DATEADD(wk, DATEDIFF(wk, 6, <EndDate>), 6)-1) * 5)) * Datediff(S, Convert(time,<StartTime>),Convert(time,<EndTime>)) --Fullweek_days

+Case When Convert(date,<StartDate>) = Convert(date,<EndDate>) then 0
      When DatePart(wEEk,<StartDate>)  <> DatePart(wEEk,<EndDate>) then
                        IIF( datepart(dw,<StartDate>) = 7,0,DateDIFF(DAY,<StartDate>+1,dateadd(d,-datepart(dw,<StartDate>),dateadd(ww,1,<StartDate>))))  -- beginFulldays
                        +IIF( datepart(dw,<EndDate>) = 1,0,DateDIFF(DAY,DATEADD(wk, DATEDIFF(wk, 6, <EndDate>), 6),<EndDate> -1))  --Endfulldays
      When DatePart(wEEk,<StartDate>)  = DatePart(wEEk,<EndDate>) then
            DateDiff(DAY,<StartDate>+1,<EndDate> ) 
    ELSE -333 END * Datediff(S, Convert(time,<StartTime>),Convert(time,<EndTime>))    

Aquí está la versión de la función:

CREATE FUNCTION [dbo].[rsf_BusinessTime]
(
@startDateTime Datetime,
@endDateTime Datetime ,
@StartTime VarChar(12),
@EndTime VarChar(12) )
RETURNS BIGINT
As
BEGIN
Declare @totalSeconds BigInt,
    @SecondsInDay int,
    @dayStart Time = Convert(time,@StartTime),
    @dayEnd Time =Convert(time,@EndTime),
    @SatAfterStart Datetime = dateadd(d,-datepart(dw,@startDateTime),dateadd(ww,1,@startDateTime)), 
    @Sunbeforend Datetime = DATEADD(wk, DATEDIFF(wk, 6, @endDateTime), 6) 

-- This function calculates the seconds between the start and end dates provided for business hours. 
-- It only returns the time between the @start and @end time (hour of day) of the work week. 
-- Weekend days are removed.
-- Holidays are not considered.  

Set @SecondsInDay = Datediff(S, @dayStart,@dayEnd) 


Set @totalSeconds = 
 --first/last/sameday
    Case when  @startDateTime= @endDateTime then 0
    When Convert(date,@startDateTime) = Convert(date,@endDateTime) Then 
         IIF( DATEPART(Dw,@startDateTime)  in(1,7)
                  or Convert(time,@startDateTime) > @dayEnd
                  or Convert(time,@endDateTime) < @dayStart,0, 
        DateDiff(S,IIF(Convert(time,@startDateTime) < @dayStart,@dayStart,Convert(time,@startDateTime))
                ,IIF(Convert(time,@endDateTime) > @dayEnd, @dayEnd, Convert(time,@endDateTime))))
    when  Convert(date,@startDateTime) <> Convert(date,@endDateTime) then 
        IIF(DATEPART(Dw,@startDateTime) in(1,7) or Convert(time,@startDateTime) >  @dayEnd,0 ,DateDiff(S,IIF(Convert(time,@startDateTime) < @dayStart,@dayStart,Convert(time,@startDateTime)), @dayEnd))
        + IIF(DATEPART(Dw,@endDateTime) in(1,7) or  Convert(time,@endDateTime) <  @dayStart,0,DateDiff(S,@dayStart,IIF(Convert(time,@endDateTime) > @dayEnd, @dayEnd, Convert(time,@endDateTime))))
    else -333
    end --as pday

+IIF(DatePart(wEEk,@startDateTime)  = DatePart(wEEk,@endDateTime)   
,0, (DateDiff(wk,@SatAfterStart,@Sunbeforend-1) * 5)) * @SecondsInDay --Fullweek_days

+Case When Convert(date,@startDateTime) = Convert(date,@endDateTime) then 0
      When DatePart(wEEk,@startDateTime)  <> DatePart(wEEk,@endDateTime) then
                        IIF( datepart(dw,@startDateTime) = 7,0,DateDIFF(DAY,@startDateTime+1,@SatAfterStart))  -- beginFulldays
                        +IIF( datepart(dw,@endDateTime) = 1,0,DateDIFF(DAY,@Sunbeforend,@endDateTime -1))  --Endfulldays
      When DatePart(wEEk,@startDateTime)  = DatePart(wEEk,@endDateTime) then
            DateDiff(DAY,@startDateTime+1,@endDateTime ) 
    ELSE -333 END * @SecondsInDay


Return @totalSeconds
END 

Answer 4

De hecho, he hecho esto antes, teniendo en cuenta todas las variables (fines de semana, vacaciones, etc.) para las horas de oficina es muy difícil, creo que esta tarea se realiza mejor fuera de SQL

Answer 5

El primer paso sería calcular los días hábiles, como se muestra en el siguiente script:

DECLARE @TotalWorkDays INT, @TotalTimeDiff DECIMAL(18, 2), @DateFrom DATETIME, @DateTo DATETIME;
SET @DateFrom = ''2017-06-05 11:19:11.287'';
SET @DateTo = ''2017-06-07 09:53:14.750'';

SET @TotalWorkDays = DATEDIFF(DAY, @DateFrom, @DateTo)
    -(DATEDIFF(WEEK, @DateFrom, @DateTo) * 2)
   -CASE
                                    WHEN DATENAME(WEEKDAY, @DateFrom) = ''Sunday''
                                    THEN 1
                                    ELSE 0
                                END+CASE
                                        WHEN DATENAME(WEEKDAY, @DateTo) = ''Saturday''
                                        THEN 1
                                        ELSE 0
                                    END;

El segundo paso implica obtener una diferencia en segundos entre las dos fechas y convertir esa diferencia en horas dividiendo por 3600.0 como se muestra en este siguiente script:

SET @TotalTimeDiff =
(
    SELECT DATEDIFF(SECOND,
                   (
                       SELECT CONVERT(TIME, @DateFrom)
                   ),
                   (
                       SELECT CONVERT(TIME, @DateTo)
                   )) / 3600.0
);

La última parte implica multiplicar el resultado del primer paso anterior por 24 (número total de horas en un día) y luego agregarlo al resultado del segundo paso:

SELECT(@TotalWorkDays * 24.00) + @TotalTimeDiff;

Finalmente, la secuencia de comandos completa que se puede utilizar para crear una función definida por el usuario para calcular las horas de trabajo se muestra a continuación:

CREATE FUNCTION [dbo].[fn_GetTotalWorkingHours]
(
    @DateFrom Datetime,
    @DateTo Datetime
)
RETURNS DECIMAL(18,2)
AS
BEGIN

DECLARE @TotalWorkDays INT, @TotalTimeDiff DECIMAL(18, 2)

SET @TotalWorkDays = DATEDIFF(DAY, @DateFrom, @DateTo)
    -(DATEDIFF(WEEK, @DateFrom, @DateTo) * 2)
   -CASE
                                    WHEN DATENAME(WEEKDAY, @DateFrom) = ''Sunday''
                                    THEN 1
                                    ELSE 0
                                END+CASE
                                        WHEN DATENAME(WEEKDAY, @DateTo) = ''Saturday''
                                        THEN 1
                                        ELSE 0
                                    END;
SET @TotalTimeDiff =
(
    SELECT DATEDIFF(SECOND,
                   (
                       SELECT CONVERT(TIME, @DateFrom)
                   ),
                   (
                       SELECT CONVERT(TIME, @DateTo)
                   )) / 3600.0
);

RETURN  (SELECT(@TotalWorkDays * 24.00) + @TotalTimeDiff)

END
GO

El método completo se describe en este artículo: https://www.sqlshack.com/how-to-calculate-work-days-and-hours-in-sql-server/

Answer 6

La pregunta dice que los días festivos no deben ser considerados, por lo que esta respuesta hace precisamente eso: calcula las horas de trabajo teniendo en cuenta los fines de semana, pero ignora los posibles días festivos.

También asume que las fechas / horas de inicio y finalización son durante el horario comercial.

Con esta suposición, al código no le importa cuándo comienza o termina el día hábil, solo le importa el número total de horas de trabajo por día. En su ejemplo, hay 8 horas hábiles entre las 09:00 y las 17:00. No tiene que ser un número entero. La siguiente fórmula lo calcula con precisión de un minuto, pero es trivial hacerlo de un segundo o de cualquier otra precisión.

Si necesita tomar en cuenta los días festivos, necesitará tener una tabla separada que enumere las fechas de los días festivos, que pueden diferir de un año a otro y de un estado a otro o de un país a otro. La fórmula principal puede seguir siendo la misma, pero deberá restar de sus horas de resultados los días festivos que caen dentro del rango de fechas especificado.

La formula

SELECT
    DATEDIFF(minute, StartDT, EndDT) / 60.0
    - DATEDIFF(day,  StartDT, EndDT) * 16
    - DATEDIFF(week, StartDT, EndDT) * 16 AS BusinessHours
FROM T

Para comprender cómo funciona, creamos una tabla con algunos datos de muestra que cubren varios casos:

DECLARE @T TABLE (StartDT datetime2(0), EndDT datetime2(0));

INSERT INTO @T VALUES
(''2012-03-05 09:00:00'', ''2012-03-05 15:00:00''), -- simple part of the same day
(''2012-03-05 10:00:00'', ''2012-03-06 10:00:00''), -- full day across the midnight
(''2012-03-05 11:00:00'', ''2012-03-06 10:00:00''), -- less than a day across the midnight
(''2012-03-05 10:00:00'', ''2012-03-06 15:00:00''), -- more than a day across the midnight
(''2012-03-09 16:00:00'', ''2012-03-12 10:00:00''), -- over the weekend, less than 7 days
(''2012-03-06 16:00:00'', ''2012-03-15 10:00:00''), -- over the weekend, more than 7 days
(''2012-03-09 16:00:00'', ''2012-03-19 10:00:00''); -- over two weekends

La consulta

SELECT
    StartDT, 
    EndDT,
    DATEDIFF(minute, StartDT, EndDT) / 60.0
    - DATEDIFF(day,  StartDT, EndDT) * 16
    - DATEDIFF(week, StartDT, EndDT) * 16 AS BusinessHours
FROM @T;

produce el siguiente resultado:

+---------------------+---------------------+---------------+
|       StartDT       |        EndDT        | BusinessHours |
+---------------------+---------------------+---------------+
| 2012-03-05 09:00:00 | 2012-03-05 15:00:00 |  6.000000     |
| 2012-03-05 10:00:00 | 2012-03-06 10:00:00 |  8.000000     |
| 2012-03-05 11:00:00 | 2012-03-06 10:00:00 |  7.000000     |
| 2012-03-05 10:00:00 | 2012-03-06 15:00:00 | 13.000000     |
| 2012-03-09 16:00:00 | 2012-03-12 10:00:00 |  2.000000     |
| 2012-03-06 16:00:00 | 2012-03-15 10:00:00 | 50.000000     |
| 2012-03-09 16:00:00 | 2012-03-19 10:00:00 | 42.000000     |
+---------------------+---------------------+---------------+

Funciona, porque en SQL Server DATEDIFF devuelve el recuento de los límites de fecha y hora especificados cruzados entre la fecha de inicio y la fecha de finalización especificadas.

Cada día tiene 8 horas hábiles. Calculo el número total de horas entre dos fechas, luego restamos el número de medias noches multiplicado por 16 horas no comerciales por día, luego restamos el número de fines de semana multiplicado por 16 (8 + 8 horas hábiles para Sat + Sun).

Answer 7

La respuesta de Baran fue arreglada y modificada para SQL 2005

SQL 2008 y superior:

-- =============================================
-- Author:      Baran Kaynak (modified by Kodak 2012-04-18)
-- Create date: 14.03.2011
-- Description: 09:30 ile 17:30 arasındaki iş saatlerini hafta sonlarını almayarak toplar.
-- =============================================
CREATE FUNCTION [dbo].[WorkTime] 
(
    @StartDate DATETIME,
    @FinishDate DATETIME
)
RETURNS BIGINT
AS
BEGIN
    DECLARE @Temp BIGINT
    SET @Temp=0

    DECLARE @FirstDay DATE
    SET @FirstDay = CONVERT(DATE, @StartDate, 112)

    DECLARE @LastDay DATE
    SET @LastDay = CONVERT(DATE, @FinishDate, 112)

    DECLARE @StartTime TIME
    SET @StartTime = CONVERT(TIME, @StartDate)

    DECLARE @FinishTime TIME
    SET @FinishTime = CONVERT(TIME, @FinishDate)

    DECLARE @WorkStart TIME
    SET @WorkStart = ''09:00''

    DECLARE @WorkFinish TIME
    SET @WorkFinish = ''17:00''

    DECLARE @DailyWorkTime BIGINT
    SET @DailyWorkTime = DATEDIFF(MINUTE, @WorkStart, @WorkFinish)

    IF (@StartTime<@WorkStart)
    BEGIN
        SET @StartTime = @WorkStart
    END
    IF (@FinishTime>@WorkFinish)
    BEGIN
        SET @FinishTime=@WorkFinish
    END
    IF (@FinishTime<@WorkStart)
    BEGIN
        SET @FinishTime=@WorkStart
    END
    IF (@StartTime>@WorkFinish)
    BEGIN
        SET @StartTime = @WorkFinish
    END

    DECLARE @CurrentDate DATE
    SET @CurrentDate = @FirstDay
    DECLARE @LastDate DATE
    SET @LastDate = @LastDay

    WHILE(@CurrentDate<=@LastDate)
    BEGIN       
        IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
        BEGIN
            IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
            BEGIN
                SET @Temp = @Temp + @DailyWorkTime
            END
            --IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes
            ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
            BEGIN
                SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
            END

            ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
            BEGIN
                SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
            END
            --IF it starts and finishes in the same date
            ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
            BEGIN
                SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)
            END
        END
        SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
    END

    -- Return the result of the function
    IF @Temp<0
    BEGIN
        SET @Temp=0
    END
    RETURN @Temp

END

SQL 2005 y abajo:

-- =============================================
-- Author:      Baran Kaynak (modified by Kodak 2012-04-18)
-- Create date: 14.03.2011
-- Description: 09:30 ile 17:30 arasındaki iş saatlerini hafta sonlarını almayarak toplar.
-- =============================================
CREATE FUNCTION [dbo].[WorkTime] 
(
    @StartDate DATETIME,
    @FinishDate DATETIME
)
RETURNS BIGINT
AS
BEGIN
    DECLARE @Temp BIGINT
    SET @Temp=0

    DECLARE @FirstDay DATETIME
    SET @FirstDay = DATEADD(dd, 0, DATEDIFF(dd, 0, @StartDate))

    DECLARE @LastDay DATETIME
    SET @LastDay = DATEADD(dd, 0, DATEDIFF(dd, 0, @FinishDate))

    DECLARE @StartTime DATETIME
    SET @StartTime = @StartDate - DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0)

    DECLARE @FinishTime DATETIME
    SET @FinishTime = @FinishDate - DATEADD(dd, DATEDIFF(dd, 0, @FinishDate), 0)

    DECLARE @WorkStart DATETIME
    SET @WorkStart = CONVERT(DATETIME, ''09:00'', 8)

    DECLARE @WorkFinish DATETIME
    SET @WorkFinish = CONVERT(DATETIME, ''17:00'', 8)

    DECLARE @DailyWorkTime BIGINT
    SET @DailyWorkTime = DATEDIFF(MINUTE, @WorkStart, @WorkFinish)

    IF (@StartTime<@WorkStart)
    BEGIN
        SET @StartTime = @WorkStart
    END
    IF (@FinishTime>@WorkFinish)
    BEGIN
        SET @FinishTime=@WorkFinish
    END
    IF (@FinishTime<@WorkStart)
    BEGIN
        SET @FinishTime=@WorkStart
    END
    IF (@StartTime>@WorkFinish)
    BEGIN
        SET @StartTime = @WorkFinish
    END

    DECLARE @CurrentDate DATETIME
    SET @CurrentDate = @FirstDay
    DECLARE @LastDate DATETIME
    SET @LastDate = @LastDay

    WHILE(@CurrentDate<=@LastDate)
    BEGIN       
        IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
        BEGIN
            IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
            BEGIN
                SET @Temp = @Temp + @DailyWorkTime
            END
            --IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes
            ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
            BEGIN
                SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
            END

            ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
            BEGIN
                SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
            END
            --IF it starts and finishes in the same date
            ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
            BEGIN
                SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)
            END
        END
        SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
    END

    -- Return the result of the function
    IF @Temp<0
    BEGIN
        SET @Temp=0
    END
    RETURN @Temp

END

Answer 8

La versión 2008 con el ciclo while es demasiado lenta para nuestro flujo de trabajo. La versión de ciclo anterior toma diez minutos en nuestro servidor SQL si ejecutamos el cálculo con varios años entre StartDate y FinishDate para un millón de filas. Mi versión completa la misma tarea en 1 minuto.

A continuación se muestra la función sin bucle (probado en 2008) para su referencia:

CREATE FUNCTION [dbo].[WorkTime] (
@EventStartDateTime DATETIME,
@EventEndDateTime DATETIME,
@BusinessStartDateTime DATETIME,
@BusinessEndDateTime DATETIME,
@IncludeWeekend BIT)
RETURNS BIGINT
AS
BEGIN

 -- Purpose: Function that calculates seconds of business hours between an event.
-- Allows the calculation to work with (@IncludeWeekend = 1) or without weekends (@IncludeWeekend = 0), that is working days only or seven days a week. 
-- Allows the calculation to work for 24x7 business hours

-- Author Mathias Florin
-- Create Date: 2016/11/05

-- Comments:    Event StartDateTime (ESDT), Event EndDateTime(EETD)
--              Business StartDateTime (BSDT), Business EndDateTime (BEDT)
--              Event StartHour (ESH), Event EndHour(EEH)
--              Business StartHour (BSH), Business EndHour (BEH)   

--              For 24x7 business hours pass these parameters to the function:
--              BusinessStartDateTime (1900-01-01 00:00:00) and BusinessEndDateTime (1900-01-02 00:00:00)

-- Check Statement: SET DATEFIRST 7; SELECT dbo.WorkTime(''2016-11-05 08:00:00'',''2016-11-05 18:00:00'',''1900-01-01 09:00:00'',''1900-01-01 17:00:00'',1)

-- Different conditions of calculation which are referenced in the SQL code below:

-- Same day incl. weekend - Business not working 24x7:
--                                              StartDateTime & EndDateTime on same day 
-- 1)  ESH  BSH <-- 0 day --> BEH EEH       ->  DIFF(BSH,BEH)       
-- 2)  BSH  ESH <-- 0 day --> BEH EEH       ->  DIFF(ESH,BEH)       
-- 3)  ESH  BSH <-- 0 day --> EEH BEH       ->  DIFF(BSH,EEH)       
-- 4)  BSH  ESH <-- 0 day --> EEH BEH       ->  DIFF(ESH,EEH)       

-- Different day incl. weekend - Business not working 24x7:                     
--                                              ESDT            Days in between                     EETD
-- 5)  ESH  BSH <-- 1..n day --> BEH EEH    ->  DIFF(BSH, BEH)  (Daydiff(all days) -1) * (BSH,BEH)  DIFF(BSH,BEH)
-- 6)  BSH  ESH <-- 1..n day --> BEH EEH    ->  DIFF(ESH,BEH)   (Daydiff(all days) -1) * (BSH,BEH)  DIFF(BSH,BEH)
-- 7)  ESH  BSH <-- 1..n day --> EEH BEH    ->  DIFF(BSH, BEH)  (Daydiff(all days) -1) * (BSH,BEH)  DIFF(BSH,EEH)
-- 8)  BSH  ESH <-- 1..n day --> EEH BEH    ->  DIFF(ESH,BEH)   (Daydiff(all days) -1) * (BSH,BEH)  DIFF(BSH,EEH)

-- Same day excl. weekend - Business not working 24x7:                      
--                                              ESDT & EETD on same day
-- 9)  ESH  BSH <-- 0 day --> BEH EEH       ->  Workday THEN DIFF(BSH,BEH) ELSE 0 END       
-- 10) BSH  ESH <-- 0 day --> BEH EEH       ->  Workday THEN DIFF(ESH,BEH) ELSE 0 END       
-- 11) ESH  BSH <-- 0 day --> EEH BEH       ->  Workday THEN DIFF(BSH,EEH) ELSE 0 END       
-- 12) BSH  ESH <-- 0 day --> EEH BEH       ->  Workday THEN DIFF(ESH,EEH) ELSE 0 END       
--                          
-- Different day excl. weekend - Business not working 24x7:                     
--                                              ESDT                                    |   Workdays in between                     |   EETD
-- 13) ESH  BSH <-- 1..n day --> BEH EEH    ->  (Workday THEN DIFF(BSH, BEH) ELSE 0)    +   ((Daydiff(workdays) -1)  * (BSH,BEH))   +   (Workday THEN DIFF(BSH,BEH) ELSE 0)
-- 14) BSH  ESH <-- 1..n day --> BEH EEH    ->  (Workday THEN DIFF(ESH,BEH) ELSE 0)     +   ((Daydiff(workdays) -1)  * (BSH,BEH))   +   (Workday THEN DIFF(BSH,BEH) ELSE 0)
-- 15) ESH  BSH <-- 1..n day --> EEH BEH    ->  (Workday THEN DIFF(BSH, BEH) ELSE 0)    +   ((Daydiff(workdays) -1)  * (BSH,BEH))   +   (Workday THEN DIFF(BSH,EEH) ELSE 0)
-- 16) BSH  ESH <-- 1..n day --> EEH BEH    ->  (Workday THEN DIFF(ESH,BEH) ELSE 0)     +   ((Daydiff(workdays) -1)  * (BSH,BEH))   +   (Workday THEN DIFF(BSH,EEH) ELSE 0)

-- Business working 24x7
-- 17)  Date difference in seconds between EventStartDateTime and EventEndDateTime

DECLARE @EventStartDateTimeHour TIME; SET @EventStartDateTimeHour = CAST(@EventStartDateTime AS TIME);
DECLARE @EventEndDateTimeHour TIME; SET @EventEndDateTimeHour = CAST(@EventEndDateTime AS TIME);
DECLARE @BusinessStartDateTimeHour TIME; SET @BusinessStartDateTimeHour = CAST(@BusinessStartDateTime AS TIME);
DECLARE @BusinessEndDateTimeHour TIME; SET @BusinessEndDateTimeHour = CAST(@BusinessEndDateTime AS TIME);
DECLARE @EventStartDateTimeIsWorkingDay BIT = CASE
    WHEN DATENAME(DW, @EventStartDateTime) IN (''Saturday'', ''Sunday'') THEN 0
    ELSE 1
END
DECLARE @EventEndDateTimeIsWorkingDay BIT = CASE
    WHEN DATENAME(DW, @EventEndDateTime) IN (''Saturday'', ''Sunday'') THEN 0
    ELSE 1
END
DECLARE @NextDayEventStartDateTime DATETIME = DATEADD(DAY, 1, @EventStartDateTime);
DECLARE @PreviousDayEventStartDateTime DATETIME = DATEADD(DAY, -1, @EventEndDateTime);
DECLARE @WorkDaysBetweenEventStartDateTimeAndEventEndDateTime INT = (SELECT
        (DATEDIFF(dd, @NextDayEventStartDateTime, @PreviousDayEventStartDateTime) + 1)
        - ((DATEDIFF(wk, @NextDayEventStartDateTime, @PreviousDayEventStartDateTime)) * 2)
        - (CASE
            WHEN DATENAME(dw, @NextDayEventStartDateTime) = ''Sunday'' THEN 1
            ELSE 0
        END)
        - (CASE
            WHEN DATENAME(dw, @PreviousDayEventStartDateTime) = ''Saturday'' THEN 1
            ELSE 0
        END))


DECLARE @Temp BIGINT
SELECT @Temp = MAX(Second)
FROM (SELECT
        ''Start'' AS Grouping
    ,   0 AS Second
    UNION
    SELECT
        ''Condition 1'' AS Grouping
    ,   DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour)
    WHERE DATEDIFF(DAY, @EventStartDateTime, @EventEndDateTime) = 0
    AND @IncludeWeekend = 1
    AND DATEDIFF(SECOND, @EventStartDateTimeHour, @BusinessStartDateTimeHour) > 0
    AND DATEDIFF(SECOND, @BusinessEndDateTimeHour, @EventEndDateTimeHour) >= 0
    AND DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour) > 0
    AND DATEDIFF(DAY, @BusinessStartDateTime, @BusinessEndDateTime) = 0
    UNION
    SELECT
        ''Condition 2'' AS Grouping
    ,   DATEDIFF(SECOND, @EventStartDateTimeHour, @BusinessEndDateTimeHour)
    WHERE DATEDIFF(DAY, @EventStartDateTime, @EventEndDateTime) = 0
    AND @IncludeWeekend = 1
    AND DATEDIFF(SECOND, @EventStartDateTimeHour, @BusinessStartDateTimeHour) <= 0
    AND DATEDIFF(SECOND, @BusinessEndDateTimeHour, @EventEndDateTimeHour) >= 0
    AND DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour) > 0
    AND DATEDIFF(DAY, @BusinessStartDateTime, @BusinessEndDateTime) = 0
    UNION
    SELECT
        ''Condition 3'' AS Grouping
    ,   DATEDIFF(SECOND, @BusinessStartDateTimeHour, @EventEndDateTimeHour)
    WHERE DATEDIFF(DAY, @EventStartDateTime, @EventEndDateTime) = 0
    AND @IncludeWeekend = 1
    AND DATEDIFF(SECOND, @EventStartDateTimeHour, @BusinessStartDateTimeHour) > 0
    AND DATEDIFF(SECOND, @BusinessEndDateTimeHour, @EventEndDateTimeHour) < 0
    AND DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour) > 0
    AND DATEDIFF(DAY, @BusinessStartDateTime, @BusinessEndDateTime) = 0
    UNION
    SELECT
        ''Condition 4'' AS Grouping
    ,   DATEDIFF(SECOND, @EventStartDateTimeHour, @EventEndDateTimeHour)
    WHERE DATEDIFF(DAY, @EventStartDateTime, @EventEndDateTime) = 0
    AND @IncludeWeekend = 1
    AND DATEDIFF(SECOND, @EventStartDateTimeHour, @BusinessStartDateTimeHour) <= 0
    AND DATEDIFF(SECOND, @BusinessEndDateTimeHour, @EventEndDateTimeHour) < 0
    AND DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour) > 0
    AND DATEDIFF(DAY, @BusinessStartDateTime, @BusinessEndDateTime) = 0
    UNION
    SELECT
        ''Condition 5'' AS Grouping
    ,   DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour) + ((DATEDIFF(DAY, @EventStartDateTime, @EventEndDateTime) - 1) * DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour)) + DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour)
    WHERE DATEDIFF(DAY, @EventStartDateTime, @EventEndDateTime) > 0
    AND @IncludeWeekend = 1
    AND DATEDIFF(SECOND, @EventStartDateTimeHour, @BusinessStartDateTimeHour) > 0
    AND DATEDIFF(SECOND, @BusinessEndDateTimeHour, @EventEndDateTimeHour) >= 0
    AND DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour) > 0
    AND DATEDIFF(DAY, @BusinessStartDateTime, @BusinessEndDateTime) = 0
    UNION
    SELECT
        ''Condition 6'' AS Grouping
    ,   DATEDIFF(SECOND, @EventStartDateTimeHour, @BusinessEndDateTimeHour) + ((DATEDIFF(DAY, @EventStartDateTime, @EventEndDateTime) - 1) * DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour)) + DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour)
    WHERE DATEDIFF(DAY, @EventStartDateTime, @EventEndDateTime) > 0
    AND @IncludeWeekend = 1
    AND DATEDIFF(SECOND, @EventStartDateTimeHour, @BusinessStartDateTimeHour) <= 0
    AND DATEDIFF(SECOND, @BusinessEndDateTimeHour, @EventEndDateTimeHour) >= 0
    AND DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour) > 0
    AND DATEDIFF(DAY, @BusinessStartDateTime, @BusinessEndDateTime) = 0
    UNION
    SELECT
        ''Condition 7'' AS Grouping
    ,   DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour) + ((DATEDIFF(DAY, @EventStartDateTime, @EventEndDateTime) - 1) * DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour)) + DATEDIFF(SECOND, @BusinessStartDateTimeHour, @EventEndDateTimeHour)
    WHERE DATEDIFF(DAY, @EventStartDateTime, @EventEndDateTime) > 0
    AND @IncludeWeekend = 1
    AND DATEDIFF(SECOND, @EventStartDateTimeHour, @BusinessStartDateTimeHour) > 0
    AND DATEDIFF(SECOND, @BusinessEndDateTimeHour, @EventEndDateTimeHour) < 0
    AND DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour) > 0
    AND DATEDIFF(DAY, @BusinessStartDateTime, @BusinessEndDateTime) = 0
    UNION
    SELECT
        ''Condition 8'' AS Grouping
    ,   DATEDIFF(SECOND, @EventStartDateTimeHour, @BusinessEndDateTimeHour) + ((DATEDIFF(DAY, @EventStartDateTime, @EventEndDateTime) - 1) * DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour)) + DATEDIFF(SECOND, @BusinessStartDateTimeHour, @EventEndDateTimeHour)
    WHERE DATEDIFF(DAY, @EventStartDateTime, @EventEndDateTime) > 0
    AND @IncludeWeekend = 1
    AND DATEDIFF(SECOND, @EventStartDateTimeHour, @BusinessStartDateTimeHour) <= 0
    AND DATEDIFF(SECOND, @BusinessEndDateTimeHour, @EventEndDateTimeHour) < 0
    AND DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour) > 0
    AND DATEDIFF(DAY, @BusinessStartDateTime, @BusinessEndDateTime) = 0
    UNION
    SELECT
        ''Condition 9'' AS Grouping
    ,   CASE
            WHEN @EventStartDateTimeIsWorkingDay = 1 THEN DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour)
            ELSE 0
        END
    WHERE DATEDIFF(DAY, @EventStartDateTime, @EventEndDateTime) = 0
    AND @IncludeWeekend = 0
    AND DATEDIFF(SECOND, @EventStartDateTimeHour, @BusinessStartDateTimeHour) > 0
    AND DATEDIFF(SECOND, @BusinessEndDateTimeHour, @EventEndDateTimeHour) >= 0
    AND DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour) > 0
    AND DATEDIFF(DAY, @BusinessStartDateTime, @BusinessEndDateTime) = 0
    UNION
    SELECT
        ''Condition 10'' AS Grouping
    ,   CASE
            WHEN @EventStartDateTimeIsWorkingDay = 1 THEN DATEDIFF(SECOND, @EventStartDateTimeHour, @BusinessEndDateTimeHour)
            ELSE 0
        END
    WHERE DATEDIFF(DAY, @EventStartDateTime, @EventEndDateTime) = 0
    AND @IncludeWeekend = 0
    AND DATEDIFF(SECOND, @EventStartDateTimeHour, @BusinessStartDateTimeHour) <= 0
    AND DATEDIFF(SECOND, @BusinessEndDateTimeHour, @EventEndDateTimeHour) >= 0
    AND DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour) > 0
    AND DATEDIFF(DAY, @BusinessStartDateTime, @BusinessEndDateTime) = 0
    UNION
    SELECT
        ''Condition 11'' AS Grouping
    ,   CASE
            WHEN @EventStartDateTimeIsWorkingDay = 1 THEN DATEDIFF(SECOND, @BusinessStartDateTimeHour, @EventEndDateTimeHour)
            ELSE 0
        END
    WHERE DATEDIFF(DAY, @EventStartDateTime, @EventEndDateTime) = 0
    AND @IncludeWeekend = 0
    AND DATEDIFF(SECOND, @EventStartDateTimeHour, @BusinessStartDateTimeHour) > 0
    AND DATEDIFF(SECOND, @BusinessEndDateTimeHour, @EventEndDateTimeHour) < 0
    AND DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour) > 0
    AND DATEDIFF(DAY, @BusinessStartDateTime, @BusinessEndDateTime) = 0
    UNION
    SELECT
        ''Condition 12'' AS Grouping
    ,   CASE
            WHEN @EventStartDateTimeIsWorkingDay = 1 THEN DATEDIFF(SECOND, @EventStartDateTimeHour, @EventEndDateTimeHour)
            ELSE 0
        END
    WHERE DATEDIFF(DAY, @EventStartDateTime, @EventEndDateTime) = 0
    AND @IncludeWeekend = 0
    AND DATEDIFF(SECOND, @EventStartDateTimeHour, @BusinessStartDateTimeHour) <= 0
    AND DATEDIFF(SECOND, @BusinessEndDateTimeHour, @EventEndDateTimeHour) < 0
    AND DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour) > 0
    AND DATEDIFF(DAY, @BusinessStartDateTime, @BusinessEndDateTime) = 0
    UNION
    SELECT
        ''Condition 13'' AS Grouping
    ,   CASE
            WHEN @EventStartDateTimeIsWorkingDay = 1 THEN DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour)
            ELSE 0
        END + ((@WorkDaysBetweenEventStartDateTimeAndEventEndDateTime) * DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour)) + CASE
            WHEN @EventEndDateTimeIsWorkingDay = 1 THEN DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour)
            ELSE 0
        END
    WHERE DATEDIFF(DAY, @EventStartDateTime, @EventEndDateTime) > 0
    AND @IncludeWeekend = 0
    AND DATEDIFF(SECOND, @EventStartDateTimeHour, @BusinessStartDateTimeHour) > 0
    AND DATEDIFF(SECOND, @BusinessEndDateTimeHour, @EventEndDateTimeHour) >= 0
    AND DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour) > 0
    AND DATEDIFF(DAY, @BusinessStartDateTime, @BusinessEndDateTime) = 0
    UNION
    SELECT
        ''Condition 14'' AS Grouping
    ,   CASE
            WHEN @EventStartDateTimeIsWorkingDay = 1 THEN DATEDIFF(SECOND, @EventStartDateTimeHour, @BusinessEndDateTimeHour)
            ELSE 0
        END + ((@WorkDaysBetweenEventStartDateTimeAndEventEndDateTime) * DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour)) + CASE
            WHEN @EventEndDateTimeIsWorkingDay = 1 THEN DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour)
            ELSE 0
        END
    WHERE DATEDIFF(DAY, @EventStartDateTime, @EventEndDateTime) > 0
    AND @IncludeWeekend = 0
    AND DATEDIFF(SECOND, @EventStartDateTimeHour, @BusinessStartDateTimeHour) <= 0
    AND DATEDIFF(SECOND, @BusinessEndDateTimeHour, @EventEndDateTimeHour) >= 0
    AND DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour) > 0
    AND DATEDIFF(DAY, @BusinessStartDateTime, @BusinessEndDateTime) = 0
    UNION
    SELECT
        ''Condition 15'' AS Grouping
    ,   CASE
            WHEN @EventStartDateTimeIsWorkingDay = 1 THEN DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour)
            ELSE 0
        END + ((@WorkDaysBetweenEventStartDateTimeAndEventEndDateTime) * DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour)) + CASE
            WHEN @EventEndDateTimeIsWorkingDay = 1 THEN DATEDIFF(SECOND, @BusinessStartDateTimeHour, @EventEndDateTimeHour)
            ELSE 0
        END
    WHERE DATEDIFF(DAY, @EventStartDateTime, @EventEndDateTime) > 0
    AND @IncludeWeekend = 0
    AND DATEDIFF(SECOND, @EventStartDateTimeHour, @BusinessStartDateTimeHour) > 0
    AND DATEDIFF(SECOND, @BusinessEndDateTimeHour, @EventEndDateTimeHour) < 0
    AND DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour) > 0
    AND DATEDIFF(DAY, @BusinessStartDateTime, @BusinessEndDateTime) = 0
    UNION
    SELECT
        ''Condition 16'' AS Grouping
    ,   CASE
            WHEN @EventStartDateTimeIsWorkingDay = 1 THEN DATEDIFF(SECOND, @EventStartDateTimeHour, @BusinessEndDateTimeHour)
            ELSE 0
        END + ((@WorkDaysBetweenEventStartDateTimeAndEventEndDateTime) * DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour)) + CASE
            WHEN @EventEndDateTimeIsWorkingDay = 1 THEN DATEDIFF(SECOND, @BusinessStartDateTimeHour, @EventEndDateTimeHour)
            ELSE 0
        END
    WHERE DATEDIFF(DAY, @EventStartDateTime, @EventEndDateTime) > 0
    AND @IncludeWeekend = 0
    AND DATEDIFF(SECOND, @EventStartDateTimeHour, @BusinessStartDateTimeHour) <= 0
    AND DATEDIFF(SECOND, @BusinessEndDateTimeHour, @EventEndDateTimeHour) < 0
    AND DATEDIFF(SECOND, @BusinessStartDateTimeHour, @BusinessEndDateTimeHour) > 0
    AND DATEDIFF(DAY, @BusinessStartDateTime, @BusinessEndDateTime) = 0
    UNION
    SELECT
        ''Condition 17'' AS Grouping
    ,   DATEDIFF(SECOND, @EventStartDateTime, @EventEndDateTime)
    WHERE DATEDIFF(DAY, @BusinessStartDateTime, @BusinessEndDateTime) > 0) AS SecondsPassed
IF DATEDIFF(SECOND, @BusinessEndDateTimeHour, @EventStartDateTimeHour) > 0 
BEGIN 
    SET @TEMP = @TEMP +  DATEDIFF(SECOND, @BusinessEndDateTimeHour, @EventStartDateTimeHour)
END

IF DATEDIFF(SECOND, @EventEndDateTimeHour, @BusinessStartDateTimeHour) > 0 
BEGIN 
    SET @TEMP = @TEMP + DATEDIFF(SECOND, @EventEndDateTimeHour, @BusinessStartDateTimeHour)
END
RETURN @Temp
END
GO

Answer 9

Sé que esta publicación es muy antigua, pero he aquí una función que escribí recientemente para calcular los horarios comerciales / minutos entre dos eventos. También tiene en cuenta las vacaciones que deben definirse en una tabla.

La función devuelve el intervalo en minutos; puede dividir por 60 para obtener horas según sea necesario.

Esto ha sido probado en SQL Server 2008. Espero que ayude a alguien.

Create Function GetWorkingMin(@StartDate DateTime, @EndDate DateTime, @Country Varchar(2)) Returns Int
AS
Begin
    Declare @WorkMin int = 0   -- Initialize counter
    Declare @Reverse bit       -- Flag to hold if direction is reverse
    Declare @StartHour int = 9   -- Start of business hours (can be supplied as an argument if needed)
    Declare @EndHour int = 17    -- End of business hours (can be supplied as an argument if needed)
    Declare @Holidays Table (HDate DateTime)   --  Table variable to hold holidayes

    -- If dates are in reverse order, switch them and set flag
    If @StartDate>@EndDate 
    Begin
        Declare @TempDate DateTime=@StartDate
        Set @StartDate=@EndDate
        Set @EndDate=@TempDate
        Set @Reverse=1
    End
    Else Set @Reverse = 0

    -- Get country holidays from table based on the country code (Feel free to remove this or modify as per your DB schema)
    Insert Into @Holidays (HDate) Select HDate from HOLIDAY Where COUNTRYCODE=@Country and HDATE>=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)

    If DatePart(HH, @StartDate)<@StartHour Set @StartDate = DateAdd(hour, @StartHour, DateDiff(DAY, 0, @StartDate))  -- If Start time is less than start hour, set it to start hour
    If DatePart(HH, @StartDate)>=@EndHour+1 Set @StartDate = DateAdd(hour, @StartHour+24, DateDiff(DAY, 0, @StartDate)) -- If Start time is after end hour, set it to start hour of next day
    If DatePart(HH, @EndDate)>=@EndHour+1 Set @EndDate = DateAdd(hour, @EndHour, DateDiff(DAY, 0, @EndDate)) -- If End time is after end hour, set it to end hour
    If DatePart(HH, @EndDate)<@StartHour Set @EndDate = DateAdd(hour, @EndHour-24, DateDiff(DAY, 0, @EndDate)) -- If End time is before start hour, set it to end hour of previous day

    If @StartDate>@EndDate Return 0

    -- If Start and End is on same day
    If DateDiff(Day,@StartDate,@EndDate) <= 0
    Begin
        If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7  -- If day is between sunday and saturday
            If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0  -- If day is not a holiday
                If @EndDate<@StartDate Return 0 Else Set @WorkMin=DATEDIFF(MI, @StartDate, @EndDate) -- Calculate difference
            Else Return 0
        Else Return 0
    End
    Else Begin
        Declare @Partial int=1   -- Set partial day flag
        While DateDiff(Day,@StartDate,@EndDate) > 0   -- While start and end days are different
        Begin
            If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7    --  If this is a weekday
            Begin
                If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0  -- If this is not a holiday
                Begin
                    If @Partial=1  -- If this is the first iteration, calculate partial time
                    Begin 
                        Set @WorkMin=@WorkMin + DATEDIFF(MI, @StartDate, DateAdd(hour, @EndHour, DateDiff(DAY, 0, @StartDate)))
                        Set @StartDate=DateAdd(hour, @StartHour+24, DateDiff(DAY, 0, @StartDate)) 
                        Set @Partial=0 
                    End
                    Else Begin      -- If this is a full day, add full minutes
                        Set @WorkMin=@WorkMin + (@EndHour-@StartHour)*60        
                        Set @StartDate = DATEADD(DD,1,@StartDate)
                    End
                End
                Else Set @StartDate = DATEADD(DD,1,@StartDate)  
            End
            Else Set @StartDate = DATEADD(DD,1,@StartDate)
        End
        If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7  -- If last day is a weekday
            If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0   -- And it is not a holiday
                If @Partial=0 Set @WorkMin=@WorkMin + DATEDIFF(MI, @StartDate, @EndDate) Else Set @WorkMin=@WorkMin + DATEDIFF(MI, DateAdd(hour, @StartHour, DateDiff(DAY, 0, @StartDate)), @EndDate)
    End 
    If @Reverse=1 Set @WorkMin=-@WorkMin
    Return @WorkMin
End

Answer 10

Una solución alternativa de @ Pavanred, viniendo desde un ángulo más basado en datos:

Crea una tabla con todas las fechas que quieras considerar en ella. Para cada día, establezca un número de horas de trabajo, de esta manera:

WorkingDate Hours Comment
=========== ===== ==================
 1 Jan 2011     0 Saturday
 2 Jan 2011     0 Sunday
 3 Jan 2011     0 Public Holiday
 4 Jan 2011     8 Normal working day
 5 Jan 2011     8 Normal working day

 -- and so on, for all the days you want to report on.

Esto requerirá una pequeña cantidad de configuración: puede rellenarlo previamente durante semanas versus fines de semana de forma automática, luego ajustarlo para días festivos, etc., según sea necesario.

Pero, lo que pierdes en la configuración, obtienes facilidad de consulta:

SELECT
  SUM(Hours) 
FROM
  working_days 
WHERE
  WorkingDate BETWEEN @StartDate AND @EndDate

... y esto puede funcionar como un enfoque más fácil si necesita comenzar a agregar reglas más complicadas para lo que define un día laboral, o si sus horas de trabajo varían según el día, etc.

También hace que las reglas sean más fáciles de "editar", ya que no necesita cambiar ningún código real para cambiar las definiciones de un día de trabajo, agregar días festivos, etc.

Answer 11

Otra forma de pensar , la siguiente función funciona correctamente si su primer día de la semana es el lunes; de lo contrario, debería cambiar las líneas relacionadas, incluyendo (6,7) los días de su fin de semana local.

create function fn_worktime(@Datetime1 DateTime,@Datetime2 DateTime)
Returns BigInt
as
Begin
    Declare 
            @Date1 Date, 
            @Date2 Date,
            @DateIndex Date,
            @minutes int,
            @lastDayMinutes int,
            @StartTime int , --in minutes
            @FinishTime int ,--in minutes
            @WorkDayLong int --in minutes

    Set @StartTime  =8 * 60 + 30 -- 8:30
    Set @FinishTime =17* 60 + 30 -- 17:30
    Set @WorkDayLong =@FinishTime - @StartTime  

    Set @Date1 = Convert(Date,@DateTime1)
    Set @Date2 = Convert(Date,@DateTime2)
    Set @minutes=DateDiff(minute,@DateTime1,DateAdd(MINUTE,@FinishTime ,convert(DateTime,@Date1)))
    if @minutes<0 OR DatePart(dw,@Date1) in (6,7)  -- you can even check holdays here. ''(6 Saturday,7 Sunday) according to SET DATEFIRST 1''
        Set @minutes=0

    Set @DateIndex=DateAdd(day,1,@Date1)
    While @DateIndex<@Date2
    Begin
        if DatePart(dw,@DateIndex) not in (6,7)  -- you can even check holdays here. ''(6 Saturday,7 Sunday) according to SET DATEFIRST 1''
            set @minutes=@minutes+@WorkDayLong 
        Set @DateIndex=DateAdd(day,1,@DateIndex)
    End
    if DatePart(dw,@DateIndex) not in (6,7)  -- you can even check holdays here
    Begin
        set @lastDayMinutes=DateDiff(minute,DateAdd(MINUTE ,@StartTime ,convert(DateTime,@Date2)),@DateTime2)
        if @lastDayMinutes>@WorkDayLong 
            set @lastDayMinutes=@WorkDayLong 
        if @Date1<>@Date2   
            set @minutes=@minutes+@lastDayMinutes
        Else
            Set @minutes=@minutes+@lastDayMinutes-@WorkDayLong 

    End
    return @minutes
End

Answer 12

    -- =============================================
-- Author:      Baran Kaynak
-- Create date: 14.03.2011
-- Description: 09:30 ile 17:30 arasındaki iş saatlerini hafta sonlarını almayarak toplar.
-- =============================================
CREATE FUNCTION [dbo].[WorkTime] 
(
    @StartDate DATETIME,
    @FinishDate DATETIME
)
RETURNS BIGINT
AS
BEGIN
    DECLARE @Temp BIGINT
    SET @Temp=0

    DECLARE @FirstDay DATE
    SET @FirstDay = CONVERT(DATE, @StartDate, 112)

    DECLARE @LastDay DATE
    SET @LastDay = CONVERT(DATE, @FinishDate, 112)

    DECLARE @StartTime TIME
    SET @StartTime = CONVERT(TIME, @StartDate)

    DECLARE @FinishTime TIME
    SET @FinishTime = CONVERT(TIME, @FinishDate)

    DECLARE @WorkStart TIME
    SET @WorkStart = ''09:30''

    DECLARE @WorkFinish TIME
    SET @WorkFinish = ''17:30''

    IF (@StartTime<@WorkStart)
    BEGIN
        SET @StartTime = @WorkStart
    END
    IF (@FinishTime>@WorkFinish)
    BEGIN
        SET @FinishTime=@WorkFinish
    END

    DECLARE @CurrentDate DATE
    SET @CurrentDate = CONVERT(DATE, @StartDate, 112)
    DECLARE @LastDate DATE
    SET @LastDate = CONVERT(DATE, @FinishDate, 112)

    WHILE(@CurrentDate<=@LastDate)
    BEGIN       
        IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
        BEGIN
            IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
            BEGIN
                SET @Temp = (@Temp + (9*60))
            END
            --IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes
            ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
            BEGIN
                SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
            END

            ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
            BEGIN
                SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
            END
            --IF it starts and finishes in the same date
            ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
            BEGIN
                SET @Temp = DATEDIFF(MINUTE, @StartDate, @FinishDate)
            END
        END
        SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
    END

    -- Return the result of the function
    IF @Temp<0
    BEGIN
        SET @Temp=0
    END
    RETURN @Temp

END

GO

Answer 13

ALTER FUNCTION WorkTime_fn (@StartDate DATETIME, @FinishDate DATETIME)
RETURNS VARCHAR(9)
AS
BEGIN
    DECLARE @Temp BIGINT
    SET @Temp=0

    DECLARE @FirstDay VARCHAR(9)
    SET @FirstDay = CONVERT(VARCHAR(9),@StartDate, 112)

    DECLARE @LastDay VARCHAR(9)
    SET @LastDay = CONVERT(VARCHAR(9),@FinishDate, 112)

    DECLARE @StartTime VARCHAR(9)
    SET @StartTime = CONVERT(VARCHAR(9),@StartDate, 108)

    DECLARE @FinishTime VARCHAR(9)
    SET @FinishTime = CONVERT(VARCHAR(9),@FinishDate, 108)

    DECLARE @WorkStart VARCHAR(9)
    SET @WorkStart = ''09:30:00''

    DECLARE @WorkFinish VARCHAR(9)
    SET @WorkFinish = ''17:30:00''

    IF (@StartTime<@WorkStart)
    BEGIN
        SET @StartTime = @WorkStart
    END
    IF (@FinishTime>@WorkFinish)
    BEGIN
        SET @FinishTime=@WorkFinish
    END

DECLARE @CurrentDate VARCHAR(9)
    SET @CurrentDate = CONVERT(VARCHAR(9),@StartDate, 112)
    DECLARE @LastDate VARCHAR(9)
    SET @LastDate = CONVERT(VARCHAR(9),@FinishDate, 112)

WHILE(@CurrentDate<=@LastDate)
BEGIN       

        IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
        BEGIN
              IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
              BEGIN
                   SET @Temp = (@Temp + (8*60))

              END

              ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
              BEGIN
                SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)

              END

              ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
              BEGIN
                SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)

              END

              ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
              BEGIN
                SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)

              END

             END

SET @CurrentDate = CONVERT(VARCHAR(9),DATEADD(day, 1, @CurrentDate),112)

END
        Return @TEMP

END

Answer 14

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @WORKINGHOURS INT
DECLARE @Days INT
SET @StartDate = ''2010/01/01''
SET @EndDate = ''2010/04/01''

--number of working days
SELECT @Days = 
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATENAME(dw, @StartDate) = ''Sunday'' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @EndDate) = ''Saturday'' THEN 1 ELSE 0 END)

--8 hours a day    
SET @WORKINGHOURS = @Days * 8 

SELECT @WORKINGHOURS