Skip to Main Content
  • Questions

  • Find the last time for consecutive rows by status

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, adrian.

Asked: March 02, 2018 - 12:25 pm UTC

Last updated: March 09, 2018 - 2:49 pm UTC

Version: 11 g

Viewed 1000+ times

You Asked

Hello,


I need some help with an Oracle Sql Query. For each day i must calculate the time difference between each cycle IN & OUT and then sum the difference as total worked minutes. The main problem that i have it's that the input data is variable, so i can have 1 or 2 or 3 or N time with the status IN and 1 or 2 or 3 or N time with the status OUT. So i used the analytical function of row_number+1 for set IN and row_number for set OUT, but i cannot determine always the max(time) for IN or OUT status per each cycle.

P.S. My problem that i have it is that for the day 30 for the cycle 3 i have the pair 30-01-2018 13:02:21--OUT and 30-01-2018 12:52:16--IN, and i need the OUT to be 30-01-2018 14:55:02--OUT, the same issue for the day 31
at the first cycle where i have the pair 31-01-2018 10:03:58--OUT and
31-01-2018 10:02:39--IN and i need OUT to be 31-01-2018 11:11:33--OUT

Thank you for the support !

 create table test
 (
 time date not null 
 ,evolutionid int not null
 ,in_out_status varchar2(3) not null
 );


insert into test (time,evolutionid,in_out_status)
values (To_Date('1/29/2018 9:52:34 AM', 'mm-dd-yyyy HH:MI:SS AM'),101,'IN'); 
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/29/2018 9:52:54 AM', 'mm-dd-yyyy HH:MI:SS AM'),100,'OUT');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/29/2018 9:54:10 AM', 'mm-dd-yyyy HH:MI:SS AM'),99,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/29/2018 10:28:27 AM', 'mm-dd-yyyy HH:MI:SS AM'),98,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/29/2018 11:43:50 AM', 'mm-dd-yyyy HH:MI:SS AM'),97,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/29/2018 12:37:40 PM', 'mm-dd-yyyy HH:MI:SS AM'),96,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/29/2018 12:37:57 PM', 'mm-dd-yyyy HH:MI:SS AM'),95,'OUT');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/29/2018 2:16:59 PM', 'mm-dd-yyyy HH:MI:SS AM'),94,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/29/2018 2:58:00 PM', 'mm-dd-yyyy HH:MI:SS AM'),93,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/29/2018 3:34:22 PM', 'mm-dd-yyyy HH:MI:SS AM'),92,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/29/2018 4:20:06 PM', 'mm-dd-yyyy HH:MI:SS AM'),91,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/29/2018 4:31:36 PM', 'mm-dd-yyyy HH:MI:SS AM'),90,'OUT');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/29/2018 4:56:48 PM', 'mm-dd-yyyy HH:MI:SS AM'),89,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/30/2018 9:53:08 AM', 'mm-dd-yyyy HH:MI:SS AM'),88,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/30/2018 10:24:15 AM', 'mm-dd-yyyy HH:MI:SS AM'),87,'OUT');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/30/2018 10:25:09 AM', 'mm-dd-yyyy HH:MI:SS AM'),86,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/30/2018 10:55:11 AM', 'mm-dd-yyyy HH:MI:SS AM'),85,'OUT');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/30/2018 11:12:52 AM', 'mm-dd-yyyy HH:MI:SS AM'),84,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/30/2018 12:52:16 PM', 'mm-dd-yyyy HH:MI:SS AM'),83,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/30/2018 1:02:21 PM', 'mm-dd-yyyy HH:MI:SS AM'),82,'OUT');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/30/2018 2:55:02 PM', 'mm-dd-yyyy HH:MI:SS AM'),81,'OUT');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/30/2018 2:57:09 PM', 'mm-dd-yyyy HH:MI:SS AM'),80,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/30/2018 3:37:14 PM', 'mm-dd-yyyy HH:MI:SS AM'),79,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/30/2018 4:53:42 PM', 'mm-dd-yyyy HH:MI:SS AM'),78,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/30/2018 5:56:30 PM', 'mm-dd-yyyy HH:MI:SS AM'),77,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/30/2018 6:01:58 PM', 'mm-dd-yyyy HH:MI:SS AM'),76,'OUT');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/31/2018 10:02:39 AM', 'mm-dd-yyyy HH:MI:SS AM'),75,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/31/2018 10:03:58 AM', 'mm-dd-yyyy HH:MI:SS AM'),74,'OUT');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/31/2018 11:11:33 AM', 'mm-dd-yyyy HH:MI:SS AM'),73,'OUT');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/31/2018 11:16:01 AM', 'mm-dd-yyyy HH:MI:SS AM'),72,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/31/2018 11:30:16 AM', 'mm-dd-yyyy HH:MI:SS AM'),71,'OUT');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/31/2018 11:52:41 AM', 'mm-dd-yyyy HH:MI:SS AM'),70,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/31/2018 12:11:36 PM', 'mm-dd-yyyy HH:MI:SS AM'),69,'OUT');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/31/2018 5:33:18 PM', 'mm-dd-yyyy HH:MI:SS AM'),68,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/31/2018 6:25:50 PM', 'mm-dd-yyyy HH:MI:SS AM'),67,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/31/2018 7:06:11 PM', 'mm-dd-yyyy HH:MI:SS AM'),66,'OUT');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/31/2018 7:07:30 PM', 'mm-dd-yyyy HH:MI:SS AM'),65,'IN');
insert into test (time,evolutionid,in_out_status)
values (To_Date('1/31/2018 7:10:54 PM', 'mm-dd-yyyy HH:MI:SS AM'),64,'OUT');

-- truncate table test;


alter session set nls_date_format = 'dd-mm-yyyy hh24:mi:ss';
select 
BB.time_BB
,BB.in_out_status_BB
,AA.time_AA
,AA.in_out_status_AA  
,round(((BB.time_BB - AA.time_AA) * 24 * 60),2) AS diff_time_minutes
from
(SELECT
time  as time_AA
,in_out_status as in_out_status_AA
,row_number() over ( order by  evolutionid desc) as rn_AA
FROM
test) AA
    
   inner join
   
(SELECT
time  as time_BB
,in_out_status as in_out_status_BB
,row_number() over ( order by  evolutionid desc) as rn_BB
FROM
test) BB

on  AA.rn_AA+1 = BB.rn_BB   -- and  mod (AA.rn_AA,2)= 0
and  AA.in_out_status_AA  not like 'OUT' and  BB.in_out_status_BB  not like 'IN' 
order by AA.TIME_AA ;

and Chris said...

So you have a sequence of rows ins and outs:

IN *
OUT *
IN
IN *
OUT
OUT *
IN
IN
IN *
OUT
OUT
OUT *


And you want to pair the last IN with last OUT after it before the next IN? i.e. the rows with stars next to them?

If so, this is another problem match_recognize makes easy!

Look for a pattern of one or more INs followed by one or more OUTs. And return the last time for each:

select * 
from   test match_recognize (
  order by time
  measures
    last (time_in.time) as last_in,
    last (time_out.time) as last_out
  pattern ( time_in+ time_out+ )
  define
    time_in as in_out_status = 'IN',
    time_out as in_out_status = 'OUT'
);

LAST_IN               LAST_OUT              
29-01-2018 09:52:34   29-01-2018 09:52:54   
29-01-2018 12:37:40   29-01-2018 12:37:57   
29-01-2018 16:20:06   29-01-2018 16:31:36   
30-01-2018 09:53:08   30-01-2018 10:24:15   
30-01-2018 10:25:09   30-01-2018 10:55:11   
30-01-2018 12:52:16   30-01-2018 14:55:02   
30-01-2018 17:56:30   30-01-2018 18:01:58   
31-01-2018 10:02:39   31-01-2018 11:11:33   
31-01-2018 11:16:01   31-01-2018 11:30:16   
31-01-2018 11:52:41   31-01-2018 12:11:36   
31-01-2018 18:25:50   31-01-2018 19:06:11   
31-01-2018 19:07:30   31-01-2018 19:10:54


Sadly, this is a 12c feature. And you're on 11g. So you need something a little different...

One way to do this is:

- Use a variant of the Tabibitosan method to group consecutive INs and OUTs
- This will lump together consecutive times for each status, but there will be gaps in the values. This makes it hard to know which IN links with which OUT.
- Renumber the groups with consecutive numbers starting at 1 for IN and OUT. Dense_rank will do this
- Pivot the results

Which gives:

with tabibitosan as (  
  select time, in_out_status,
         row_number() over (order by time) -
           row_number() over (partition by in_out_status order by time) grp
  from   test
), grps as (
  select dense_rank() over ( partition by in_out_status order by grp ) dr,
         t.time, t.in_out_status
  from   tabibitosan t
)
  select *
  from   grps
  pivot (
    max(time) for
    in_out_status in ( 'IN' last_in, 'OUT' last_out)
  )
  order  by dr;

DR   LAST_IN                LAST_OUT               
   1 29-JAN-2018 09:52:34   29-JAN-2018 09:52:54   
   2 29-JAN-2018 12:37:40   29-JAN-2018 12:37:57   
   3 29-JAN-2018 16:20:06   29-JAN-2018 16:31:36   
   4 30-JAN-2018 09:53:08   30-JAN-2018 10:24:15   
   5 30-JAN-2018 10:25:09   30-JAN-2018 10:55:11   
   6 30-JAN-2018 12:52:16   30-JAN-2018 14:55:02   
   7 30-JAN-2018 17:56:30   30-JAN-2018 18:01:58   
   8 31-JAN-2018 10:02:39   31-JAN-2018 11:11:33   
   9 31-JAN-2018 11:16:01   31-JAN-2018 11:30:16   
  10 31-JAN-2018 11:52:41   31-JAN-2018 12:11:36   
  11 31-JAN-2018 18:25:50   31-JAN-2018 19:06:11   
  12 31-JAN-2018 19:07:30   31-JAN-2018 19:10:54

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

with all that coming and going...

Racer I., March 08, 2018 - 11:54 am UTC

Hi,

From my own experience with timecard systems i'd expect to use first IN to last OUT for each cycle and ignore the inner rows.

Once again avoiding match_recognize ;)

with
allrows as (
  select time, in_out_status cs, lag(in_out_status) over(order by time) ps, lead(in_out_status) over(order by time) ns
  from test),
effrows as (
select time in_time, cs, lead(time) over(order by time) out_time 
  from allrows
  where (cs = 'IN'  and NVL(ps, 'OUT') = 'OUT')
     or (cs = 'OUT' and NVL(ns, 'IN') = 'IN'))
select in_time, out_time, TRUNC(((out_time - in_time) * 24 * 60)) mi
from effrows
where cs = 'IN'
order by 1

IN_TIME             OUT_TIME            MI
29.01.2018 09:52:34 29.01.2018 09:52:54 0
29.01.2018 09:54:10 29.01.2018 12:37:57 163
29.01.2018 14:16:59 29.01.2018 16:31:36 134
29.01.2018 16:56:48 30.01.2018 10:24:15 1047
30.01.2018 10:25:09 30.01.2018 10:55:11 30
30.01.2018 11:12:52 30.01.2018 14:55:02 222
30.01.2018 14:57:09 30.01.2018 18:01:58 184
31.01.2018 10:02:39 31.01.2018 11:11:33 68
31.01.2018 11:16:01 31.01.2018 11:30:16 14
31.01.2018 11:52:41 31.01.2018 12:11:36 18
31.01.2018 17:33:18 31.01.2018 19:06:11 92
31.01.2018 19:07:30 31.01.2018 19:10:54 3


Add a group by trunc(in_time) and SUM(mi) to get values per day.

regards,
Chris Saxon
March 08, 2018 - 1:05 pm UTC

Yeah, first-in, last-out for times is more common in my experience.

thank you for the support !!!!

adrian banica, March 09, 2018 - 9:41 am UTC

Your ideas are great use to me. I still have some bottlenecks with the data returned from the query.
I install also Oracle 12c for the match_recognize function and it works fine, but not 100% because it did not match all the days(time) ok.
The inner query gives the bellow results that are incomplete as match.

my_out my_in
2017-11-29 15:12:08 2017-11-29 15:10:49
2017-11-29 15:16:55 2017-11-29 15:13:39
2017-11-29 17:00:55 2017-11-29 16:50:56
2017-11-29 09:48:28 2017-11-29 17:15:25

create table test
(
time varchar2(50) not null
,in_out_status varchar2(3) not null
);

insert into test (time,in_out_status)
values ('11/29/2017 6:30:54 PM','OUT');
insert into test (time,in_out_status)
values ('11/29/2017 6:11:15 PM','OUT');
insert into test (time,in_out_status)
values ('11/29/2017 5:15:25 PM','IN');
insert into test (time,in_out_status)
values ('11/29/2017 5:00:55 PM','OUT');
insert into test (time,in_out_status)
values ('11/29/2017 4:50:56 PM','IN');
insert into test (time,in_out_status)
values ('11/29/2017 4:33:25 PM','IN');
insert into test (time,in_out_status)
values ('11/29/2017 3:16:55 PM','OUT');
insert into test (time,in_out_status)
values ('11/29/2017 3:13:39 PM','IN');
insert into test (time,in_out_status)
values ('11/29/2017 3:12:08 PM','OUT');
insert into test (time,in_out_status)
values ('11/29/2017 3:10:49 PM','IN');
insert into test (time,in_out_status)
values ('11/29/2017 10:08:47 AM','IN');
insert into test (time,in_out_status)
values ('11/29/2017 10:00:20 AM','OUT');
insert into test (time,in_out_status)
values ('11/29/2017 9:48:43 AM','IN');
insert into test (time,in_out_status)
values ('11/29/2017 9:48:28 AM','OUT');


ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
select
my_out
,my_in
,round(((my_out - my_in)*24*60),2) as diff_time_minutes
from
(select
to_date( last_out, 'mm-dd-yyyy HH:MI:SS AM') as my_out
,to_date( last_in, 'mm-dd-yyyy HH:MI:SS AM') as my_in
from test
match_recognize (order by time
measures
max (time_in.time) as last_in,
max (time_out.time) as last_out
pattern ( time_in+ time_out+ )
define
time_in as in_out_status = 'IN',
time_out as in_out_status = 'OUT'
)order by
to_date( last_in, 'mm-dd-yyyy HH:MI:SS AM')
,to_date( last_out, 'mm-dd-yyyy HH:MI:SS AM')
)ALL_IN
where extract ( day from (my_out)) = extract ( day from (my_in))
and extract ( month from (my_out)) = extract ( month from (my_in))
and extract ( year from (my_out)) = extract ( year from (my_in))
and round(((my_out - my_in)*24*60),2) > 0;

Thank you for the support !
Chris Saxon
March 09, 2018 - 11:28 am UTC

Reason #239 not to store dates in a varchar2:

You screw up the sorting!

select * from test
order  by time;

TIME                     IN_OUT_STATUS   
11/29/2017 10:00:20 AM   OUT             
11/29/2017 10:08:47 AM   IN              
11/29/2017 3:10:49 PM    IN              
11/29/2017 3:12:08 PM    OUT             
11/29/2017 3:13:39 PM    IN              
11/29/2017 3:16:55 PM    OUT             
11/29/2017 4:33:25 PM    IN              
11/29/2017 4:50:56 PM    IN              
11/29/2017 5:00:55 PM    OUT             
11/29/2017 5:15:25 PM    IN              
11/29/2017 6:11:15 PM    OUT             
11/29/2017 6:30:54 PM    OUT             
11/29/2017 9:48:28 AM    OUT             
11/29/2017 9:48:43 AM    IN


Use the correct data type (date) and you save yourself a whole bunch of conversions. And the data looks right to me:

drop table test cascade constraints purge;
alter session set nls_date_format = 'mm/dd/yyyy hh:mi:ss am';
create table test 
( 
time date not null 
,in_out_status varchar2(3) not null 
); 

insert into test (time,in_out_status) 
values ('11/29/2017 6:30:54 PM','OUT'); 
insert into test (time,in_out_status) 
values ('11/29/2017 6:11:15 PM','OUT'); 
insert into test (time,in_out_status) 
values ('11/29/2017 5:15:25 PM','IN'); 
insert into test (time,in_out_status) 
values ('11/29/2017 5:00:55 PM','OUT'); 
insert into test (time,in_out_status) 
values ('11/29/2017 4:50:56 PM','IN'); 
insert into test (time,in_out_status) 
values ('11/29/2017 4:33:25 PM','IN'); 
insert into test (time,in_out_status) 
values ('11/29/2017 3:16:55 PM','OUT'); 
insert into test (time,in_out_status) 
values ('11/29/2017 3:13:39 PM','IN'); 
insert into test (time,in_out_status) 
values ('11/29/2017 3:12:08 PM','OUT'); 
insert into test (time,in_out_status) 
values ('11/29/2017 3:10:49 PM','IN'); 
insert into test (time,in_out_status) 
values ('11/29/2017 10:08:47 AM','IN'); 
insert into test (time,in_out_status) 
values ('11/29/2017 10:00:20 AM','OUT'); 
insert into test (time,in_out_status) 
values ('11/29/2017 9:48:43 AM','IN'); 
insert into test (time,in_out_status) 
values ('11/29/2017 9:48:28 AM','OUT'); 

select 
  last_out as my_out ,
  last_in as my_in ,
  round(((last_out - last_in)*24*60),2) diff
from test match_recognize (
  order by time 
  measures 
    max (time_in.time) as last_in, 
    max (time_out.time) as last_out 
  pattern ( time_in+ time_out+ ) 
  define 
    time_in as in_out_status = 'IN', 
    time_out as in_out_status = 'OUT' 
)
order by last_in, last_out;

MY_OUT                   MY_IN                    DIFF    
11/29/2017 10:00:20 am   11/29/2017 09:48:43 am     11.62 
11/29/2017 03:12:08 pm   11/29/2017 03:10:49 pm      1.32 
11/29/2017 03:16:55 pm   11/29/2017 03:13:39 pm      3.27 
11/29/2017 05:00:55 pm   11/29/2017 04:50:56 pm      9.98 
11/29/2017 06:30:54 pm   11/29/2017 05:15:25 pm     75.48

Thank you'

Adrian.banica, March 09, 2018 - 11:36 am UTC

My input data it's an Excel, so i worked with The data type varchar2 not with date because that it's my input.



Thank you for the support!
Chris Saxon
March 09, 2018 - 2:49 pm UTC

No problem. Though it doesn't matter what source is - always store dates as dates! (or timestamps ;)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.