Solved

Oracle: How to calculate time according to row

Posted on 2011-03-03
6
488 Views
Last Modified: 2013-12-19
Hello Again,
I have a table that holds data regarding the users' activity on the phone. It looks like this

Agent      date      signofftime      status
John      1/4/2011      11:17:33      BREAK                        
John      1/4/2011      11:35:56      Signed ON
John      1/4/2011      13:47:08      LUNCH                        
John      1/4/2011      14:22:43      Signed ON
Jane      1/4/2011      11:18:23      BREAK                        
Jane      1/4/2011      11:34:23      Signed ON
Jane      1/4/2011      12:00:05      LUNCH                        
Jane      1/4/2011      12:30:24      Signed ON

I want to calculate the time difference in hh:mm:ss between break and lunch status and the 'SignOn' time that immediately follows on the next row.

This table holds multiple users and multiple dates. It is certain tho that the Break and Lunch statuses are always followed by Signed on.

thank you in advance!

lemmohr
0
Comment
Question by:lemmohr
6 Comments
 

Author Comment

by:lemmohr
ID: 35027031
how do i edit the points here? I put 250 by mistake, should be 500
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 35027061
SELECT *
  FROM (SELECT agent,
               status,
               t,
               TO_CHAR(
                   TRUNC(SYSDATE)
                   + (t - LAG(t) OVER (PARTITION BY agent ORDER BY t)),
                   'hh24:mi:ss'
               )
                   diff
          FROM (SELECT agent,
                       TO_DATE(
                           signoffdate || signofftime,
                           'mm/dd/yyyyhh24:mi:ss'
                       )
                           t,
                       status
                  FROM yourtable))
 WHERE diff IS NOT NULL
ORDER BY agent, t
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35027070
I'm assuming your date strings are  mm/dd/yyyy,  if not, adjust the format accordingly in the to_date call
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 32

Expert Comment

by:awking00
ID: 35028839
Given your example, what do you expect the output to look like?
0
 

Expert Comment

by:NewDan526
ID: 35037756
Accurate analysis will save a lot of (re)development time and cost.  So its worth commenting on here...

ASSUMPTIONS/COMMENTS:
1. Your table looks like this: (I would consider making date and signofftime one timestamp column - unless you have a specific reason not to)
      CREATE TABLE timeclock
      (
         agent        VARCHAR2(20),
         signofftime  TIMESTAMP,
         status       VARCHAR2(20)
      );

2.  Your objective is to calculate the time your (presumably hard-working) employees are not working.

3. You are not addressing the last time on one day to the first on the next, and there are no night shifts crossing midnight.

4.  Your are NOT calculating how long they work (which would be a useful metric for the business since you should know when they leave for the day)

5. You are NOT covering any variations or exceptions (at this point), everybody always clocks in and out appropriately, and there are no data errors.

-------------
SOLUTION:
SELECT *
  FROM (SELECT agent,
               LAG(status) OVER (PARTITION BY agent ORDER BY SIGNOFFTIME) status,
               (SIGNOFFTIME - LAG(SIGNOFFTIME)
                  OVER (PARTITION BY agent ORDER BY SIGNOFFTIME)) as diff,
               SIGNOFFTIME
          FROM TIMECLOCK
          )
 WHERE diff IS NOT NULL
   and status in('BREAK','LUNCH')
ORDER BY agent, SIGNOFFTIME

--------------
RESULTS:
"AGENT"      "STATUS"      "DIFF"      "SIGNOFFTIME"
"Jane"      "BREAK"      0:16:0.0      04-JAN-11 11.34.23.000000000 AM
"Jane"      "LUNCH"      0:30:19.0      04-JAN-11 12.30.24.000000000 PM
"John"      "BREAK"      0:18:23.0      04-JAN-11 11.35.56.000000000 AM
"John"      "LUNCH"      0:35:35.0      04-JAN-11 02.22.43.000000000 PM
0
 

Author Closing Comment

by:lemmohr
ID: 35189721
i apologize, I thought i already awarded the points.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

810 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