SQL Total Duration

Posted on 2009-02-16
Last Modified: 2012-05-06
Hi All,

I would like to calculate the total duration of multiple items that overlap. Kind of like Agent Utilization.
Example Data:
Start                      End                      Agent
1/1/2009 12:00 AM      1/1/2009 12:21 AM      Bob
1/1/2009 12:03 AM      1/1/2009 12:24 AM      Bob
1/1/2009 12:05 AM      1/1/2009 12:28 AM      Bob
1/1/2009 12:43 AM      1/1/2009 12:52 AM      Bob
1/1/2009 12:45 AM      1/1/2009 12:58 AM      Bob

We know that Bob worked from 12:00 to 12:28 and then from 12:43 to 12:58. Making his total time worked 43 minutes. How would I come to this conclusion from an SQL statement? Alternatively the reverse would work for me as well, Bob didnt work 23 hours and 17 minutes.

Any direction would be greatly appreciated.

Question by:kanus
    LVL 15

    Expert Comment

    what about select sum(end-start) from table where agent='bob' ?  
    or sum(datetiff(mi, start, end)) - for mssql
    LVL 40

    Expert Comment

    What is your SQL version?

    Author Comment

    Ludofulop, I believe that would would give me a lot larger number than I am looking for, it would make a total sum of start-end.

    Sharath_123 I'm using access 2003. It will eventually be done in Oracle, I will be able to convert any SQL I use in Access later to Oracle. The access DB I'm using more of a sample for how it will look.

    Thank you for your help.
    LVL 19

    Expert Comment

    modify names accordingly
    select sum(g.TimeMinutes) as TotalMinutes, g.Agent
    SELECT ID, Start, End, Agent, datediff("n", Start, End) AS TimeMinutes
    FROM yourtable
    ) g
    group by g.Agent

    Open in new window

    LVL 25

    Expert Comment

    Here's code that would work on Oracle/SQL backend -

    Adapt it for your access backend -
    create table #tmp (starttime datetime null, endtime datetime null, agent varchar(10) null)
    insert #tmp values('1/1/2009 12:00 AM','1/1/2009 12:21 AM','Bob')
    insert #tmp values('1/1/2009 12:03 AM','1/1/2009 12:24 AM','Bob')
    insert #tmp values('1/1/2009 12:05 AM','1/1/2009 12:28 AM','Bob')
    insert #tmp values('1/1/2009 12:43 AM','1/1/2009 12:52 AM','Bob')
    insert #tmp values('1/1/2009 12:45 AM','1/1/2009 12:58 AM','Bob')
    select agent,sum(datediff(minute,mintime, endtime)) as totalduration
    from ( select t1.agent, t1.endtime, min(t3.starttime) as mintime
           from #tmp t1
           left join #tmp t2 on t2.agent = t1.agent and t2.starttime > t1.starttime and t2.starttime <= t1.endtime
           left join #tmp t3 on t3.agent = t1.agent and t3.starttime < t1.starttime and t3.endtime >= t1.starttime
           where t2.agent is null
           group by t1.agent, t1.endtime
         ) tnew
    group by agent

    Open in new window


    Author Comment

    Frankytee, I couldnt get that running in Access.

    This is what Access does with the SQL format
    SELECT sum(g.TimeMinutes) AS TotalMinutes, g.Agent
    FROM [SELECT Start, End, Agent, datediff("n",Start,End) AS TimeMinutes FROM Activity; ] AS g
    GROUP BY g.Agent;

    Then gives me an error
    The Microsoft Jet database engine cannot find the input table or query 'SELECT Start, End, Agent, datediff("n",Start,End) AS TimeMinutes FROM Activity;'. Make sure it exists and that its name is spelled correctly.

    However, before we go any further, I was analyzing the query I believe this would give me a total of the time spent?
    Which is 86 minutes. If so, this isnt exactly what I'm looking for. As for Bob, it would be impossible to work 86 minutes in less than one hours time. I need figure out how to get to the point where it shows me 43 minutes as the amount of time he has worked. I apologize if I wasnt clear, my goal is to find out how much Bob is utilized. I appreciate all of your help.

    Reb73, I appreciate your effort as always (You always pop up to help with my SQL questions :)), it appears your query would give me what Im looking for, but just wanted to verify, in this scenario the results would show Bob worked 43 minutes? (I dont have anything to test this or else I would check for myself.)
    LVL 25

    Expert Comment

    Yes, it does.. Actual results using your sample data below -

    agent      totalduration
    ---------- -------------
    Bob                   43


    Author Comment

    Hi reb73,

    Sorry to keep bugging you, I just have one more question for you. If you can answer this question I will fully understand your code. Would the following values still result in 43 or would the SQL need to be altered?

    insert #tmp values('1/1/2009 12:00 AM','1/1/2009 12:21 AM','Bob')
    insert #tmp values('1/1/2009 12:02 AM','1/1/2009 12:22 AM','Bob')
    insert #tmp values('1/1/2009 12:03 AM','1/1/2009 12:23 AM','Bob')
    insert #tmp values('1/1/2009 12:04 AM','1/1/2009 12:24 AM','Bob')
    insert #tmp values('1/1/2009 12:05 AM','1/1/2009 12:28 AM','Bob')
    insert #tmp values('1/1/2009 12:43 AM','1/1/2009 12:52 AM','Bob')
    insert #tmp values('1/1/2009 12:45 AM','1/1/2009 12:58 AM','Bob')

    Thank you.
    LVL 25

    Accepted Solution

    It'll still result in 43 minutes ;-)

    The alias t2 will eliminate overlapping end/start times returning the following records from t1 -

    '1/1/2009 12:05 AM','1/1/2009 12:28 AM','Bob'
    '1/1/2009 12:45 AM','1/1/2009 12:58 AM','Bob'

    and the alias t3 then gets the minimum start time from the overlapping records - getting 12:00 and 12:43 respectively which are used as adjusted starttimes for the two records above..

    Author Comment

    You rock my friend.

    Author Closing Comment

    Thanks again reb73!

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Suggested Solutions

    In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now