Sid ABS Sid ABS - 2 months ago 15
SQL Question

Select Active/Inactive based on date

I have the following tables:

Table TRANSACTION

ID   | DATE  
----   ------
1    | 23-01-16
2    | 23-01-16
1    | 24-01-16


Table USER_ID

 ID
----
1
2


Now the issue is, between the date range 23-01-16 and 25-01-16, how would I detect that 2 is inactive on 24-01-16 and 1,2 are inactive on 25-01-16

If I am using

select u.id 
from user_id u 
where u.user_id not in (select t.id 
from transaction t 
where t.date>='23-01-16' 
and t.date<='25-01-16') 


I would get inactive ID between that range but not according to the specific dates.

Any idea on how to do it.

Answer

You can use something like the following:

select user_id.id, day
from user_id
     cross join (
                select date '2016-01-23' + level -1 as day
                from dual
                connect by  date '2016-01-23' + level -1 <= date '2016-01-25'
                ) days
     left outer join transaction_ on ( user_id.id_ = transaction.id and
                                       days.day = transaction.date
                                     )
where transaction.date is null

The part with the connect by builds a list of days, according to you input interval (23-25/01); the remaining part uses an outer join to check only the missing occurrences.