Calculate business hours between two dates
How can I calculate business hours between two dates? For example we have two dates; 01/01/2010 15:00 and 04/01/2010 12:00 开发者_StackOverflow中文版And we have working hours 09:00 to 17:00 in weekdays How can I calculate working hours with sql?
Baran's answer fixed and modified for SQL 2005
SQL 2008 and above:
-- =============================================
-- 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 and below:
-- =============================================
-- 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
I know this is post is very old but here is a function I wrote recently to calculate Business Hours/Minutes between any two events. It also takes into account any holidays which must be defined in a table.
The function returns the interval in minutes - you can divide by 60 to get hours as required.
This has been tested on SQL Server 2008. Hope it helps someone.
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
An alternative solution from @Pavanred's, coming at things from a more data-based angle:
Create a table with all the dates you want to consider in it. For each day, set a number of working hours, like so:
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.
This will take a small amount of setting up -- you can pre-populate it for weeks versus weekends automatically, then adjust for public holidays, etc, as necessary.
But, what you lose in the setting up, you gain in ease of querying:
SELECT
SUM(Hours)
FROM
working_days
WHERE
WorkingDate BETWEEN @StartDate AND @EndDate
...and this can work out as an easier approach if you need to start adding more complicated rules for what defines a working day, or if your working hours vary depending on the day, etc.
It also makes the rules more easily "editable", as you don't need to change any actual code to change the definitions of a working day, add public holidays, etc.
The first step would be to calculate working days, as shown in the script below:
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;
The second step involves getting a difference in seconds between the two dates and converting that difference into hours by dividing by 3600.0 as shown in this following script:
SET @TotalTimeDiff =
(
SELECT DATEDIFF(SECOND,
(
SELECT CONVERT(TIME, @DateFrom)
),
(
SELECT CONVERT(TIME, @DateTo)
)) / 3600.0
);
The last part involves multiplying the output the first step above by 24 (total number of hours in a day) and then later adding that to the output of the second step:
SELECT(@TotalWorkDays * 24.00) + @TotalTimeDiff;
Finally, the complete script that can be used to create a user defined function for calculating working hours is shown below:
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
The complete method is descibed in this article: https://www.sqlshack.com/how-to-calculate-work-days-and-hours-in-sql-server/
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
-- =============================================
-- 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
The question says that public holidays should not be considered, so this answer does just that - calculates business hours taking weekends into account, but ignoring possible public holidays.
It also assumes that the given start and end date/times are during the business hours.
With this assumption the code doesn't care about the time when the business day starts or ends, it cares only about the total number of business hours per day. In your example, there are 8 business hours between 09:00 and 17:00. It doesn't have to be a whole number. The formula below calculates it with one minute precision, but it is trivial to make it one second or any other precision.
If you need to take public holidays into account you'd need to have a separate table which would list dates for public holidays, which may differ from year to year and from state to state or country to country. The main formula may stay the same, but you'd need to subtract from its result hours for public holidays that fall within the given range of dates.
The formula
SELECT
DATEDIFF(minute, StartDT, EndDT) / 60.0
- DATEDIFF(day, StartDT, EndDT) * 16
- DATEDIFF(week, StartDT, EndDT) * 16 AS BusinessHours
FROM T
To understand how it works let's create a table with some sample data that covers various cases:
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
The query
SELECT
StartDT,
EndDT,
DATEDIFF(minute, StartDT, EndDT) / 60.0
- DATEDIFF(day, StartDT, EndDT) * 16
- DATEDIFF(week, StartDT, EndDT) * 16 AS BusinessHours
FROM @T;
produces the following result:
+---------------------+---------------------+---------------+
| 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 |
+---------------------+---------------------+---------------+
It works, because in SQL Server DATEDIFF returns the count of the specified datepart boundaries crossed between the specified startdate and enddate.
Each day has 8 business hours. I calculate total number of hours between two dates, then subtract the number of midnights multiplied by 16 non-business hours per day, then subtract the number of weekends multiplied by 16 (8+8 business hours for Sat+Sun).
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
Here is an inline version Start/EndDateTime like 2015-03-16 09:52:24.000 Start/EndTime (businesshours) like 07:00:00 It is bulky but works in your select statement
I will post it in Function version as well.
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>))
Here is the Function Version:
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
I have actually done this before, taking into account all the variables (weekends, holidays, etc) for business hours is very difficult, I think this task is best done outside SQL
Another way of thinking, the below function work correctly if your first day of week is Monday otherwise you should change related lines including (6,7) to your local weekend's days
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
What do you think about this solution?
Without using loop "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
Here's an alternative solution, without the use of a function. Note that this relies on the existence of a numbers table, populated with at least the maximum number of days the tasks you're tracking may take.
This doesn't take public holidays into account. If you don't work weekends, setting the opening and closing times to midnight in the @OpeningHours table variable should do the job.
I've tested this against 8500 rows of 'real world' data and found it to be performant.
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;
A version I wrote today taking into account bank holidays. Note: This is not thoroughly tested and could no doubt be improved.
CREATE FUNCTION [dbo].[WorkingHoursBetween2Dates]
(
@dtFrom datetime,
@dtTo datetime
)
RETURNS INT
BEGIN
DECLARE @tblDates AS TABLE (DateValue DATE)
DECLARE @dFrom date = @dtFrom
DECLARE @dTo date = @dtTo
DECLARE @intDays int
DECLARE @intHours int = 0
DECLARE @dFromWorkday bit = CASE WHEN (DATENAME(WEEKDAY, @dFrom) IN ('Saturday','Sunday')) OR EXISTS (SELECT * FROM dbo.BankHolidays WHERE BankHolidayDate = @dFrom) THEN 0 ELSE 1 END
DECLARE @dToWorkday bit = CASE WHEN (DATENAME(WEEKDAY, @dTo) IN ('Saturday','Sunday')) OR EXISTS (SELECT * FROM dbo.BankHolidays WHERE BankHolidayDate = @dTo) THEN 0 ELSE 1 END
IF DATEPART(HOUR,@dtFrom) < 9
SET @dtFrom = DATEADD(HOUR,9,CAST(CAST(@dtFrom AS DATE) AS DATETIME))
ELSE
IF DATEPART(HOUR,@dtFrom) > 17
SET @dtFrom = DATEADD(HOUR,17,CAST(CAST(@dtFrom AS DATE) AS DATETIME))
IF DATEPART(HOUR,@dtTo) < 9
SET @dtTo = DATEADD(HOUR,9,CAST(CAST(@dtTo AS DATE) AS DATETIME))
ELSE
IF DATEPART(HOUR,@dtTo) > 17
SET @dtTo = DATEADD(HOUR,17,CAST(CAST(@dtTo AS DATE) AS DATETIME))
WHILE @dFrom <= @dTo
BEGIN
INSERT INTO @tblDates
(
DateValue
)
SELECT @dFrom
WHERE NOT ((DATENAME(WEEKDAY, @dFrom) IN ('Saturday','Sunday')) OR EXISTS (SELECT * FROM dbo.BankHolidays WHERE BankHolidayDate = @dFrom))
SET @dFrom = DATEADD(DAY,1,@dFrom)
END
SET @intDays = CASE WHEN EXISTS(SELECT * FROM @tblDates) THEN (SELECT COUNT(*) FROM @tblDates) - 1 ELSE 0 END
IF @intDays = 0
BEGIN
IF @dFromWorkday = 1
IF DATEPART(HOUR,@dtFrom) < 17
BEGIN
IF DATEDIFF(DAY,@dtFrom,@dtTo)=0
SET @intHours = DATEDIFF(HOUR,@dtFrom,@dtTo)
ELSE
SET @intHours = DATEDIFF(HOUR,@dtFrom,DATEADD(HOUR,17,CAST(CAST(@dtFrom AS DATE) AS DATETIME)))
END
IF @dToWorkday = 1 AND DATEDIFF(DAY,@dtFrom,@dtTo)<>0
IF DATEPART(HOUR,@dtTo) >= 17
SET @intHours = @intHours + 8
ELSE
IF DATEPART(HOUR,@dtTo) > 9
SET @intHours = @intHours + DATEPART(HOUR,@dtTo) - 9
END
ELSE
BEGIN
IF @dFromWorkday = 1
IF DATEPART(HOUR,@dtFrom) < 17
BEGIN
SET @intHours = DATEDIFF(HOUR,@dtFrom,DATEADD(HOUR,17,CAST(CAST(@dtFrom AS DATE) AS DATETIME)))
SET @intDays = @intDays - 1
END
IF DATEPART(HOUR,@dtTo) < 17
SET @intHours = @intHours + (@intDays * 8) + CASE WHEN @dToWorkday = 1 THEN DATEDIFF(HOUR,DATEADD(HOUR,9,CAST(CAST(@dtTo AS DATE) AS DATETIME)), @dtTo) ELSE 0 END
ELSE
SET @intHours = @intHours + ((@intDays + 1) * 8)
END
RETURN (@intHours)
END
I've just hit this issue and come up with the following solution. It takes as start and end time for the working day, works out the minutes used for partial days, removes lunch breaks if more than x hours have been worked, and removes weekends and bank holidays.
Here's the working out: Date From "2020-09-09 07:49" to "2020-12-18 08:27:00.000" = 34598 minutes
= 101 days - 28 weekends = 73 days (71 minus start and end partial days) = 34080 minutes (568 hours x 71 whole days @ 8 hours per day) + start date 07:49 to 17:00 with an hour for lunch (8 hours 11 minutes) = 491 (551 minutes - 60 for lunch) + end date 08:00 to 08:27 = 27 minutes = 34080 + 491 + 27 = 34598
CREATE TABLE bankHoliday (
bhDate DATE
) ON [PRIMARY]
-- 2021
INSERT INTO bankHoliday (bhDate) VALUES ('2021-01-01' );
INSERT INTO bankHoliday (bhDate) VALUES ('2021-04-02' );
INSERT INTO bankHoliday (bhDate) VALUES ('2021-04-05' );
INSERT INTO bankHoliday (bhDate) VALUES ('2021-05-03' );
INSERT INTO bankHoliday (bhDate) VALUES ('2021-05-31' );
INSERT INTO bankHoliday (bhDate) VALUES ('2021-08-30' );
INSERT INTO bankHoliday (bhDate) VALUES ('2021-12-27' );
INSERT INTO bankHoliday (bhDate) VALUES ('2021-12-28' );
DROP FUNCTION dbo.[fn_GetTotalWorkingMinutes];
GO
CREATE FUNCTION [fn_GetTotalWorkingMinutes]
(
@DateFrom Datetime,
@DateTo Datetime
)
RETURNS INT
AS
BEGIN
DECLARE @StartOfDay TIME = '08:00:00'
DECLARE @EndOfDay TIME = '17:00:00'
DECLARE @Lunch INT = 60
DECLARE @ThresholdForLunch INT = 300 -- 5 hours x 60 mins, assume x minutes for lunch (working 8:00AM to after 1:00PM, or before 12:00PM to 5:00pm will probably include lunch)
DECLARE @WorkingMinutes INT = 0
;WITH seq(n) AS
(
-- Get an integer sequence number starting from zero, per day between From and To dates
SELECT 0 UNION ALL SELECT n + 1 FROM seq
WHERE n < DATEDIFF(DAY, @DateFrom, @DateTo)
),
d(Id,calDate,dayNum) AS
(
-- Return the sequence as an Id, the dates between From and To, and the Day-of-week number (1 = Sunday, 7 = Saturday)
SELECT n AS Id
, DATEADD(DAY, n, @DateFrom) AS calDate
, DATEPART(dw, DATEADD( DAY, n, @DateFrom)) AS dayNum
FROM seq
),
src AS
(
SELECT Id
, CAST(CONVERT(NVARCHAR(10), d.calDate, 126) AS date) AS calDate
, dayNum
, CASE
WHEN FORMAT(d.calDate, 'yyyy-MM-dd' ) = FORMAT(@DateFrom, 'yyyy-MM-dd' ) THEN @DateFrom -- Return the Date From with Start Time
ELSE CAST(CONVERT(NVARCHAR(10), d.calDate, 126) AS datetime) + CAST(@StartOfDay AS datetime) -- Add the Start of Day Time to each day
END AS calStartDateTime
, CASE
WHEN FORMAT(d.calDate, 'yyyy-MM-dd' ) = FORMAT(@DateTo, 'yyyy-MM-dd' ) THEN @DateTo -- Return the Date To with End Time
ELSE CAST(CONVERT(NVARCHAR(10), d.calDate, 126) AS datetime) + CAST(@EndOfDay AS datetime) -- Add the End of Day Time to each day
END AS calEndDateTime
FROM d
-- Exclude Bank Holidays
LEFT JOIN dbo.bankHoliday bh
ON bh.bhDate = d.caldate
WHERE bh.bhDate IS NULL -- Exclude Bank Holidays
AND d.dayNum > 1 -- After Sunday
AND d.dayNum < 7 -- Before Saturday
)
/* For Debugging
SELECT Id
, calDate
, calStartDateTime
, calEndDateTime
, CASE
WHEN DATEDIFF(minute, calStartDateTime, calEndDateTime) > 5 THEN DATEDIFF(minute, calStartDateTime, calEndDateTime) - @Lunch -- Take an hour off for lunch if more than 5 hours have been worked
ELSE DATEDIFF(minute, calStartDateTime, calEndDateTime)
END AS WorkingHours
*/
SELECT @WorkingMinutes = SUM(
CASE
WHEN DATEDIFF(minute, calStartDateTime, calEndDateTime) > @ThresholdForLunch THEN DATEDIFF(minute, calStartDateTime, calEndDateTime) - @Lunch -- Take an hour off for lunch if more than 5 hours have been worked
ELSE DATEDIFF(minute, calStartDateTime, calEndDateTime)
END
)
FROM src
--ORDER BY calDate
OPTION (MAXRECURSION 0);
RETURN (@workingMinutes);
END
GO
SELECT requested
, solved
, dbo.[fn_GetTotalWorkingMinutes](requested, solved)
FROM MyTable
加载中,请稍侯......
精彩评论