Determine Average Time between dates

Question

Post reply

Determine Average Time between dates

ehlinger

Hall of Fame

Points: 3585
More actions
January 9, 2008 at 12:43 am

#180247

I am trying to figure out how to calculate the average time between phone calls for a user. The initial requirement is to calcualte this on all calls for an entire month but I would guess that would lead to other periods as well, such as daily, weekly, etc. One hurdle is what to do when going from one day to the next. I could possibly just week out any times between calls that are greater than a certain amount of time to address that.

Any way, here is a small sample of what I'll be dealing with. Any ideas on how to approach this or get it to work would be greatly appreciated.

CREATE TABLE #avetime (origdate datetime, duration_seconds int, duration_minutes decimal(9,2), phoneuser varchar(20), calltype varchar(15))
INSERT into #avetime VALUES ('Jan 7 2008 9:19AM', 21, 0.4, 'Coleman', 'Long Distance') INSERT into #avetime VALUES ('Jan 7 2008 9:19AM', 48, 0.8, 'Coleman', 'Long Distance') INSERT into #avetime VALUES ('Jan 7 2008 9:33AM', 81, 1.4, 'Coleman', 'Long Distance') INSERT into #avetime VALUES ('Jan 7 2008 9:35AM', 87, 1.5, 'Coleman', 'Internal') INSERT into #avetime VALUES ('Jan 7 2008 9:37AM', 27, 0.5, 'Coleman', 'Internal') INSERT into #avetime VALUES ('Jan 7 2008 9:42AM', 13, 0.2, 'Coleman', 'Long Distance') INSERT into #avetime VALUES ('Jan 7 2008 9:43AM', 84, 1.6, 'Coleman', 'Long Distance') INSERT into #avetime VALUES ('Jan 7 2008 12:00PM', 914, 15.2, 'Coleman', 'Long Distance') INSERT into #avetime VALUES ('Jan 7 2008 2:24PM', 3, 0.1, 'Coleman', 'Internal') INSERT into #avetime VALUES ('Jan 8 2008 10:13AM', 21, 0.4, 'Coleman', 'Long Distance') INSERT into #avetime VALUES ('Jan 8 2008 10:33AM', 482, 8.0, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 8 2008 11:49AM', 56, 0.9, 'Coleman', 'Long Distance')

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 1

To do what you need you will have to self join on the #avetime table:

select stime.phoneuser, stime.origdate as call_start, etime.origdate as call_end



  from #avetime stime

 inner join avetime etime

    on stime.phoneuser = etime.phoneuser

and etime.origdate = (select top 1 origdate from avetime where origdate > stime.origdate order by origdate)

As you can see, the join is on user and time, however we need to join the two different times, given the data you supplied this meant using a subquery.

Now we can modify that query to get the average time in seconds:

select stime.phoneuser, avg(datediff(ss, stime.origdate, etime.origdate))



  from #avetime stime

 inner join #avetime etime

    on stime.phoneuser = etime.phoneuser

   and etime.origdate = (select top 1 origdate from avetime where origdate > stime.origdate order by origdate)

group by stime.phoneuser

I haven't tested this query with any more data, I suspect that it could be rather slow for large amounts of data.

ehlinger Hall of Fame Points: 3585 More actions · Answer 2

ehlinger

Hall of Fame

Points: 3585

January 13, 2008 at 11:04 pm

#767563

Thanks. This works great. Based on the sample data this does exacltly what I need it to do. Now, to broden the scope a little bit, how could I do the same thing if I had multiple phone users in the data set and wanted to return the average time between calls for each particluar user?

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 3

Samuel Vella

SSCrazy Eights

Points: 8055

January 13, 2008 at 11:54 pm

#767566

test it out the second query a little and see what happens 😉

ehlinger Hall of Fame Points: 3585 More actions · Answer 4

ehlinger

Hall of Fame

Points: 3585

January 15, 2008 at 4:19 pm

#768394

Maybe I'm missing something. Did you change the second query? If so, it didn't take. I've tried some different things with it but haven't had any luck as of yet. Any ideas would be great.

ehlinger Hall of Fame Points: 3585 More actions · Answer 5

By using this revised test data, even when you run the first query from Samuel it is not including everything for Dolan and I can't figure out why.

CREATE TABLE #avetime (origdate datetime, duration_seconds int, duration_minutes decimal(9,2), phoneuser varchar(20), calltype varchar(15))



INSERT into #avetime VALUES ('Jan  7 2008  9:19:00AM', 21, 0.4, 'Coleman', 'Long Distance')

INSERT into #avetime VALUES ('Jan  7 2008  9:19:30AM', 48, 0.8, 'Coleman', 'Long Distance')

INSERT into #avetime VALUES ('Jan  7 2008  9:33AM', 81, 1.4, 'Coleman', 'Long Distance')

INSERT into #avetime VALUES ('Jan  7 2008  9:35AM', 87, 1.5, 'Coleman', 'Internal')

INSERT into #avetime VALUES ('Jan  7 2008  9:37AM', 27, 0.5, 'Coleman', 'Internal')

INSERT into #avetime VALUES ('Jan  7 2008  9:42AM', 13, 0.2, 'Coleman', 'Long Distance')

INSERT into #avetime VALUES ('Jan  7 2008  9:43AM', 84, 1.6, 'Coleman', 'Long Distance')

INSERT into #avetime VALUES ('Jan  7 2008 12:00PM', 914, 15.2, 'Coleman', 'Long Distance')

INSERT into #avetime VALUES ('Jan  7 2008  2:24PM', 3, 0.1, 'Coleman', 'Internal')

INSERT into #avetime VALUES ('Jan  8 2008  10:13AM', 21, 0.4, 'Coleman', 'Long Distance')

INSERT into #avetime VALUES ('Jan  8 2008  10:33AM', 482, 8.0, 'Coleman', 'Long Distance')

INSERT into #avetime VALUES ('Jan  8 2008  11:49AM', 56, 0.9, 'Coleman', 'Long Distance')

INSERT into #avetime VALUES ('Jan  8 2008  9:15AM', 60, 1, 'Dolan', 'Long Distance')

INSERT into #avetime VALUES ('Jan  8 2008  9:56AM', 21, 0.4, 'Dolan', 'Long Distance')

INSERT into #avetime VALUES ('Jan  8 2008  10:13AM', 21, 0.4, 'Dolan', 'Long Distance')

INSERT into #avetime VALUES ('Jan  8 2008  11:49AM', 56, 0.9, 'Dolan', 'Long Distance')

INSERT into #avetime VALUES ('Jan 8 2008 12:15PM', 56, 0.9, 'Dolan', 'Long Distance')

In the end, I'll also have to change the start date to add on the duration. I'm hoping that will be easy, just a simple dateadd to the stime I think. If we can figure out why the query isn't returning all of the Dolan records then that may shed some light. Anyone have any thoughts?

Mark Cowne One Orange Chip Points: 26865 More actions · Answer 6

The subquery needs to reference phoneuser

select stime.phoneuser, avg(datediff(ss, stime.origdate, etime.origdate))



  from #avetime stime

 inner join #avetime etime

    on stime.phoneuser = etime.phoneuser

   and etime.origdate = (select top 1 origdate from #avetime where origdate > stime.origdate and phoneuser = stime.phoneuser order by origdate)

group by stime.phoneuser

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

ehlinger Hall of Fame Points: 3585 More actions · Answer 7

Thanks. Based on your input and help, this is what does the trick for the sample data:

SELECT stime.phoneuser,



	CAST(AVG(CAST((DATEDIFF(ss, DATEADD(ss, stime.duration_seconds, stime.origdate), etime.origdate))AS DECIMAL(10,2)) /60) AS DECIMAL(10,2))

FROM #avetime stime JOIN #avetime etime ON stime.phoneuser = etime.phoneuser AND etime.origdate = (SELECT TOP 1 origdate FROM #avetime WHERE origdate > stime.origdate AND phoneuser = stime.phoneuser ORDER BY origdate)

WHERE CAST(FLOOR(CAST((stime.origdate) AS float))AS datetime) = CAST(FLOOR(CAST((etime.origdate) AS float))AS datetime)

GROUP BY stime.phoneuser

However, I discovered a slight hickup when looking at the data on the production server. Users can be on a call, put that on hold when another call comes in, complete the second call and then return to the original call. Here is an example of that in this new sample data:

CREATE TABLE #avetime (origdate datetime, duration_seconds int, phoneuser varchar(20), calltype varchar(15))



INSERT into #avetime VALUES ('Jan  7 2008  9:19:00AM', 21, 'Coleman', 'Long Distance')

INSERT into #avetime VALUES ('Jan  7 2008  9:19:30AM', 48, 'Coleman', 'Long Distance')

INSERT into #avetime VALUES ('Jan  7 2008  9:33AM', 81, 'Coleman', 'Long Distance')

INSERT into #avetime VALUES ('Jan  7 2008  9:35AM', 87, 'Coleman', 'Internal')

INSERT into #avetime VALUES ('Jan  7 2008  9:37AM', 27, 'Coleman', 'Internal')

INSERT into #avetime VALUES ('Jan  7 2008  9:42AM', 13, 'Coleman', 'Long Distance')

INSERT into #avetime VALUES ('Jan  7 2008  9:43AM', 84, 'Coleman', 'Long Distance')

INSERT into #avetime VALUES ('Jan  7 2008 12:00PM', 914, 'Coleman', 'Long Distance')

INSERT into #avetime VALUES ('Jan  7 2008  2:24PM', 3, 'Coleman', 'Internal')

INSERT into #avetime VALUES ('Jan  8 2008  10:13AM', 21, 'Coleman', 'Long Distance')

INSERT into #avetime VALUES ('Jan  8 2008  10:33AM', 482, 'Coleman', 'Long Distance')

INSERT into #avetime VALUES ('Jan  8 2008  10:35AM', 30, 'Coleman', 'Internal')

INSERT into #avetime VALUES ('Jan  8 2008  11:49AM', 56, 'Coleman', 'Long Distance')

INSERT into #avetime VALUES ('Jan  8 2008  9:15AM', 60, 'Dolan', 'Long Distance')

INSERT into #avetime VALUES ('Jan  8 2008  9:56AM', 21, 'Dolan', 'Long Distance')

INSERT into #avetime VALUES ('Jan  8 2008  10:13AM', 21,  'Dolan', 'Long Distance')

INSERT into #avetime VALUES ('Jan  8 2008  11:49AM', 56, 'Dolan', 'Long Distance')

INSERT into #avetime VALUES ('Jan 8 2008 12:15PM', 56, 'Dolan', 'Long Distance')

Basically, I want to do the same thing that was accomplished before, but given this sample data, the entry for Coleman on Jan 8 at 10:35 should be eliminated because the call prior to it "consumes" it. This is an example where the person basically used call waiting to place the first call on hold, took a second call and then came back to the first. So that second call doesn't have an affect on the average time between calls.

I've tried a bunch of different things but can't seem to get it.

Mark Cowne One Orange Chip Points: 26865 More actions · Answer 8

See if this helps

WITH ReducedAvetime AS



(SELECT origdate, duration_seconds, phoneuser, calltype

 FROM #avetime a

 WHERE NOT EXISTS (SELECT * FROM #avetime b

                   WHERE b.phoneuser=a.phoneuser

                     AND a.origdate > b.origdate

                     AND a.origdate <= DATEADD(SECOND,duration_seconds,b.origdate))

)

SELECT stime.phoneuser,

	CAST(AVG(CAST((DATEDIFF(ss, DATEADD(ss, stime.duration_seconds, stime.origdate), etime.origdate))AS DECIMAL(10,2)) /60) AS DECIMAL(10,2))

FROM ReducedAvetime stime JOIN ReducedAvetime etime ON stime.phoneuser = etime.phoneuser AND etime.origdate =

(SELECT TOP 1 origdate FROM ReducedAvetime WHERE origdate > stime.origdate AND phoneuser = stime.phoneuser ORDER BY origdate)

WHERE CAST(FLOOR(CAST((stime.origdate) AS float))AS datetime) = CAST(FLOOR(CAST((etime.origdate) AS float))AS datetime)

GROUP BY stime.phoneuser

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

ehlinger Hall of Fame Points: 3585 More actions · Answer 9

Thanks Mark. That worked. Just had to make one change because in some instances there was no time between calls because of conferencing. Here is the result:

SELECT datetimeconnect, durationseconds, loginname, calltype



 FROM @phonedataprep a

 WHERE NOT EXISTS (SELECT * FROM @phonedataprep b

                   WHERE b.loginname=a.loginname

                     AND a.datetimeconnect > b.datetimeconnect

AND a.datetimeconnect < DATEADD(SECOND,durationseconds,b.datetimeconnect))

Also had to put it into a table variable since I forgot that this all resided on our non-SQL 2005 server.