May 19, 2014 at 9:47 am
Hello
I have a table with a list of jobs along with their start and end datetime values
I am looking for a function which will return the time taken to process a job using a start date and an end date. If the date range covers a Saturday or Sunday I want the time to ignore the weekends
Example
Start Date=2014-05-15 12:00:00.000
End Date=2014-05-19 13:00:00.000
Total Time should be: 2 Days, 1 Hour and 0 Minutes
May 19, 2014 at 11:06 am
I did not address the weekend requirement but came up with something that will calculate days, hours and minutes. The only thing left to do is calclulate how much of the time range (in minutes) falls in a weekend and subtract that value; I don't have time for that at the moment (hopefully I'll have more time later this afternoon) but this should get you started.
DECLARE
@StartDate datetime = '2014-05-15 12:00:00.000',
@EndDate datetime = '2014-05-19 13:03:00.000';
DECLARE
@diff int = datediff(MINUTE, @StartDate, @EndDate);
SELECT[days] = (@diff / 1440),
[hour] = (@diff % 1440) / 60,
[min] = (@diff % 1440) % 60;
This will return 4 days, 1 hour, 3 minutes. The last step is to subtract the weekend hours minutes.
Edit: Typo (meant to say minutes)
-- Itzik Ben-Gan 2001
May 19, 2014 at 11:12 am
The best way would be using pre-build calendar table. The following example creates on-fly calendar containing weekend days for 100 years starting from 1 Jan 2000.
declare @sample table (StartDate datetime, EndDate datetime)
insert @sample select '2014-05-15 12:00:00.000', '2014-05-19 13:00:00.000'
insert @sample select '2014-05-16 12:00:00.000', '2014-05-19 13:00:00.000'
insert @sample select '2014-05-16 15:12:00.000', '2014-05-19 13:00:00.000'
insert @sample select '2014-05-19 12:00:00.000', '2014-05-19 13:00:00.000'
insert @sample select '2014-05-19 12:35:00.000', '2014-05-19 13:00:00.000'
;with OnFly100YearsOfWeekends
as
(
select cl.d
from (select DATEADD(day,n.rn,'1 Jan 2000') d
from (select top 400000 row_number() over (order by (select null)) rn
from sys.columns s1, sys.columns s2) n
) cl
where cl.d <= '31 Dec 2100'
and datename(weekday, cl.d) in ('Sunday', 'Saturday')
)
select q.*, q.diffInMinutes/1440 as [Days], (q.diffInMinutes%1440)/60 as [Hours], (q.diffInMinutes%60) as [Minutes]
from (
select s.StartDate, s.EndDate, datediff(minute,StartDate, enddate) - (wdc.WeekendDays * 24 * 60) diffInMinutes
from @sample s
outer apply (select count(*) as WeekendDays from OnFly100YearsOfWeekends where d between s.StartDate and s.EndDate) wdc
) q
Please note:
1. It is a simple solution working on assumption that your start and end days are never weekend days, so it always removes exact number of minutes for the whole weekend day - 2 * 24 hours * 60 minutes.
2. It is not aware of holidays
3. You are using English day names
If your start and end days can be weekend days themselves, the code will require to calculate datediff in minute for each period intersecting with weekend.
May 19, 2014 at 5:04 pm
Here's another version. It uses no extra tables and makes no assumptions: start date and/or end date can be on a weekend, and all SQL settings, including language, are irrelevant.
declare @sample table (StartDate datetime, EndDate datetime)
insert @sample select '2014-05-15 12:00:00.000', '2014-05-19 13:00:00.000'
insert @sample select '2014-05-16 12:00:00.000', '2014-05-19 13:00:00.000'
insert @sample select '2014-05-16 15:12:00.000', '2014-05-19 13:00:00.000'
insert @sample select '2014-05-17 15:12:00.000', '2014-05-19 13:00:00.000' --added
insert @sample select '2014-05-18 15:12:00.000', '2014-05-19 13:00:00.000' --added
insert @sample select '2014-05-19 12:00:00.000', '2014-05-19 13:00:00.000'
insert @sample select '2014-05-19 12:35:00.000', '2014-05-19 13:00:00.000'
SELECT
StartDate, EndDate,
total_work_minutes / 1440 AS Days,
total_work_minutes % 1440 / 60 AS Hours,
total_work_minutes % 60 AS Minutes
FROM (
SELECT
StartDate,
EndDate,
--work time for start day
CASE WHEN start_day_of_week >= 5 THEN 0
ELSE DATEDIFF(MINUTE, StartDate, CASE WHEN total_days_worked = 1 THEN EndDate
ELSE DATEADD(DAY, 1, StartDate_midnight) END) END
--AS start_day_minutes,
+
--work time for end day
CASE WHEN end_day_of_week >= 5 OR total_days_worked = 1 THEN 0
ELSE DATEDIFF(MINUTE, EndDate_midnight, EndDate) END
--AS end_day_minutes,
+
--work time for whole weeks worked
whole_weeks_worked * 5 * 24 * 60
--AS whole_weeks_minutes,
+
--work time for inbetween days worked, subtracting for Sat and/or Sun, if applicable
inbetween_days_worked * 24 * 60 -
CASE WHEN inbetween_days_worked = 0 THEN 0
WHEN start_day_of_week = day_of_week_Sat OR (start_day_of_week + 1 + inbetween_days_worked) = day_of_week_Sat
THEN 24 * 60
WHEN (start_day_of_week + 1 + inbetween_days_worked) >= day_of_week_Sun THEN 2 * 24 * 60
ELSE 0
END
--AS inbetween_minutes
AS total_work_minutes
FROM @sample
CROSS APPLY (
SELECT
DATEADD(DAY, DATEDIFF(DAY, 0, StartDate), 0) AS StartDate_midnight,
DATEADD(DAY, DATEDIFF(DAY, 0, EndDate), 0) AS EndDate_midnight,
DATEDIFF(DAY, 0, StartDate) % 7 AS start_day_of_week,
DATEDIFF(DAY, 0, EndDate) % 7 AS end_day_of_week,
5 AS day_of_week_Sat,
6 AS day_of_week_Sun,
DATEDIFF(DAY, StartDate, EndDate) + 1 AS total_days_worked
) AS calculated_columns_1
CROSS APPLY (
SELECT
CASE WHEN total_days_worked < 9 THEN 0 ELSE (total_days_worked - 3) / 7 END AS whole_weeks_worked,
CASE WHEN total_days_worked < 3 THEN 0 ELSE (total_days_worked - 2) % 7 END AS inbetween_days_worked
) AS calculated_columns_2
) AS derived
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 20, 2014 at 6:53 am
If ignoring weekends, is there any consideration for normal business hours?
For example, 8am to 6pm are normal business hours.
If start is 4:30 pm on Thursday, and end is 9:15am on Monday, what is the expected result?
1.5 + 10 + 1.25 is vastly different than 7.5 + 24 +9.25 (hours).
Part of the answer is how the data is being used.
Then you also can have Holidays......
May 20, 2014 at 8:34 am
Thanks for all your responses!
The ideal way as stated would be a using a calendar table.
For Info: Working hours are not taken into consideration, only weekends need to be discounted (and holidays)
Scott's code seems to be working great for what I need.
I was also passed the following code from a colleague which appears to be working fine as long as the start date is not on a weekend.
declare @sample table (Starttime datetime, EndTime datetime)
insert @sample select '2014-05-15 12:00:00.000', '2014-05-19 13:00:00.000'
insert @sample select '2014-05-16 12:00:00.000', '2014-05-19 13:00:00.000'
insert @sample select '2014-05-16 15:12:00.000', '2014-05-19 13:00:00.000'
insert @sample select '2014-05-17 15:12:00.000', '2014-05-19 13:00:00.000' --added
insert @sample select '2014-05-18 15:12:00.000', '2014-05-19 13:00:00.000' --added
insert @sample select '2014-05-19 12:00:00.000', '2014-05-19 13:00:00.000'
insert @sample select '2014-05-19 12:35:00.000', '2014-05-19 13:00:00.000'
insert @sample select '2014-05-15 12:35:00.000', '2014-05-21 13:00:00.000'
insert @sample select '2014-05-16 12:35:00.000', '2014-05-20 13:01:00.000'
insert @sample select '2014-05-13 11:35:00.000', '2014-05-20 13:01:00.000'
SELECT *, CASE
WHEN DATEDIFF(WK, STARTTIME, ENDTIME) = 1
THEN RIGHT('0' + CONVERT(VARCHAR,DATEDIFF(MINUTE,STARTTIME,DATEADD(HOUR,-(DATEDIFF(WK, STARTTIME, ENDTIME)*48),ENDTIME))/ 60),2) + ':' + RIGHT('0' + CONVERT(VARCHAR,DATEDIFF(MINUTE,STARTTIME,DATEADD(HOUR,-48,ENDTIME)) % 60),2)
ELSE RIGHT('0' + CONVERT(VARCHAR,DATEDIFF(MINUTE,STARTTIME,ENDTIME)/ 60),2) + ':' + RIGHT('0' + CONVERT(VARCHAR,DATEDIFF(MINUTE,STARTTIME,ENDTIME) % 60),2)
END
FROM @sample
ORDER BY ENDTIME DESC
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply