Link to home
Create AccountLog in
Avatar of Maliki Hassani
Maliki HassaniFlag for United States of America

asked on

Oracle: Get previous record

Experts,  I have a query that pulls in dates for each record that is updated.  I would like to do a time calculation of how long in minutes it took from the previous update.

I believe there is a way by using the over partition by.  Here is the query output:

TKT_ID      DATE_CREATED      UPDATE_TYPE
890188      2/15/2013 9:14:54 AM      ALARM
890188      2/15/2013 9:19:33 AM      ASSIGNMENT CHANGE
890188      2/15/2013 9:19:33 AM      NOTES
890188      2/15/2013 9:19:33 AM      TICKET CREATION
890188      2/15/2013 9:19:34 AM      NOTES
890188      2/15/2013 9:19:49 AM      ACCESSED
890188      2/15/2013 9:21:14 AM      STATUS CHANGE
890188      2/15/2013 9:24:42 AM      ASSIGNMENT CHANGE

I am looking to create something like this:

TKT_ID      DATE_CREATED      TIME_LAPSE_MINUTES      UPDATE_TYPE
890188      2/15/2013 9:19:33 AM           0      ASSIGNMENT CHANGE
890188      2/15/2013 9:19:33 AM           0      NOTES
890188      2/15/2013 9:19:33 AM           0      TICKET CREATION
890188      2/15/2013 9:19:34 AM           0      NOTES
890188      2/15/2013 9:19:49 AM           0      ACCESSED
890188      2/15/2013 9:21:14 AM           2      STATUS CHANGE
890188      2/15/2013 9:24:42 AM           3      ASSIGNMENT CHANGE

This is the query that I am working with, I am using SYSDATE right now:

SELECT TKT_ID, DATE_CREATED, SYSDATE, to_char(((SYSDATE - DATE_CREATED)*1440),'9999999.99') AS TIME_LAPSE_MINUTE, UPDATE_TYPE
FROM
(
SELECT TKT_ID, TAH_ASSIGN_START_DATE AS DATE_CREATED, UPPER(TAH_CHANGE_TYPE) AS UPDATE_TYPE
FROM VIEW_TKT_ASSIGNMENTS
WHERE TKT_ID = '890188'
)

Thank you
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Maliki Hassani

ASKER

Okay, I heard of that similar to excels offset
Thanks!