December 2, 2015 at 4:44 pm
,a.CREATED_DATE
,a.CLOSE_DATE
T-SQL, I am trying to get the code to measure network days between those dates. We need the output in Days, but only counting business hours. Our business hours are 8:00am to 5:00pm, for 9 hours a day. So Created at 3PM and closed at 10 am, would be .5 days.
Thoughts? If I can get Network_days in there, that would be great. I can make a temp table called #temp1 to store that years holidays if that helps.
Thanks in advance
December 2, 2015 at 4:52 pm
Hello,
In your example, 3PM to 5PM and then next day 8AM to 10AM = 2 + 2 = 4 Hours (and 4 hours is not exactly 1/2 of 9)
Can you please elaborate further on what exactly you want and what business rules they should abide by.
Aditya Daruka
December 2, 2015 at 4:55 pm
Re:
Hello,
In your example, 3PM to 5PM and then next day 8AM to 10AM = 2 + 2 = 4 Hours
You are correct, I should have said Opens at 3pm and Closes at 10:30am, for 4.5 hours, but we need it to output .5 for 1/2 of a day.
thanks for catching that
December 2, 2015 at 5:39 pm
A recursive solution, but it should be carefully used. Ideally should be used only if start and end date gaps is max few hundred days.
Anyways, here it is and I have not spent time trying to accommodate holidays and hours to day conversion, etc. You should also consider having a calendar tables with working days, working hours, public holidays, etc and should use it instead of recursion
---------------
-- Assumptions
-- Close Date Time >= Open Date Time
-- Close and Open Time should always fall within business hours of 8 AM and 5 PM
---------------
DECLARE @Created_Date DATETIME = '2015-01-01 10:00'
, @Close_Date DATETIME = '2015-01-14 10:00' ;
select datepart(weekday, @Close_Date) ;
WITH dataCTE AS
(
SELECT TodaysDate = CAST(@Created_Date AS DATE)
, TomorrowsStartDateTime = DATEADD(HOUR, 32, CAST(CAST(@Created_Date AS DATE) AS DATETIME))
, OpenHours = CASE
WHEN CAST(@Created_Date AS DATE) = CAST(@Close_Date AS DATE) -- Same Day
THEN DATEDIFF(HOUR, @Created_Date, @Close_Date)
ELSE DATEDIFF(HOUR, @Created_Date, DATEADD(HOUR, 17, CAST(CAST(@Created_Date AS DATE) AS DATETIME))) -- Whole Day
END
UNION ALL
SELECT TodaysDate = CAST(data.TomorrowsStartDateTime AS DATE)
, TomorrowsStartDateTime = DATEADD(HOUR, 32, CAST(CAST(data.TomorrowsStartDateTime AS DATE) AS DATETIME))
, OpenHours = CASE
WHEN CAST(data.TomorrowsStartDateTime AS DATE) = CAST(@Close_Date AS DATE) -- Same Day
THEN DATEDIFF(HOUR, data.TomorrowsStartDateTime, @Close_Date)
ELSE DATEDIFF(HOUR, data.TomorrowsStartDateTime, DATEADD(HOUR, 17, CAST(CAST(data.TomorrowsStartDateTime AS DATE) AS DATETIME))) -- Whole Day
END
FROM dataCTE data
WHERE data.TomorrowsStartDateTime < @Close_Date
)
SELECT data.*
FROM dataCTE data
WHERE DATEPART(WEEKDAY, data.TodaysDate) BETWEEN 2 AND 6 -- Just week days
OPTION (MAXRECURSION 10000) ;
December 2, 2015 at 7:23 pm
Using a calendar table (a form of Tally table having dates instead of numbers) allows both simplify the code and speed up its execution:
DECLARE @Created_Date DATETIME = '2000-12-02 15:00'
, @Close_Date DATETIME = '2015-12-03 10:30' ;
SELECT SUM(Duration_Min) / 60.0 / 9
FROM (
SELECT CASE WHEN cs.Date = DATEADD(dd, DATEDIFF(dd, 0, @Created_Date), 0) THEN DATEDIFF(n, @Created_Date, DATEADD(hh, 17, CONVERT(DATETIME, cs.Date)))
WHEN cs.Date = DATEADD(dd, DATEDIFF(dd, 0, @Close_Date), 0) THEN DATEDIFF(n, DATEADD(hh, 8, CONVERT(DATETIME, cs.Date)), @Close_Date)
ELSE 9*60 END Duration_Min
FROM dbo.CalendarStat cs
WHERE cs.Date >= DATEADD(dd, DATEDIFF(dd, 0, DATEADD(hh, 24-17, @Created_Date) ), 0) -- excluse days when connected after hours
AND cs.Date < DATEADD(hh, -9, @Close_Date) -- exclude days when disconnected before hours
) C
Calculations over any range of dates covered by your Calendar table (my one has all dates from 1900-01-01 to 2076-12-31) takes no time at all.
_____________
Code for TallyGenerator
December 30, 2015 at 9:48 am
First of all, thanks for this; it is very close to what I needed as well. The issue i'm having though is it doesn't seem to count time from 8 am to 9 am.
For example, when I execute this from @Created_Date DATETIME = '2015-12-01 10:00' to @Closed_Date DATETIME = '2015-12-30 10:00', I get 20.000000, exactly as expected.
But when I run it from '2015-12-01 10:00' to '2015-12-30 08:00' I get 19.77777777, and then from '2015-12-01 10:00' to '2015-12-30 09:00' I get the same 19.77777777. It seems its not accounting for the extra hour from 8 am to 9 am on 2015-12-30.
Can you please help clarify why this is happening? From what I can tell your code looks good; I admit I don't fully understand it yet and hopefully this isn't something obvious. Thank you
December 30, 2015 at 10:57 am
ok after reviewing I think the line:
AND [Calendar Date] < DATEADD(hh, -9, @End_Date) -- excludes days when disconnected before hours
should be:
AND [Calendar Date] < DATEADD(hh, -8, @End_Date) -- excludes days when disconnected before hours.
This seems to take care of the issue. Also, I noticed that this takes into account that the created and closed dates will be during business hours. If not, on the created and closed date it will add the extra off business hours time into the count.
This was a really nice solution though regardless. Thanks for providing!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply