Solved

Oracle: How to calculate time according to row

Posted on 2011-03-03
490 Views
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.

lemmohr
0
Question by:lemmohr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

Author Comment

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

LVL 74

Accepted Solution

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 74

Expert Comment

ID: 35027070
I'm assuming your date strings are  mm/dd/yyyy,  if not, adjust the format accordingly in the to_date call
0

LVL 32

Expert Comment

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

Expert Comment

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

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

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

Featured Post

Question has a verified solution.

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

Suggested Solutions

DB migration from Mssql to 12c oracle , data not loading. 3 52
pivot rows to columns 1 58
SQL query for highest sequence 4 73
Oracle Join issue. 3 42
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month9 days, 9 hours left to enroll