Last February, I had come over to Atlanta, Georgia from India for a new project and had been quite overwhelmed by the new way of life, the work and the country that I wasn't able to do justice to this blog. I apologize for those who had been following this mini series of scenarios. Hope I can get some time now to revive this.
Here goes the scenario:
I have a response/resolution time reporting requirement for a Service Call Management System:
My main table is #ServiceCalls. The table definition goes like this:
create table #ServiceCalls
(
SvcCallNo int,
SvcCallDesc varchar(100),
CreateTime datetime,
ResolutionTime datetime
)
Here is the data for this table
insert into #ServiceCalls
select 4709,'I have the complaint #4709','Oct 9 2007 6:59AM','Oct 10 2007 12:59PM' union all
select 4716,'I have the complaint #4716','Oct 8 2007 12:23PM','Oct 10 2007 6:23PM' union all
select 4685,'I have the complaint #4685','Oct 7 2007 12:04PM','Oct 9 2007 6:04PM' union all
select 4695,'I have the complaint #4695','Oct 6 2007 10:59AM','Oct 9 2007 4:59PM' union all
select 4654,'I have the complaint #4654','Oct 5 2007 10:29AM','Oct 8 2007 4:29PM' union all
select 4692,'I have the complaint #4692','Oct 4 2007 10:00AM','Oct 8 2007 4:00PM' union all
select 4637,'I have the complaint #4637','Oct 3 2007 9:27AM','Oct 8 2007 3:27PM' union all
select 4674,'I have the complaint #4674','Oct 2 2007 1:52PM','Oct 6 2007 7:52PM' union all
select 4689,'I have the complaint #4689','Oct 1 2007 9:36AM','Oct 5 2007 3:36PM' union all
select 4700,'I have the complaint #4700','Oct 1 2007 4:43AM','Oct 4 2007 10:43AM'
With the above table, I need to generate a report having these fields.
SvcCallNo, ActualCreateTime, ActualResolutionTime, Effort
Points to Note:
1. Service calls are worked upon between 9:00 AM and 6:00 PM Monday thru Friday (Working Hours).
2. ActualCreateTime is the closest working time on or after the CreateTime.
For Example, for SvcCallNo 4709, the ActualCreateTime is 'Oct 9 2007 9:00AM'
3. ActualResolutionTime is the closest working time on or before the ResolutionTimeFor Example, for SvcCallNo 4716 the ActualResolutionTime is 'Oct 10 2007 6:00PM'
4. Effort is the number of working hours between the actualCreateTime and ActualResolutionTime
How do I go about writing the query. The solution can be in SQL Server 2000 or 2005.
Hint: You can create a calendar table and solve this (its still a challenge). If you can come up with an efficient query without a calendar table for SQL Server 2000 that would be pure genius.
I don't even know why I wasted so much time on this, but here goes my code. It should work if you have it default that first day of the week is sunday.
ReplyDeleteselect svccallNo
, case when (datepart(dw,createtime) between 2 and 6) and (datepart(HH,createtime) between 9 and 18) then createtime --Normal Days
when datepart(dw,createtime) = 7 then dateadd(HH,9,dateadd(dd,2,convert(Datetime, convert(varchar,year(createtime))+ '-'+ convert(varchar,month(createtime)) + '-' + convert(varchar,day(createtime)))))--saturday
when datepart(dw,createtime) = 1 then dateadd(HH,9,dateadd(dd,1,convert(Datetime, convert(varchar,year(createtime))+ '-'+ convert(varchar,month(createtime)) + '-' + convert(varchar,day(createtime)))))--sunday
when (datepart(dw,createtime) = 6) And (datepart(HH,createtime) > 18) then dateadd(HH,9,dateadd(dd,3,convert(Datetime, convert(varchar,year(createtime))+ '-'+ convert(varchar,month(createtime)) + '-' + convert(varchar,day(createtime)))))--friday night
when (datepart(dw,createtime) in (2,3,4,5,6)) And (datepart(HH,createtime) < 9 ) then dateadd(HH,9,convert(Datetime, convert(varchar,year(createtime))+ '-'+ convert(varchar,month(createtime)) + '-' + convert(varchar,day(createtime))))--weekday morning
when (datepart(dw,createtime) in (2,3,4,5)) And (datepart(HH,createtime) > 18 ) then dateadd(HH,9,dateadd(dd,1,convert(Datetime, convert(varchar,year(createtime))+ '-'+ convert(varchar,month(createtime)) + '-' + convert(varchar,day(createtime)))))--weekday night xcept friday
end as ActualCreateTime
, case when (datepart(dw,ResolutionTime) between 2 and 6) and (datepart(HH,ResolutionTime) between 9 and 18) then ResolutionTime--normal day
when datepart(dw,ResolutionTime) = 7 then dateadd(HH,18,dateadd(dd,-1,convert(Datetime, convert(varchar,year(ResolutionTime))+ '-'+ convert(varchar,month(ResolutionTime)) + '-' + convert(varchar,day(ResolutionTime)))))--saturday
when datepart(dw,ResolutionTime) = 1 then dateadd(HH,18,dateadd(dd,-2,convert(Datetime, convert(varchar,year(ResolutionTime))+ '-'+ convert(varchar,month(ResolutionTime)) + '-' + convert(varchar,day(ResolutionTime)))))--sunday
when (datepart(dw,ResolutionTime) = 2) And (datepart(HH,ResolutionTime) < 9) then dateadd(HH,18,dateadd(dd,-3,convert(Datetime, convert(varchar,year(ResolutionTime))+ '-'+ convert(varchar,month(ResolutionTime)) + '-' + convert(varchar,day(ResolutionTime)))))--monday morning
when (datepart(dw,ResolutionTime) in (2,3,4,5,6)) And (datepart(HH,ResolutionTime) > 18 ) then dateadd(HH,18,convert(Datetime, convert(varchar,year(ResolutionTime))+ '-'+ convert(varchar,month(ResolutionTime)) + '-' + convert(varchar,day(ResolutionTime))))--weekday nights
when (datepart(dw,ResolutionTime) in (3,4,5,6)) And (datepart(HH,ResolutionTime) < 9 ) then dateadd(HH,18,dateadd(dd,-1,convert(Datetime, convert(varchar,year(ResolutionTime))+ '-'+ convert(varchar,month(ResolutionTime)) + '-' + convert(varchar,day(ResolutionTime)))))--weekday morning xcept monday
end as ActualResolutionTime
, (floor(datediff(HH,
case when (datepart(dw,createtime) between 2 and 6) and (datepart(HH,createtime) between 9 and 18) then createtime
when datepart(dw,createtime) = 7 then dateadd(HH,9,dateadd(dd,2,convert(Datetime, convert(varchar,year(createtime))+ '-'+ convert(varchar,month(createtime)) + '-' + convert(varchar,day(createtime)))))
when datepart(dw,createtime) = 1 then dateadd(HH,9,dateadd(dd,1,convert(Datetime, convert(varchar,year(createtime))+ '-'+ convert(varchar,month(createtime)) + '-' + convert(varchar,day(createtime)))))
when (datepart(dw,createtime) = 6) And (datepart(HH,createtime) > 18) then dateadd(HH,9,dateadd(dd,3,convert(Datetime, convert(varchar,year(createtime))+ '-'+ convert(varchar,month(createtime)) + '-' + convert(varchar,day(createtime)))))
when (datepart(dw,createtime) in (2,3,4,5,6)) And (datepart(HH,createtime) < 9 ) then dateadd(HH,9,convert(Datetime, convert(varchar,year(createtime))+ '-'+ convert(varchar,month(createtime)) + '-' + convert(varchar,day(createtime))))
when (datepart(dw,createtime) in (2,3,4,5)) And (datepart(HH,createtime) > 18 ) then dateadd(HH,9,dateadd(dd,1,convert(Datetime, convert(varchar,year(createtime))+ '-'+ convert(varchar,month(createtime)) + '-' + convert(varchar,day(createtime)))))
end,
case when (datepart(dw,ResolutionTime) between 2 and 6) and (datepart(HH,ResolutionTime) between 9 and 18) then ResolutionTime
when datepart(dw,ResolutionTime) = 7 then dateadd(HH,18,dateadd(dd,-1,convert(Datetime, convert(varchar,year(ResolutionTime))+ '-'+ convert(varchar,month(ResolutionTime)) + '-' + convert(varchar,day(ResolutionTime)))))
when datepart(dw,ResolutionTime) = 1 then dateadd(HH,18,dateadd(dd,-2,convert(Datetime, convert(varchar,year(ResolutionTime))+ '-'+ convert(varchar,month(ResolutionTime)) + '-' + convert(varchar,day(ResolutionTime)))))
when (datepart(dw,ResolutionTime) = 2) And (datepart(HH,ResolutionTime) < 9) then dateadd(HH,18,dateadd(dd,-3,convert(Datetime, convert(varchar,year(ResolutionTime))+ '-'+ convert(varchar,month(ResolutionTime)) + '-' + convert(varchar,day(ResolutionTime)))))
when (datepart(dw,ResolutionTime) in (2,3,4,5,6)) And (datepart(HH,ResolutionTime) > 18 ) then dateadd(HH,18,convert(Datetime, convert(varchar,year(ResolutionTime))+ '-'+ convert(varchar,month(ResolutionTime)) + '-' + convert(varchar,day(ResolutionTime))))
when (datepart(dw,ResolutionTime) in (3,4,5,6)) And (datepart(HH,ResolutionTime) < 9 ) then dateadd(HH,18,dateadd(dd,-1,convert(Datetime, convert(varchar,year(ResolutionTime))+ '-'+ convert(varchar,month(ResolutionTime)) + '-' + convert(varchar,day(ResolutionTime)))))
end
)/24.0)*9) --regular 9 hour day
+ (18 - datepart(HH,
case when (datepart(dw,createtime) between 2 and 6) and (datepart(HH,createtime) between 9 and 18) then createtime
when datepart(dw,createtime) = 7 then dateadd(HH,9,dateadd(dd,2,convert(Datetime, convert(varchar,year(createtime))+ '-'+ convert(varchar,month(createtime)) + '-' + convert(varchar,day(createtime)))))
when datepart(dw,createtime) = 1 then dateadd(HH,9,dateadd(dd,1,convert(Datetime, convert(varchar,year(createtime))+ '-'+ convert(varchar,month(createtime)) + '-' + convert(varchar,day(createtime)))))
when (datepart(dw,createtime) = 6) And (datepart(HH,createtime) > 18) then dateadd(HH,9,dateadd(dd,3,convert(Datetime, convert(varchar,year(createtime))+ '-'+ convert(varchar,month(createtime)) + '-' + convert(varchar,day(createtime)))))
when (datepart(dw,createtime) in (2,3,4,5,6)) And (datepart(HH,createtime) < 9 ) then dateadd(HH,9,convert(Datetime, convert(varchar,year(createtime))+ '-'+ convert(varchar,month(createtime)) + '-' + convert(varchar,day(createtime))))
when (datepart(dw,createtime) in (2,3,4,5)) And (datepart(HH,createtime) > 18 ) then dateadd(HH,9,dateadd(dd,1,convert(Datetime, convert(varchar,year(createtime))+ '-'+ convert(varchar,month(createtime)) + '-' + convert(varchar,day(createtime)))))
end
))--the piece before the end of the day
+ (datepart(HH,
case when (datepart(dw,ResolutionTime) between 2 and 6) and (datepart(HH,ResolutionTime) between 9 and 18) then ResolutionTime
when datepart(dw,ResolutionTime) = 7 then dateadd(HH,18,dateadd(dd,-1,convert(Datetime, convert(varchar,year(ResolutionTime))+ '-'+ convert(varchar,month(ResolutionTime)) + '-' + convert(varchar,day(ResolutionTime)))))
when datepart(dw,ResolutionTime) = 1 then dateadd(HH,18,dateadd(dd,-2,convert(Datetime, convert(varchar,year(ResolutionTime))+ '-'+ convert(varchar,month(ResolutionTime)) + '-' + convert(varchar,day(ResolutionTime)))))
when (datepart(dw,ResolutionTime) = 2) And (datepart(HH,ResolutionTime) < 9) then dateadd(HH,18,dateadd(dd,-3,convert(Datetime, convert(varchar,year(ResolutionTime))+ '-'+ convert(varchar,month(ResolutionTime)) + '-' + convert(varchar,day(ResolutionTime)))))
when (datepart(dw,ResolutionTime) in (2,3,4,5,6)) And (datepart(HH,ResolutionTime) > 18 ) then dateadd(HH,18,convert(Datetime, convert(varchar,year(ResolutionTime))+ '-'+ convert(varchar,month(ResolutionTime)) + '-' + convert(varchar,day(ResolutionTime))))
when (datepart(dw,ResolutionTime) in (3,4,5,6)) And (datepart(HH,ResolutionTime) < 9 ) then dateadd(HH,18,dateadd(dd,-1,convert(Datetime, convert(varchar,year(ResolutionTime))+ '-'+ convert(varchar,month(ResolutionTime)) + '-' + convert(varchar,day(ResolutionTime)))))
end
) - 9) --the piece after the start of the day
as Effort
from #serviceCalls
Hi Omnibuzz,
ReplyDeleteGood to see you blogging again!
Here's a query that will work in all versions of SQL, using no calendar table and only a single table scan. I do hope that formatting is retained...
BTW, you never specified how parts of hours have to be handled. I added test cases for this, but did not try to correct the results as I have no idea what results you want.
BTW2, I also added some more interesting test data.
Here's the query:
-- Add some extra test data
insert into #ServiceCalls
select 4444,'This one spans more than a single week','2007-10-01T04:43:00', '2007-10-14T10:43:00'
union all
select 4445,'Opened after 6 PM, closed before 9 AM','2007-10-03T21:43:00','2007-10-05T05:43:00'
union all
select 4446,'Opened friday evening, closed monday morning','2007-10-05T21:43:00','2007-10-08T05:43:00'
union all
select 4447,'Testcase 1 for rounding of hours and minutes','2007-10-05T09:05:00','2007-10-05T17:55:00'
union all
select 4448,'Testcase 2 for rounding of hours and minutes','2007-10-05T09:55:00','2007-10-05T17:05:00'
go
SELECT SvcCallNo, ActualCreateTime, ActualResolutionTime,
DATEDIFF(hh, ActualCreateTime, ActualResolutionTime)
- DATEDIFF(dd, ActualCreateTime, ActualResolutionTime) * 15
- DATEDIFF(ww, ActualCreateTime, ActualResolutionTime) * 18 AS Effort
FROM
(SELECT SvcCallNo,
CASE DATENAME(dw, CreateTimeBusinessHours)
WHEN 'Saturday'
THEN DATEADD(dd, DATEDIFF(dd, -2, CreateTimeBusinessHours), '09:00:00')
WHEN 'Sunday'
THEN DATEADD(dd, DATEDIFF(dd, -1, CreateTimeBusinessHours), '09:00:00')
ELSE CreateTimeBusinessHours
END AS ActualCreateTime,
CASE DATENAME(dw, ResolutionTimeBusinessHours)
WHEN 'Sunday'
THEN DATEADD(dd, DATEDIFF(dd, 2, ResolutionTimeBusinessHours), '18:00:00')
WHEN 'Saturday'
THEN DATEADD(dd, DATEDIFF(dd, 1, ResolutionTimeBusinessHours), '18:00:00')
ELSE ResolutionTimeBusinessHours
END AS ActualResolutionTime
FROM
(SELECT SvcCallNo,
CASE
WHEN DATEPART(hh, CreateTime) >= 18
THEN DATEADD(dd, DATEDIFF(dd, -1, CreateTime), '09:00:00')
WHEN DATEPART(hh, CreateTime) < 9
THEN DATEADD(dd, DATEDIFF(dd, 0, CreateTime), '09:00:00')
ELSE CreateTime
END AS CreateTimeBusinessHours,
CASE
WHEN DATEPART(hh, ResolutionTime) < 9
THEN DATEADD(dd, DATEDIFF(dd, 1, ResolutionTime), '18:00:00')
WHEN DATEPART(hh, ResolutionTime) >= 18
THEN DATEADD(dd, DATEDIFF(dd, 0, ResolutionTime), '18:00:00')
ELSE ResolutionTime
END AS ResolutionTimeBusinessHours
FROM #ServiceCalls) AS d1) AS d2;
That was fast. I never thought I would get an answer for atleast a week. Both the solutions are overwhelming in different senses.
ReplyDeleteChangos, Welcome to my blog. I appriciate your effort in coming up with the solution. No offences, but looking at the length, I can never ever understand the logic. But I should say you are fast to give it under 6 hours of posting. I will run some tests and let you know my comments.
And Hugo.. Boy, Am I glad you commented :) Good to hear from you.
Your solution is overwhelming and not possible ( at least not for me yet). I am going to take some time to understand your thought process before I comment. Learn it before I appriciate it :)
-Omni
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='ServiceCalls')
ReplyDeleteDROP TABLE ServiceCalls
GO
create table ServiceCalls
(
SvcCallNo int,
SvcCallDesc varchar(100),
CreateTime datetime,
ResolutionTime datetime
)
insert into ServiceCalls
select 4709,'I have the complaint #4709','Oct 9 2007 6:59AM','Oct 10 2007 12:59PM' union all
select 4716,'I have the complaint #4716','Oct 8 2007 12:23PM','Oct 10 2007 6:23PM' union all
select 4685,'I have the complaint #4685','Oct 7 2007 12:04PM','Oct 9 2007 6:04PM' union all
select 4695,'I have the complaint #4695','Oct 6 2007 10:59AM','Oct 9 2007 4:59PM' union all
select 4654,'I have the complaint #4654','Oct 5 2007 10:29AM','Oct 8 2007 4:29PM' union all
select 4692,'I have the complaint #4692','Oct 4 2007 10:00AM','Oct 8 2007 4:00PM' union all
select 4637,'I have the complaint #4637','Oct 3 2007 9:27AM','Oct 8 2007 3:27PM' union all
select 4674,'I have the complaint #4674','Oct 2 2007 1:52PM','Oct 6 2007 7:52PM' union all
select 4689,'I have the complaint #4689','Oct 1 2007 9:36AM','Oct 5 2007 3:36PM' union all
select 4700,'I have the complaint #4700','Oct 1 2007 4:43AM','Oct 4 2007 10:43AM' ;
WITH WorkingHours
AS ( SELECT SvcCallNo,
CreateTime,
ResolutionTime,
ActualCreateTime = CASE WHEN DATEPART(dw, CreateTime) = 1 THEN -- sunday
DATEADD(hh, 9, CAST(FLOOR(CAST(DATEADD(d, 1, CreateTime) AS FLOAT)) AS DATETIME))
WHEN DATEPART(dw, CreateTime) = 7 THEN -- saturday
DATEADD(hh, 9, CAST(FLOOR(CAST(DATEADD(d, 2, CreateTime) AS FLOAT)) AS DATETIME))
ELSE CASE WHEN DATEPART(hh, CreateTime) < 9 THEN DATEADD(hh, 9, CAST(FLOOR(CAST(CreateTime AS FLOAT)) AS DATETIME))
WHEN DATEPART(hh, CreateTime) >= 18 THEN DATEADD(hh, 33, CAST(FLOOR(CAST(CreateTime AS FLOAT)) AS DATETIME))
ELSE CreateTime
END
END,
ActualResolutionTime = CASE WHEN DATEPART(dw, ResolutionTime) = 1 THEN -- sunday
DATEADD(hh, 18, CAST(FLOOR(CAST(DATEADD(d, -2, ResolutionTime) AS FLOAT)) AS DATETIME))
WHEN DATEPART(dw, ResolutionTime) = 7 THEN -- saturday
DATEADD(hh, 18, CAST(FLOOR(CAST(DATEADD(d, -1, ResolutionTime) AS FLOAT)) AS DATETIME))
ELSE CASE WHEN DATEPART(hh, ResolutionTime) >= 18 THEN DATEADD(hh, 18, CAST(FLOOR(CAST(ResolutionTime AS FLOAT)) AS DATETIME))
ELSE ResolutionTime
END
END
FROM ServiceCalls
) ,
WorkingHoursDiff
AS ( SELECT SvcCallNo,
CreateTime,
ResolutionTime,
ActualCreateTime,
ActualResolutionTime,
DayCount = DATEDIFF(DAY, ActualCreateTime, ActualResolutionTime) + 1
FROM WorkingHours
) ,
WorkingHoursDiffExceptWeekends
AS ( SELECT SvcCallNo,
CreateTime,
ResolutionTime,
ActualCreateTime,
ActualResolutionTime,
/* TODO insert your work calendar here*/
DayCountExceptWeekends = DayCount / 7 * 5 + DayCount % 7 - ( SELECT COUNT(*)
FROM ( SELECT 1 AS d
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
) weekdays
WHERE d <= DayCount % 7
AND DATENAME(WEEKDAY, ActualResolutionTime - d + 1) IN ( 'Saturday', 'Sunday' )
)
FROM WorkingHoursDiff
)
SELECT SvcCallNo,
CreateTime,
ResolutionTime,
ActualCreateTime,
ActualResolutionTime,
ActualHoursWorked = DayCountExceptWeekends * 9
FROM WorkingHoursDiffExceptWeekends
Hi Omnibuzz,
ReplyDelete>>I am going to take some time to understand your thought process before I comment<<
Sorry for not catching it sooner. And for forgetting to comment my code.
In case you're still studying, the idea is quite simple.
The first step (in the derived table) uses two CASE expressions to roll forward or roll back to beginning or end of next/previous business day (the DATEADD/DATEDIFF trick is well known for stripping off a time portion, but by using different time constants can be used to get a fixed time offset as well). This changes a begin/end datetime outside of business hours to the correct datetime for the calculation.
The second step calculates the number of hours between the "corrected" begin and end datetime, then subtracts 15 hours for each midnight enclosed in the range (since there are 15 non-working hours each day), and then 18 hours for each spanned weekend (for the 2 * 9 remaining hours of the saturday and sunday are not working hours either).
Hi Hugo,
ReplyDeleteThere was no need to comment the code. The test data and the column names were self-explanatory. Your solution was too overwhelming and humbling at the same time. After looking at your solution, mine looks like child play. I learnt a lot from it, a new way of thinking in sets.
I am just leaving your answer as the single best answer to this scenario. Its simple and elegant.
I will be constantly looking at how to improve your answer :)
-Omni
what do u think?
ReplyDeleteSELECT svcCallNo, ActualCreate, ActualEnd, DATEDIFF(hh, ActualCreate, ActualEnd)-
DATEDIFF(dd, ActualCreate, ActualEnd) * 15 - DATEDIFF (ww, ActualCreate, ActualEnd) *18
FROM
(SELECT svccallno,
case WHEN crDow between 1 AND 5
THEN case WHEN datepart(hh, createtime) < 9 then dateadd(hh,9, crdate)
WHEN datepart(hh, createtime) >= 18 then
CASE crDOW WHEN 5 THEN dateadd(hh,81, crdate)
ELSE dateadd(hh,33, crdate) END
ELSE createtime END
ELSE dateadd(hh, (8-crdow)*24+9, crdate) END ActualCreate, createtime,
case WHEN resDow in (6,7) THEN dateadd(hh,(5-resDow)*24+18,resDate)
ELSE
case when datepart(hh, resolutiontime) >= 18 then dateadd(hh,18,resdate)
when datepart(hh, resolutiontime) < 9 then
CASE resDow WHEN 1 then dateadd(hh, -54, resdate)
ELSE dateadd(hh, -6, resdate) END
ELSE resolutiontime END
END ActualEnd, resolutiontime
from
(select svccallNo, createtime, cast(floor(cast(createtime as float)) as datetime) crDate, datepart(dw, createtime) crDow,
ResolutionTime, cast(floor(cast(resolutiontime as float)) as datetime)resDate, datepart(dw, resolutiontime) resDow
FROM #ServiceCalls)A)B