Post reply

Determine Average Time between dates

  • 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')

  • January 9, 2008 at 2:53 am

    #766065

    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.

  • 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?

  • January 13, 2008 at 11:54 pm

    #767566

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

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

  • January 15, 2008 at 10:37 pm

    #768467

    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?

  • January 16, 2008 at 2:50 am

    #768514

    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

  • January 16, 2008 at 11:58 pm

    #769081

    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.

  • January 17, 2008 at 5:05 am

    #769141

    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

  • January 17, 2008 at 4:52 pm

    #769499

    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.

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

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