SQL Total Duration

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

What is your SQL version?

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

Select allOpen in new window

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

Select allOpen in new window

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

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

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

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.

You rock my friend.

Thanks again reb73!