Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Finding the voids

Author  Topic 

dadoftwowinds
Starting Member

1 Post

Posted - 2010-05-06 : 17:18:50
Hi, I have a table [PHONE_LOG} that has ID int, StartTime DateTime and StopTime DateTime.

I want to find the all the ranges of time where no one was on the phone -- rounded at the minute and computed only at the day level.

Assume this data:
1, 20010410 4:00:00:000, 20010410 4:30:00:000
2. 20010410 4:15:00:000, 20010410 23:30:00:000

The results should be
20010410 00:00:00:000, 20010410 3:59:00:000
20010410 23:31:00:000, 20010410 23:59:00:000

My brain can't figure this one out.

Gervaise.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-06 : 22:53:23
Try this:

WITH DigitsCTE(digit) AS
(
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
)
, NumbersCTE(number) AS
(
SELECT ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) - 1
FROM DigitsCTE AS D0
CROSS JOIN
DigitsCTE AS D1
)
, MinutesCTE(minute) AS
(
SELECT TOP(60) number
FROM NumbersCTE
)
, HoursCTE(hour) AS
(
SELECT TOP(24) number
FROM NumbersCTE
)
, OneDayMinutesCTE(minute) AS
(
SELECT hour * 60 + minute
FROM HoursCTE AS H
CROSS JOIN
MinutesCTE AS M
)
, PhoneLogDaysCTE AS
(
SELECT date
FROM (SELECT DATEADD(DAY, DATEDIFF(DAY, '20100101', StartTime), '20100101')
FROM dbo.PHONE_LOG

UNION ALL

SELECT DATEADD(DAY, DATEDIFF(DAY, '20100101', StopTime), '20100101')
FROM dbo.PHONE_LOG) AS T(date)
GROUP BY date
)
, PhoneLogVoidsCTE(void_date, group_identifier) AS
(
SELECT D.date, DATEADD(MINUTE, -1 * ROW_NUMBER() OVER(ORDER BY D.date), D.date)
FROM (SELECT DATEADD(MINUTE, ODM.minute, PLD.date)
FROM PhoneLogDaysCTE AS PLD
CROSS JOIN
OneDayMinutesCTE AS ODM) AS D(date)
WHERE NOT EXISTS(SELECT *
FROM PHONE_LOG AS PL
WHERE PL.StartTime <= D.date
AND PL.StopTime >= D.date)
)
SELECT MIN(PLV.void_date) AS start_time, MAX(PLV.void_date) AS end_time
FROM PhoneLogVoidsCTE AS PLV
GROUP BY PLV.group_identifier
ORDER BY start_time, end_time;

And let me know if it works.
Go to Top of Page

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-06 : 23:02:47
I forget to mention that in PhoneLogDaysCTE I have assumed that in your PHONE_LOG table the day of StartTime and the day of StopTime may be different. So if that assumption is not valid which means that the day of StartTime and the day of StopTime are alwayes the same, then you can optimize my query by removing the UNION ALL part from PhoneLogDaysCTE to remove the StopTime select.
Go to Top of Page
   

- Advertisement -