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