CODEWITHSUNDEEP
sqloracle-database
Javatar
Javatar

Reputation: 703

Oracle SQL - Get difference between dates based on check in and checkout records

Assume I have the following table data.

#  |  USER  |  Entrance |  Transaction Date Time
-----------------------------------------------
1  |  ALEX  |  INBOUND  |  2020-01-01  10:20:00
2  |  ALEX  |  OUTBOUND |  2020-01-02  10:00:00
3  |  ALEX  |  INBOUND  |  2020-01-04  11:30:00
4  |  ALEX  |  OUTBOUND |  2020-01-07  15:00:00
5  |  BEN   |  INBOUND  |  2020-01-08  08:00:00
6  |  BEN   |  OUTBOUND |  2020-01-09  09:00:00

I would like to know the total of how many days the user has stay outbound.

For each inbound and outbound is considered one trip, every trip exceeded 24 hours is considered as 2 days.

Below is my desired output:

No. of Days   |   Trips Count
----------------------------------
Stay < 1 day  |   1
Stay 1 day    |   1
Stay 2 days   |   0
Stay 3 days   |   0
Stay 4 days   |   1

Upvotes: 1

Views: 105

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I would use lead() and aggregation. Assuming that the rows are properly interlaced:

select floor( (next_dt - dt) ) as num_days, count(*)
from (select t.*,
             lead(dt) over (partition by user order by dt) as next_dt
      from trips t
     ) t
where entrance = 'INBOUND'
group by floor( (next_dt - dt) )
order by num_days;

Note: This does not include the 0 rows. That does not seem central to your question and is a significant complication.

Upvotes: 2

Turo
Turo

Reputation: 4914

I still don't know what you mean with < 1 day, but this I got this far

Setup

create table trips (id number, name varchar2(10), entrance varchar2(10), ts TIMESTAMP); 
insert into trips values( 1  ,  'ALEX','INBOUND', TIMESTAMP '2020-01-01 10:20:00');
insert into trips values(2  ,  'ALEX','OUTBOUND',TIMESTAMP '2020-01-02 10:00:00');
insert into trips values(3  ,  'ALEX','INBOUND',TIMESTAMP '2020-01-04 11:30:00');
insert into trips values(4  ,  'ALEX','OUTBOUND',TIMESTAMP '2020-01-07  15:00:00');
insert into trips values(5  ,  'BEN','INBOUND',TIMESTAMP '2020-01-08 08:00:00');
insert into trips values(6  ,  'BEN','OUTBOUND',TIMESTAMP '2020-01-09 07:00:00');

Query

select decode (t.days, 0 , 'Stay < 1 day', 1, 'Stay 1 day', 'Stay ' || t.days ||  ' days') Days , count(d.days) Trips_count
FROM (Select Rownum - 1  days From dual Connect By Rownum <= 6) t   left join
(select  extract (day from b.ts - a.ts) + 1 as days from trips a
inner join trips b on a.name = b.name 
and a.entrance = 'INBOUND' 
and b.entrance = 'OUTBOUND' 
and a.ts < b.ts 
and not exists (select ts from trips where entrance = 'OUTBOUND' and ts > a.ts and ts < b.ts)) d
on t.days = d.days
group by t.days order by t.days

Result

DAYS            | TRIPS_COUNT
----------------|------------
Stay < 1 day    | 0
Stay 1 day      | 2
Stay 2 days     | 0
Stay 3 days     | 0
Stay 4 days     | 1
Stay 5 days     | 0

You could replace the 6 with a select max with the second subquery repeated

Upvotes: 0

Related Questions