CODEWITHSUNDEEP
sqloracle-databaseoracle11gsql-query-store
AL- MAHROUQI
AL- MAHROUQI

Reputation: 13

SQLQuery for Time In and Time Out attendance in Oracle

I have a table in oracle with the below sample output.

EID   | type  |   Date
24    |  IN   |03/25/2019 6:45 am
24    |  OUT  |03/25/2019 8:05 am
24    |  IN   |03/25/2019 8:06 am
24    |  IN   |03/25/2019 8:28 am
24    |  OUT  |03/25/2019 9:48 am
24    |  IN   |03/25/2019 9:52 am
24    |  IN   |03/25/2019 9:57 am
24    |  IN   |03/25/2019 10:44 am
24    |  OUT  |03/25/2019 12:16 pm
24    |  OUT  |03/25/2019 1:00 pm
24    |  IN   |03/25/2019 1:05 pm
24    |  OUT  |03/25/2019 2:21  pm

I want to build a query to achieve the below results:

EID |       TIMEIN        |    TIMEOUT            | DIIF_IN_MIN 
24  | 03/25/2019 6:45 am  | 03/25/2019 8:05 am    |   1 
24  | 03/25/2019 8:06 am  |       null            |   0
24  | 03/25/2019 8:28 am  | 03/25/2019 9:48 am    |   4
24  | 03/25/2019 9:52 am  |       null            |   0
24  | 03/25/2019 9:57 am  |       null            |   0
24  | 03/25/2019 10:44 am | 03/25/2019 12:16 pm   |   0
24  |        null         | 03/25/2019 1:00 pm    |   5
24  | 03/25/2019 1:05 pm  | 03/25/2019 2:21  pm   |   0

Upvotes: 1

Views: 400

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

You can do this with the following logic.

You can get all the ins using a lead() query. Then you can get the unmatched outs using a lag():

select t.eid, date as timein,
       (case when next_type = 'OUT' then next_date end) as timeout,
       ((case when next_type = 'OUT' then next_date end) - date) * (24 * 60) as diff_in_minutes
from (select t.*,
             lead(type) over (partition by eid order by date) as next_type,
             lead(type) over (partition by eid order by date) as next_date
      from t
     ) t
where type = 'IN'
union all
select t.eid, null as timein,
       date as timeout, null as diff_in_minutes
from (select t.*,
             lag(type) over (partition by eid order by date) as prev_type,
             lag(date) over (partition by eid order by date) as prev_date
      from t
     ) t
where type = 'OUT' and (prev_type <> 'IN' or prev_type is null);

Here is a db<>fiddle with all your data, showing that it supports the multiple INs and OUTs.

Note this assumes that the date/time column is really a date. It only converts to a timestamp to show the time component in the result set.

Upvotes: 0

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65363

You can use such a logic by the contribution of lead window analytic function

with tab(eid, type, dates ) as
(
 select 24,'IN' ,timestamp'2019-03-25 06:45:00' from dual union all
 select 24,'OUT',timestamp'2019-03-25 08:05:00' from dual union all
 select 24,'IN' ,timestamp'2019-03-25 08:06:00' from dual union all
 select 24,'IN' ,timestamp'2019-03-25 08:28:00' from dual union all
 select 24,'OUT',timestamp'2019-03-25 09:48:00' from dual union all
 select 24,'IN' ,timestamp'2019-03-25 09:52:00' from dual    
)
select t1.eid, t1.dates as timein, t2.dates as timeout, 
       nvl(to_number(regexp_substr(to_char(t1.ld_dates - t2.dates),'[^:]+',1,2)),0) 
           as diff_in_minutes
  from ( select lead(dates) over (order by dates) as ld_dates, t.* 
           from tab t 
          where type = 'IN' order by dates) t1
  full join ( select * from tab where type = 'OUT' order by dates) t2
    on t1.dates <= t2.dates and ld_dates > t2.dates
 order by t1.dates;

EID TIMEIN              TIMEOUT             DIFF_IN_MINUTES
24  25.03.2019 06:45:00 25.03.2019 08:05:00 1
24  25.03.2019 08:06:00 NULL                0
24  25.03.2019 08:28:00 25.03.2019 09:48:00 4
24  25.03.2019 09:52:00 NULL                0

Demo

Upvotes: 1

Related Questions