CODEWITHSUNDEEP
oracle-databaseoracle11goracle-sqldeveloper
Koti Raavi
Koti Raavi

Reputation: 310

How to get latest active record

Here is my data. Member can be enroll multiple times and can change plan anytime,I need to get C_level data for latest near to getdate that is "8/1/2017" for ID 1, For ID 2 doesn't have latest data, that case we have to show 12/31/2016 record.

ID Start_Date End_Date   C_Level
1  1/1/2016  12/31/2016    1
1  1/1/2017  8/1/2017      2
1  9/1/2017  12/31/2017    3
1  1/1/2018  12/31/2018    0
2  1/1/2015  12/31/2015    2
2  1/1/2016  12/31/2016    3

Upvotes: 0

Views: 66

Answers (1)

krokodilko
krokodilko

Reputation: 36127

If I understand your requirements right, then this query should give what you want:

WITH current_running AS (
  SELECT to_date( '8/1/2017','mm/dd/rrrr') As Current_running_date
  FROM dual
)
SELECT * FROM (
   SELECT t.*,
          row_number() Over (partition by id order by end_date desc ) As rn,
          c.Current_running_date
   FROM Table1 t
   JOIN current_running c
   ON c.Current_running_date >= ANY( t.Start_Date, t.End_Date )
)
WHERE rn = 1
;

Demo: http://sqlfiddle.com/#!4/33de0/10

For current_running_date = 2017-08-01 it gives:

| ID |            START_DATE |              END_DATE | C_LEVEL | RN | CURRENT_RUNNING_DATE |
|----|-----------------------|-----------------------|---------|----|----------------------|
|  1 | 2017-01-01 00:00:00.0 | 2017-08-01 00:00:00.0 |       2 |  1 | 2017-08-01T00:00:00Z |
|  2 | 2016-01-01 00:00:00.0 | 2016-12-31 00:00:00.0 |       3 |  1 | 2017-08-01T00:00:00Z |

while for current_running_date = 2016-07-15

| ID |            START_DATE |              END_DATE | C_LEVEL | RN | CURRENT_RUNNING_DATE |

|----|-----------------------|-----------------------|---------|----|----------------------|
|  1 | 2016-01-01 00:00:00.0 | 2016-12-31 00:00:00.0 |       1 |  1 | 2016-07-15T00:00:00Z |
|  2 | 2016-01-01 00:00:00.0 | 2016-12-31 00:00:00.0 |       3 |  1 | 2016-07-15T00:00:00Z |

Upvotes: 1

Related Questions