ID two records and subtract time fields

[scottyjohn]

Hi all,
I have an oracle viewas per below....

CREATE OR REPLACE VIEW v_login (
  employee_id,
  place,
  event_desc,
  eventdate,
  "TIME"
) AS
SELECT
        P.EMPLOYEE_ID,
        L.NAME,
        CASE STATUS
        WHEN 0 THEN 'LOGOUT'
        WHEN 1 THEN 'LOGIN'
                 ELSE 'Unknown' END,
        TO_CHAR(TO_DATE('01.01.70','DD.MM.RR')+(TIME/(60*60*24)),'DD/MM/YYYY'),
        TO_CHAR(TO_DATE('01.01.70','DD.MM.RR')+(TIME/(60*60*24)),'HH24:MI:SS')
        FROM
CONFIG02.CFG_PERSON P,
SOFTPHONE.LOGIN S,
CONFIG02.CFG_PLACE L
WHERE P.DBID = S.AGENTDBID AND S.PLACEDBID = L.DBID
AND QUEUEDBID ='0'
/

It will display two records with the same employee_id and the same date, which will be a login event and a logout event. What I need to do is to have a query which will display the employee_id, the date, and the total time between the login event and the logout event. I cant think how to do this, so any help or advice would be much appreciated....

John

 

[r937]

perhaps you could ask in one of the oracle forums

this forum is for ANSI SQL

r937.com | rudy.ca

 

[PHV]

A starting point:
SELECT I.employee_id, I.eventdate, I.Time, O.Time
FROM v_login I
INNER JOIN v_login O ON I.employee_id = O.employee_id AND I.eventdate = O.eventdate
WHERE I.event_desc = 'LOGIN' AND O.event_desc = 'LOGOUT'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886