Comments on SQL Garbage Collector: A Scenario to Ponder #13

Thanks for the nice info
Regards,
Jack

2008-01-06T01:33:00.000-05:00
Thanks for the nice info
Regards,
Jack
http://db2examples.googlepages.com/

what do u think?

SELECT svcCallNo, Actu...

2007-12-28T08:49:00.000-05:00

what do u think?

SELECT 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

Hi Hugo,
There was no need to comment the co...

2007-10-27T11:36:00.000-04:00

Hi Hugo,
There 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

Hi Omnibuzz,

Sorry for not catching...

2007-10-24T16:30:00.000-04:00

Hi Omnibuzz,

>>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).

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES ...

2007-10-16T16:00:00.000-04:00

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='ServiceCalls')
DROP 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

That was fast. I never thought I would get an answ...

2007-10-15T20:58:00.000-04:00

That was fast. I never thought I would get an answer for atleast a week. Both the solutions are overwhelming in different senses.

Changos, 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

Hi Omnibuzz,

Good to see you blogging again...

2007-10-10T18:10:00.000-04:00

Hi Omnibuzz,

Good 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;

I don't even know why I wasted so much time on thi...

2007-10-09T17:38:00.000-04:00

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.

select 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

Comments on SQL Garbage Collector: A Scenario to Ponder #13

Thanks for the nice infoRegards,Jack 2008-01-06T01:33:00.000-05:00 Thanks for the nice infoRegards,Jackhttp://db2examples.googlepages.com/

what do u think?SELECT svcCallNo, Actu...

Hi Hugo, There was no need to comment the co...

Hi Omnibuzz,Sorry for not catching...