CODEWITHSUNDEEP
sqloracle-database
Kuntal Shaw
Kuntal Shaw

Reputation: 1395

How to find particular dates between two rows which have dates in oracle?

I'm a beginner and learning Oracle queries and stuck with a problem.

Consider the following table : 

    RecordNumber|   StartDate   |   StopDate
-------------------------------------------------
        1211    |   15-Oct-15   |   17-Oct-15
        1211    |   18-Oct-15   |   25-Oct-15
        1211    |   28-Oct-15   |   02-Nov-15
        1211    |   05-Nov-15   |   18-Nov-15
        1211    |   21-Nov-15   |   23-Nov-15
        1012    |   18-Sep-15   |   19-Nov-15
        1012    |   21-Nov-15   |    (null)

Table name - Records

A record is active from its StartDate till the StopDate. If the StopDate is null, it means the record is still active. I'm clueless with writing a query in Oracle which finds the dates on which a record was inactive, considering the last StopDate to be the termination date.

The output should be somewhat like:

RecordNumber| InactiveDate   
------------------------------------
    1211    |   26-Oct-15     
    1211    |   27-Oct-15      
    1211    |   03-Nov-15      
    1211    |   04-Nov-15      
    1211    |   19-Nov-15       
    1211    |   20-Nov-15   
    1012    |   20-Nov-15

Any help would be appreciated. Thanks.

Upvotes: 3

Views: 105

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132720

First, you need a way of generating all the dates of interest - e.g. all the dates from 26-Oct-15 to 20-Nov-15 inclusive (since that is the range you have used in your example output). This query will do that:

select date '2015-10-25' + rownum as d
from dual
connect by level < 30;

D
---------
26-OCT-15
27-OCT-15
28-OCT-15
29-OCT-15
30-OCT-15
31-OCT-15
01-NOV-15
02-NOV-15
03-NOV-15
04-NOV-15
05-NOV-15
06-NOV-15
07-NOV-15
08-NOV-15
09-NOV-15
10-NOV-15
11-NOV-15
12-NOV-15
13-NOV-15
14-NOV-15
15-NOV-15
16-NOV-15
17-NOV-15
18-NOV-15
19-NOV-15
20-NOV-15

You can then cross join that to the records data to get a Cartesian product of all recordnumber date combinations of interest:

with dates as
( select date '2015-10-25' + rownum as d
  from dual
  connect by level < 27
)

select distinct recordnumber, d
from   records r
       cross join dates   

RECORDNUMBER D
------------ ---------
        1012 26-OCT-15
        1012 27-OCT-15
        1012 28-OCT-15
        1012 29-OCT-15
        1012 30-OCT-15
        1012 31-OCT-15
        1012 01-NOV-15
        1012 02-NOV-15
        1012 03-NOV-15
        1012 04-NOV-15
        1012 05-NOV-15
        1012 06-NOV-15
        1012 07-NOV-15
        1012 08-NOV-15
        1012 09-NOV-15
        1012 10-NOV-15
        1012 11-NOV-15
        1012 12-NOV-15
        1012 13-NOV-15
        1012 14-NOV-15
        1012 15-NOV-15
        1012 16-NOV-15
        1012 17-NOV-15
        1012 18-NOV-15
        1012 19-NOV-15
        1012 20-NOV-15
        1211 26-OCT-15
        1211 27-OCT-15
        1211 28-OCT-15
        1211 29-OCT-15
        1211 30-OCT-15
        1211 31-OCT-15
        1211 01-NOV-15
        1211 02-NOV-15
        1211 03-NOV-15
        1211 04-NOV-15
        1211 05-NOV-15
        1211 06-NOV-15
        1211 07-NOV-15
        1211 08-NOV-15
        1211 09-NOV-15
        1211 10-NOV-15
        1211 11-NOV-15
        1211 12-NOV-15
        1211 13-NOV-15
        1211 14-NOV-15
        1211 15-NOV-15
        1211 16-NOV-15
        1211 17-NOV-15
        1211 18-NOV-15
        1211 19-NOV-15
        1211 20-NOV-15

52 rows selected.

Another query will return the dates where records are active:

with dates as
( select date '2015-10-25' + rownum as d
from dual
connect by level < 27
)
select distinct recordnumber, d
from   records r
       join dates
       on dates.d between r.startdate and nvl(r.stopdate, dates.d)
order by recordnumber, d;

RECORDNUMBER D
------------ ---------
        1012 26-OCT-15
        1012 27-OCT-15
        1012 28-OCT-15
        1012 29-OCT-15
        1012 30-OCT-15
        1012 31-OCT-15
        1012 01-NOV-15
        1012 02-NOV-15
        1012 03-NOV-15
        1012 04-NOV-15
        1012 05-NOV-15
        1012 06-NOV-15
        1012 07-NOV-15
        1012 08-NOV-15
        1012 09-NOV-15
        1012 10-NOV-15
        1012 11-NOV-15
        1012 12-NOV-15
        1012 13-NOV-15
        1012 14-NOV-15
        1012 15-NOV-15
        1012 16-NOV-15
        1012 17-NOV-15
        1012 18-NOV-15
        1012 19-NOV-15
        1211 28-OCT-15
        1211 29-OCT-15
        1211 30-OCT-15
        1211 31-OCT-15
        1211 01-NOV-15
        1211 02-NOV-15
        1211 05-NOV-15
        1211 06-NOV-15
        1211 07-NOV-15
        1211 08-NOV-15
        1211 09-NOV-15
        1211 10-NOV-15
        1211 11-NOV-15
        1211 12-NOV-15
        1211 13-NOV-15
        1211 14-NOV-15
        1211 15-NOV-15
        1211 16-NOV-15
        1211 17-NOV-15
        1211 18-NOV-15

45 rows selected.

Combining these, we can subtract the dates where the records are active from all the dates to get the dates where they are inactive:

with dates as
( select date '2015-10-25' + rownum as d
  from dual
  connect by level < 27
)
select recordnumber, d
from   records r
       cross join dates
minus
select recordnumber, d
from   records r
       join dates
       on dates.d between r.startdate and nvl(r.stopdate, dates.d)
order by recordnumber, d;

RECORDNUMBER D
------------ ---------
        1012 20-NOV-15
        1211 26-OCT-15
        1211 27-OCT-15
        1211 03-NOV-15
        1211 04-NOV-15
        1211 19-NOV-15
        1211 20-NOV-15

Upvotes: 3

Related Questions