We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

SQL Total Duration

kanus
kanus asked
on
Medium Priority
958 Views
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.

Thanks!
Comment
Watch Question

what about select sum(end-start) from table where agent='bob' ?  
or sum(datetiff(mi, start, end)) - for mssql
etc..
SharathData Engineer
CERTIFIED EXPERT

Commented:
What is your SQL version?

Author

Commented:
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.
modify names accordingly
select sum(g.TimeMinutes) as TotalMinutes, g.Agent
from 
(
SELECT ID, Start, End, Agent, datediff("n", Start, End) AS TimeMinutes
FROM yourtable
) g
group by g.Agent

Open in new window

Commented:
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

Commented:
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.)

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

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

Author

Commented:
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.
Commented:
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..

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
You rock my friend.

Author

Commented:
Thanks again reb73!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.