asked on

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

Dale Fye

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 Capece

ASKER

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 Fye

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.

Anthony Perkins

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.

Thomasian

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

ASKER CERTIFIED SOLUTION

Lee Wadwell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial