February 28, 2012 at 6:04 am
I've been scratching my head on this one for quite a bit, basically trying to calculate how long it takes someone to log a response to a job really. For example:
Job given: 27-Feb-12 9:30am
Job first response: 28-Feb-12 11.00am
Response time 1day 1hour 30mins (or 25.5 hours) etc.
The output format as such hasn't been decided so it's not like I need it to reflect either of the above. It's going to be in a report as a 'kpi' if you like so at a glance someone can isolate things out of the ordinary or something.
I can see a simple date diff allows me to return hours, days, mins etc, however, this doesn't take account of any weekends. If the above was:
Job given: 24-Feb-12 5:00pm
Job first response: 28-Feb-12 11.00am
Then I'd expect the response time to read 7 hours (for the Friday), 24hours (for the Monday) and 11 hours (for the Tuesday). i.e. 42 hours or 1day 18 hours.
I've taken a look at a few similar solutions and nothing seems to do quite what I'm after - for example I'm not especially interested in working hours, many may think that Friday should only count for half an hour or so, this is not the case.
I've seen that a calendar table which indicates 'work day and non work day' as a bit field or similar are often incorporated into these solutions, that would be okay, I could then extend this for say Christmas, Easter, Bank Holidays etc. (That's not the main focus, a nice side benefit!).
Any pointers anyone? (I should say that in 99% of cases both dates will occur on weekdays (not necessarily within the same week), but there are rare chances where the response date might fall on a weekend day).
February 28, 2012 at 6:10 am
yeah a Calendar table is definitely the way to go.
I've posted one here that is one of the better versions out there...it features all the funcitons to generate US Holidays, Daylight savings, lunar phase and a lot more.
TallyCalendar_Complete_With_DST.txt
with that in place, you can take into account workdays and holidays like you need to do.
Lowell
February 28, 2012 at 6:19 am
for your specific quesiton, i'd add one more calculated column to make life easy:
--IsWorkDay?
ALTER TABLE TallyCalendar
ADD IsWorkDay AS CASE WHEN IsWorkHoliday = 1 OR DayOfWeek IN('Saturday','Sunday') THEN 0 ELSE 1 END PERSISTED
Lowell
February 28, 2012 at 6:20 am
That's a very comprehensive calendar table! Thanks for the linkage. The calendar table isn't so much the issue, how I use it is the issue!... I could knock up a really dirty one that just had say everything from 2012 thus far with me indicating weekends, I just don't get how I'd apply it...
Whilst waiting for a response I tried something else a little odd/dirty... calculating the week number of the two dates, if it's the same week number then it's fine to do a datediff or whatever, if it's 1 greater then 48 hours should be subtracted, etc. With some modification it would work okay ish... but that's just because I don't get how I'd use the calendar table in this instance
February 28, 2012 at 6:31 am
Rob-350472 (2/28/2012)
That's a very comprehensive calendar table! Thanks for the linkage. The calendar table isn't so much the issue, how I use it is the issue!... I could knock up a really dirty one that just had say everything from 2012 thus far with me indicating weekends, I just don't get how I'd apply it...Whilst waiting for a response I tried something else a little odd/dirty... calculating the week number of the two dates, if it's the same week number then it's fine to do a datediff or whatever, if it's 1 greater then 48 hours should be subtracted, etc. With some modification it would work okay ish... but that's just because I don't get how I'd use the calendar table in this instance
ok how to use it is the easy part. you just need to provide a bit more detail.
show me any query you would use on your table that has the start and end date.
we can then adjust it to join on the tally table to get the calculation you are looking for.
crappy example using sys.tables)
/*
WorkDays(No column name)
5317677490
*/
SELECT
SUM((IsWorkDay)) As WorkDays,
COUNT(*) TotalDays
from TallyCalendar
LEFT OUTER JOIN (
select MIN(create_date) mindate,MAX(create_date)maxdate from sys.tables
)myAlias
ON TallyCalendar.TheDate BETWEEN myAlias.mindate and myAlias.maxdate
Lowell
February 28, 2012 at 8:02 am
Okay, well below is a table I've just knocked up, using real dates in the query I have, it should illustrate the point - some responses are within an hour or two, some can be over a weekend, some even longer. The code after shows the sort of progress I was making with my crude 48 hours per weekend adjustment factor!
CREATE Table tmp_FindTimePeriod
(TaskID int, AssignedDate datetime, FirstContact datetime)
INSERT INTO tmp_FindTimePeriod values (1, '2012-02-03 10:16:47.410','2012-02-08 09:53:01.507')
INSERT INTO tmp_FindTimePeriod values (2, '2012-02-08 15:02:45.137','2012-02-08 16:38:12.540')
INSERT INTO tmp_FindTimePeriod values (3, '2012-02-06 22:03:28.813','2012-02-08 16:01:07.887')
INSERT INTO tmp_FindTimePeriod values (4, '2012-02-03 20:29:09.897','2012-02-08 11:03:58.387')
INSERT INTO tmp_FindTimePeriod values (5, '2012-02-02 19:18:59.680','2012-02-08 15:47:15.367')
INSERT INTO tmp_FindTimePeriod values (6, '2012-01-27 16:11:48.807','2012-02-08 11:08:03.877')
SELECT *,
DATEDIFF(hh, AssignedDate,FirstContact)*1.00 as HoursTakenToRespond,
DATEDIFF(hh, AssignedDate,FirstContact)*1.00-(DATEPART(wk, FirstContact)-DATEPART(wk, AssignedDate))*48 as HoursTakenToRespond_Ajusted
FROM tmp_FindTimePeriod
So, I guess I'd be looking for output such as hours and mins to respond or days, hours mins to respond. That sort of thing. I'm thinking the output I want isn't especially important at this stage.
March 6, 2012 at 6:09 am
Can anyone give me any pointers on how I'd use a calendar table as suggested to accomplish this?
I can see how I could use one to identify the number of business days within a given set of two dates, however, it's less clear how I could use this in time difference calculations to ignore days of specific number/name?
Otherwise all I can think of is my crude 48 hour reduction based on week number of start and end datetime ranges.
Thanks!
March 6, 2012 at 6:43 am
oops, can see your try now...
Comments removed.
March 6, 2012 at 7:01 am
Here how you can do it with calendar table:
-- just small calendar table which has week_day's for 2012
-- YOU WILL BE BETTER USING A PROPER ONE!
select cdate, datepart(weekday,cdate) week_day_no, DATENAME(weekday,cdate) week_day
into #calendar
from
(select top 366 dateadd(d,row_number() over (order by (select null))-1,cast('20120101' as datetime)) as cdate
from sys.columns ) d
-- for each task we will get number of weekend days * 24hours
-- which gives as weekend hour's ajuustment
;with cte_we_ajustment
as
(
SELECT t.TaskID, count(*) * 24 WeekendHours
FROM #calendar c
join tmp_FindTimePeriod t
on cdate between t.AssignedDate and t.FirstContact
where c.week_day in ('Saturday','Sunday')
group by t.TaskID
)
-- so the final query:
SELECT *,
DATEDIFF(hh, AssignedDate,FirstContact)*1.00 as HoursTakenToRespond,
DATEDIFF(hh, AssignedDate,FirstContact)*1.00 - ISNULL(a.WeekendHours, 0) as HoursTakenToRespond_Ajusted
FROM tmp_FindTimePeriod t
LEFT JOIN cte_we_ajustment a ON a.TaskID = t.TaskID
But I cannot see what is wrong with your query, it will do the same job most likely faster...
Also:
What about if FirstContact happens on weekend?
What about state holidays?
March 6, 2012 at 7:48 am
Ah, I see, this makes sense now, so I was sort of on the right lines, so I'd use the Calendar table to build up a collection of exclusion days, so with Easter coming up soon I would count all days which were 'Saturday', 'Sunday' - or have a calendar table with bit fields for weekend, holiday etc.
This gives me the same non work day x24 hour adjustment period but allows me to be a little more accurate in the cases of public holidays and the like.
Thanks, I think that query and example just unlocked the potential of it all to me 🙂
Thanks!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply