0

Hi i have a result like this from my query.

enter image description here

and i want to get the difference of a specific epmloyee break time so i need to minus the time (OUT-IN).

I have search the net but i see nothing related to my problem.

sample data below to make things more better.

ID  Date-TIme           Log-Type
1   2020-02-03 01:52:01   IN
2   2020-02-03 02:00:50   OUT
3   2020-02-03 03:12:14   IN
4   2020-02-03 03:48:55   OUT
5   2020-02-03 05:22:00   OUT
6   2020-02-03 21:59:52   IN
7   2020-02-03 21:59:57   IN
8   2020-02-03 22:20:21   OUT

expected result.

ID  Date-TIme           Log-Type       RESULT
1   2020-02-03 01:52:01   IN
2   2020-02-03 02:00:50   OUT
3   2020-02-03 03:12:14   IN           1:12
4   2020-02-03 03:48:55   OUT
5   2020-02-03 05:22:00   OUT
6   2020-02-03 21:59:52   IN           5:38
7   2020-02-03 21:59:57   IN
8   2020-02-03 22:20:21   OUT

My query on getting the result.

  SELECT L.NEVENTLOGIDN, LPAD (nuserid, 6, '0') nuserid, u.susername, 
    TO_CHAR (TO_DATE ('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + ( (ndatetime) / (60 * 60 * 24)), 'YYYY-MM-DD HH24:MI:SS') 
    date_time, l.nreaderidn, r.sname, 
    CASE WHEN l.nreaderidn IN (SELECT nreaderidn FROM tb_reader where sdescription in(Select Distinct SDESCRIPTION from TB_READER where SDESCRIPTION is not null and SDESCRIPTION not Like '%IN%' and SDESCRIPTION not like '%OUT%') and upper(sname) like '%' || upper('OUT') || '%') THEN 'OUT' 
    WHEN l.nreaderidn  IN (SELECT nreaderidn FROM tb_reader where sdescription in(Select Distinct SDESCRIPTION from TB_READER where SDESCRIPTION is not null and SDESCRIPTION not Like '%IN%' and SDESCRIPTION not like '%OUT%') and upper(sname) like '%' || upper('IN') || '%') THEN 'IN' END logtype 
   FROM TB_EVENT_LOG l, TB_READER r, TB_USER u 
   WHERE 
   l.nreaderidn IN (SELECT nreaderidn FROM tb_reader where sdescription in(Select Distinct SDESCRIPTION from TB_READER where SDESCRIPTION is not null and SDESCRIPTION not Like '%IN%' and SDESCRIPTION not like '%OUT%')) 
   AND NDATETIME >= (trunc(sysdate -1) - TO_DATE ('1970-01-01 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM')) * 24 * 60 * 60 
   AND ndatetime <= (trunc(sysdate) - TO_DATE ('1970-01-01 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM')) * 24 * 60 * 60 
   AND l.nuserid = u.suserid 
   AND l.nreaderidn = r.nreaderidn                           
   ORDER BY 2, 4;

It is normal in data to have a consecutive IN or OUT in that case i need to get the latest.

Hope someone help me out with this. Thank you in advance

  • i want to get the difference of a specific epmloyee break time so i need to minus the time (OUT-IN). Something like (schematically) CASE WHEN log_type = 'IN' AND LAG(log_type) OVER (PARTITION BY employee ORDER BY date_time) = 'OUT' THEN DATEDIFF(date_time, LAG(date_time) OVER (PARTITION BY employee ORDER BY date_time)) ELSE NULL END ? – Akina Feb 4 at 5:30
  • "DATEDIFF": invalid identifier is it a function? – ivan kim Feb 4 at 5:57
  • It was a scheme, idea. Of course you have to bring them in line with the requirements of the syntax. – Akina Feb 4 at 6:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.