Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

SQL Total Duration

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!
0
kanus
Asked:
kanus
1 Solution
 
ludofulopCommented:
what about select sum(end-start) from table where agent='bob' ?  
or sum(datetiff(mi, start, end)) - for mssql
etc..
0
 
SharathData EngineerCommented:
What is your SQL version?
0
 
kanusAuthor 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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

0
 
reb73Commented:
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

0
 
kanusAuthor 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.)
0
 
reb73Commented:
Yes, it does.. Actual results using your sample data below -

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

0
 
kanusAuthor 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.
0
 
reb73Commented:
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..
0
 
kanusAuthor Commented:
You rock my friend.
0
 
kanusAuthor Commented:
Thanks again reb73!
0

Featured Post

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.

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