[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

SQL Oracle to Access

Hi All,

Anyone able to convert this Oracle SQL Query into something I can use in Access 2003? I've played with it a bit, but I have no clue how this would work in Access.

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

Original Post Here

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_24148387.html#a23655591

Thanks in advance.
0
kanus
Asked:
kanus
1 Solution
 
reb73Commented:
Sorry, I should have advised you on the Access Query in my previous post as well -

Create two queries as shown in the code snippet below (change Table1 to your actual Access Table name for all aliases below) -


QryInner
--------
SELECT T1.agent, T1.starttime, T1.endtime, MIN(T3.starttime) AS mintime
FROM (Table1 AS T1 LEFT JOIN Table1 AS T2 ON (T1.agent=T2.agent) AND (T2.starttime>T1.starttime) AND (T2.starttime<=T1.endtime)) LEFT JOIN Table1 AS T3 ON (T1.agent=T3.agent) AND (T3.starttime<T1.starttime) AND (T3.endtime>=T1.starttime)
WHERE T2.agent IS NULL
GROUP BY T1.agent, T1.starttime, T1.endtime;
 
Then invoke it in another query as follows -
QryOuter
--------
SELECT QryInner.agent, sum(datediff("n",IIF(QryInner.mintime IS NULL, QryInner.starttime, QryInner.mintime), QryInner.endtime)) AS totalduration
FROM QryInner
GROUP BY QryInner.agent;

Open in new window

0
 
kanusAuthor Commented:
/Bows
Thank you again :)
0

Featured Post

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.

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