SQL to find gaps in date/time series table

I have a table which logs employee phone calls namely "start of call" and duration. I need to be able to find and list all instances where the employee was NOT on the phone for X minutes (let's say X=30). Only calls between 9AM and 4PM should be considered. The main purpose of the query is to identify all the 30 +minute blocks of time where there was no phone activity.

Sample of CALLS table:

CALL_DATE             CALL_DURATION_SECONDS

3/1/12 09:00AM        3690
3/1/12 10:19AM         400
3/1/12 10:39AM         332

The output of the query or code would list the start time of the each "GAP"  and the duration of the gap. There can be multiple gaps in one business day for the employee.

This solution can either be an MS Access code/query or SQL Server t-SQL
Louis CapeceAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
Is your intent to do this for a particular employee/date combination, or do you want to select a single employee and return results for multiple days?

So, for the data you provided, there would be no records returned, because 3690 = 1 hr, 90 seconds which only gives you a gap of 8 minutes between the end of that call and the beginning of the next call, correct?

Do you want to start the 30 minute period at the end of each call?  Would it make more sense to identify all of the gaps, and the duration of the gap where the gap exceeds 30 minutes.

I would start out with a query that looks like:

SELECT T1.Caller
           , T1.CALL_DATE
           , DateAdd("s", T1.CALL_DURATION_SECONDS, T1.[Call_Date]) as CallEnd
           , MIN(T2.[Call_Date]) as NextCallStart
FROM yourTable T1
LEFT JOIN yourTable T2
ON T1.Caller = T2.Caller
AND T2.Call_Date > DateAdd("s", T1.CALL_DURATION_SECONDS, T1.[Call_Date])
GROUP BY T1.Caller
                 , T1.Call_Date
                 , DateAdd("s", T1.CALL_DURATION_SECONDS, T1.[Call_Date])
HAVING DateDiff("n", DateAdd("s", T1.CALL_DURATION_SECONDS, T1.[Call_Date]), T2.[Call_Date]) > 30

This should get you each caller, when their call started, and the beginning of the subsequent call, and filter out those records where the span between the end of one call and the beginning of the next exceeds 30 minutes.
Louis CapeceAuthor Commented:
Fyed,,
Thanks for the reply -

The sample data was arbitrary, mainly for example of format/fields..

The end goal is to find people who have spans of at least 30 mins "not on the phone".
so the gap would start at the "end of call" (call time + duration). The GAP "timer" starts when the call is terminated and ends when a subsequent call is made.

for example, if Emp has a 35 minute gap between 10am and 12pm, as well as a 62 min gap between 2pm and 4pm, the query output would ideally look like this example:

10:12AM   GAP: 35 Min
2:22PM GAP: 62 Min

The CALLS table has Emp ID, so I would be great if   "all" empl's could be output in the query.
Dale FyeOwner, Developing Solutions LLCCommented:
If you can provide a database with some sample data (all I need is this one table), that would make the process of writing the query easier.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Anthony PerkinsCommented:
Exactly, if you want a quick turnaround, consider posting a SQL Script that includes a CREATE TABLE and an INSERT(s) to provide enough sample data.
ThomasianCommented:
DECLARE @CALLS TABLE (EMP_ID int, CALL_DATE datetime, CALL_DURATION_SECONDS int)

INSERT @CALLS
SELECT 1, '3/1/12 09:00AM', 3690
UNION ALL SELECT 1, '3/1/12 10:19AM', 400
UNION ALL SELECT 1, '3/1/12 10:39AM', 332
UNION ALL SELECT 1, '3/1/12 12:39PM', 332

SELECT
	EMP_ID
	,C.END_CALL
	,N.CALL_DATE
FROM
	(SELECT *, END_CALL = DATEADD(second, CALL_DURATION_SECONDS, CALL_DATE)
	FROM
		@CALLS
	) C CROSS APPLY
	(SELECT TOP 1 CALL_DATE
	FROM @CALLS
	WHERE EMP_ID = C.EMP_ID AND CALL_DATE > C.CALL_DATE
	ORDER BY CALL_DATE
	) N
WHERE
	DATEDIFF(MINUTE,C.END_CALL,N.CALL_DATE)>30
	AND CONVERT(varchar(2),C.END_CALL,108) BETWEEN 9 AND 16

Open in new window

lwadwellCommented:
my take
DECLARE @CALLS TABLE (EMP_ID int, CALL_DATE datetime, CALL_DURATION_SECONDS int)

INSERT @CALLS
SELECT 1, '3/1/12 09:00AM', 3690
UNION ALL SELECT 1, '3/1/12 10:19AM', 400
UNION ALL SELECT 1, '3/1/12 10:39AM', 332
UNION ALL SELECT 1, '3/1/12 12:39PM', 332
UNION ALL SELECT 1, '3/2/12 11:00AM', 800
UNION ALL SELECT 1, '3/2/12 12:30PM', 300
UNION ALL SELECT 1, '3/2/12 15:00PM', 500
UNION ALL SELECT 1, '3/2/12 16:01PM', 50
UNION ALL SELECT 1, '3/2/12 17:00PM', 50

;WITH common_list as (
SELECT emp_id, call_date, call_duration_seconds, DATEADD(ss , call_duration_seconds, call_date) call_end
     , DATEPART(yy, call_date) yr, DATEPART(dy, call_date) dy
     , row_number()over(partition by emp_id, DATEPART(yy, call_date), DATEPART(dy, call_date) order by call_date) rn
FROM @CALLS
WHERE DATEPART(hh, call_date) BETWEEN 9 and 15
)
SELECT cl1.emp_id, cl1.call_date, cl1.call_duration_seconds, cl1.call_end
     , cl2.call_date next_call_start, DATEDIFF(mi, cl1.call_end, cl2.call_date) gap
FROM common_list cl1
     JOIN common_list cl2 ON cl1.yr = cl2.yr AND cl1.dy = cl2.dy AND cl1.rn+1 = cl2.rn
WHERE DATEDIFF(mi, cl1.call_end, cl2.call_date) > 30

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.