Как я могу вычислить рабочее время между двумя датами? Например, у нас есть две даты; 01.01.2010 15:00 и 04/01/2010 12:00 И мы работаем с 09:00 до 17:00 в будние дни Как я могу вычислить рабочее время с sql?
Расчет рабочего времени между двумя датами
Ответ 1
Ответ барана фиксированный и измененный для SQL 2005
SQL 2008 и выше:
-- =============================================
-- 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 @[email protected]
END
IF (@FinishTime<@WorkStart)
BEGIN
SET @[email protected]
END
IF (@StartTime>@WorkFinish)
BEGIN
SET @StartTime = @WorkFinish
END
DECLARE @CurrentDate DATE
SET @CurrentDate = @FirstDay
DECLARE @LastDate DATE
SET @LastDate = @LastDay
WHILE(@CurrentDate<[email protected])
BEGIN
IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
BEGIN
IF (@[email protected]) AND (@[email protected])
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 (@[email protected]) AND (@[email protected])
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
END
ELSE IF (@[email protected]) AND (@[email protected])
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
END
--IF it starts and finishes in the same date
ELSE IF (@[email protected]) AND (@[email protected])
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 и ниже:
-- =============================================
-- 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 @[email protected]
END
IF (@FinishTime<@WorkStart)
BEGIN
SET @[email protected]
END
IF (@StartTime>@WorkFinish)
BEGIN
SET @StartTime = @WorkFinish
END
DECLARE @CurrentDate DATETIME
SET @CurrentDate = @FirstDay
DECLARE @LastDate DATETIME
SET @LastDate = @LastDay
WHILE(@CurrentDate<[email protected])
BEGIN
IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
BEGIN
IF (@[email protected]) AND (@[email protected])
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 (@[email protected]) AND (@[email protected])
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
END
ELSE IF (@[email protected]) AND (@[email protected])
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
END
--IF it starts and finishes in the same date
ELSE IF (@[email protected]) AND (@[email protected])
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
Ответ 2
Я знаю, что это сообщение очень старое, но вот функция, которую я написал недавно, чтобы вычислить часы работы/минуты между любыми двумя событиями. Он также учитывает любые праздники, которые должны быть определены в таблице.
Функция возвращает интервал в минутах - вы можете разделить на 60, чтобы получить часы по мере необходимости.
Это было протестировано на SQL Server 2008. Надеюсь, это поможет кому-то.
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 [email protected]
Set @[email protected]
Set @[email protected]
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 [email protected] 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)>[email protected]+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)>[email protected]+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 @[email protected] + 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 @[email protected] + (@[email protected])*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 @[email protected] + DATEDIFF(MI, @StartDate, @EndDate) Else Set @[email protected] + DATEDIFF(MI, DateAdd(hour, @StartHour, DateDiff(DAY, 0, @StartDate)), @EndDate)
End
If @Reverse=1 Set @[email protected]
Return @WorkMin
End
Ответ 3
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
Ответ 4
Первым шагом будет рассчитать рабочие дни, как показано ниже в 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;
Второй шаг включает в себя получение разницы в секундах между двумя датами и преобразование этой разницы в часы путем деления на 3600,0, как показано ниже: script:
SET @TotalTimeDiff =
(
SELECT DATEDIFF(SECOND,
(
SELECT CONVERT(TIME, @DateFrom)
),
(
SELECT CONVERT(TIME, @DateTo)
)) / 3600.0
);
Последняя часть включает в себя умножение вывода на первый шаг выше на 24 (общее количество часов в день), а затем последующее добавление этого результата на второй шаг:
SELECT(@TotalWorkDays * 24.00) + @TotalTimeDiff;
Наконец, полный script, который можно использовать для создания пользовательской функции для расчета рабочего времени, показан ниже:
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
Полный метод описан в этой статье: https://www.sqlshack.com/how-to-calculate-work-days-and-hours-in-sql-server/
Ответ 5
Альтернативное решение от @Pavanred's, исходящее от вещей с более основанного на данных угла:
Создайте таблицу со всеми датами, которые вы хотите рассмотреть в ней. Для каждого дня задайте количество рабочих часов, например:
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.
Это займет небольшую сумму - вы можете предварительно заполнить его в течение недель в зависимости от выходных дней автоматически, затем при необходимости настроить праздничные дни и т.д.
Но, что вы теряете при настройке, вы легко получаете запрос:
SELECT
SUM(Hours)
FROM
working_days
WHERE
WorkingDate BETWEEN @StartDate AND @EndDate
... и это может работать как более простой подход, если вам нужно начать добавлять более сложные правила для того, что определяет рабочий день, или если ваш рабочий день меняется в зависимости от дня и т.д.
Он также упрощает "редактирование" правил, поскольку вам не нужно изменять какой-либо реальный код, чтобы изменить определения рабочего дня, добавить праздничные дни и т.д.
Ответ 6
-- =============================================
-- 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 @[email protected]
END
DECLARE @CurrentDate DATE
SET @CurrentDate = CONVERT(DATE, @StartDate, 112)
DECLARE @LastDate DATE
SET @LastDate = CONVERT(DATE, @FinishDate, 112)
WHILE(@CurrentDate<[email protected])
BEGIN
IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
BEGIN
IF (@[email protected]) AND (@[email protected])
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 (@[email protected]) AND (@[email protected])
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
END
ELSE IF (@[email protected]) AND (@[email protected])
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
END
--IF it starts and finishes in the same date
ELSE IF (@[email protected]) AND (@[email protected])
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
Ответ 7
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 @[email protected]
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<[email protected])
BEGIN
IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
BEGIN
IF (@[email protected]) AND (@[email protected])
BEGIN
SET @Temp = (@Temp + (8*60))
END
ELSE IF (@[email protected]) AND (@[email protected])
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
END
ELSE IF (@[email protected]) AND (@[email protected])
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
END
ELSE IF (@[email protected]) AND (@[email protected])
BEGIN
SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)
END
END
SET @CurrentDate = CONVERT(VARCHAR(9),DATEADD(day, 1, @CurrentDate),112)
END
Return @TEMP
END
Ответ 8
Вот встроенная версия Начало /EndDateTime как 2015-03-16 09: 52: 24.000 Start/EndTime (рабочие часы), такие как 07:00:00 Он громоздкий, но работает в вашем заявлении select
Я также опубликую его в версии Function.
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>))
Вот версия функции:
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
Ответ 9
В вопросе говорится, что государственные праздники не следует рассматривать, так что этот ответ делает именно это: расчет рабочего дня с учетом выходных дней, но игнорирование возможных праздничных дней.
Он также предполагает, что данные даты и времени начала и окончания указаны в рабочее время.
При таком предположении код не заботится о времени, когда рабочий день начинается или заканчивается, он заботится только об общем количестве рабочих часов в день. В вашем примере с 09:00 до 17:00 работает 8 рабочих часов. Это не должно быть целое число. Формула ниже вычисляет ее с точностью до одной минуты, но тривиально сделать ее на одну секунду или любую другую точность.
Если вам нужно принять во внимание праздничные дни, вам потребуется отдельная таблица, в которой будут указаны даты для праздничных дней, которые могут отличаться от года к году, а также от штата к штату или от страны к стране. Основная формула может оставаться неизменной, но вам нужно будет вычесть из своих часов результата для государственных праздников, которые попадают в заданный диапазон дат.
Формула
SELECT
DATEDIFF(minute, StartDT, EndDT) / 60.0
- DATEDIFF(day, StartDT, EndDT) * 16
- DATEDIFF(week, StartDT, EndDT) * 16 AS BusinessHours
FROM T
Чтобы понять, как это работает, создайте таблицу с некоторыми примерами данных, которые охватывают различные случаи:
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
Запрос
SELECT
StartDT,
EndDT,
DATEDIFF(minute, StartDT, EndDT) / 60.0
- DATEDIFF(day, StartDT, EndDT) * 16
- DATEDIFF(week, StartDT, EndDT) * 16 AS BusinessHours
FROM @T;
дает следующий результат:
+---------------------+---------------------+---------------+
| 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 |
+---------------------+---------------------+---------------+
Это работает, потому что в SQL Server DATEDIFF возвращает счетчик указанных границ даты, пересеченных между указанными начальными и конечными значениями.
Каждый день имеет 8 рабочих часов. Я вычисляю общее количество часов между двумя датами, а затем вычитаю количество полуночи, умноженное на 16 нерабочих часов в день, затем вычитаем количество выходных дней, умноженное на 16 (8 + 8 рабочих часов для Sat + Sun).
Ответ 10
Я действительно делал это раньше, принимая во внимание все переменные (выходные, праздничные дни и т.д.) для рабочих часов, очень сложно, я думаю, что эта задача лучше всего сделать за пределами SQL
Ответ 11
Еще один способ мышления, нижняя функция работает правильно, если ваш первый день недели - понедельник, иначе вы должны изменить связанные линии, включая (6,7), в выходные дни локального времени.
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 [email protected] - @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 @[email protected][email protected]
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 @[email protected]
if @Date1<>@Date2
set @[email protected][email protected]
Else
Set @[email protected][email protected]@WorkDayLong
End
return @minutes
End
Ответ 12
Что вы думаете об этом решении?
Без использования цикла "Пока".
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
Ответ 13
Версия 2008 с циклом while слишком медленна для нашего рабочего процесса. Версия цикла выше занимает 10 минут на нашем SQL-сервере, если мы запускаем вычисление с несколькими годами между StartDate и FinishDate для миллиона строк. Моя версия завершает одну и ту же задачу за 1 минуту.
Ниже приведена функция без цикла (проверена в 2008 году) для справки:
ALTER 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
RETURN @Temp
END
Ответ 14
Здесь альтернативное решение без использования функции. Обратите внимание, что это зависит от существования таблицы чисел, заполненной, по крайней мере, максимальным количеством дней, которое могут выполнять отслеживаемые задачи.
Это не учитывает государственные праздники. Если вы не работаете в выходные дни, установка времени открытия и закрытия до полуночи в переменной таблицы @OpeningHours должна выполнять задание.
Я тестировал это против 8500 строк данных "реального мира" и нашел, что он работает.
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;