A Scenario to Ponder #13
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
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.
