[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1351
  • Last Modified:

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
0
Louis Capece
Asked:
Louis Capece
1 Solution
 
Dale FyeCommented:
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.
0
 
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.
0
 
Dale FyeCommented:
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.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

0
 
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

0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now