Oracle: How to calculate time according to row
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm assuming your date strings are mm/dd/yyyy, if not, adjust the format accordingly in the to_date call
Given your example, what do you expect the output to look like?
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
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
ASKER
i apologize, I thought i already awarded the points.
ASKER