Sid ABS Sid ABS - 1 year ago 70
SQL Question

Select Active/Inactive based on date

I have the following tables:


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



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

from user_id u 
where u.user_id not in (select 
from transaction t 

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

Any idea on how to do it.

Answer Source

You can use something like the following:

select, 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_ = and
where 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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download