SQL Oracle to Access

Posted on 2009-02-16
Last Modified: 2012-05-06
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

Thanks in advance.
Question by:kanus
    LVL 25

    Accepted Solution

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

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


    Author Closing Comment

    Thank you again :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    737 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

    18 Experts available now in Live!

    Get 1:1 Help Now