Is contract active on a given date

user_1443324 0
Viewed 51

In my table contracts I do have all contracts and orders (order belong to particular contracts defined by parent id pid). Contracts and orders are distinguished by id_type;

  • 1 = contract (active at the beginning)
  • 2 = deactivation order (contract becomes inactive)
  • 3 = reactivation order (contract becomes active again)

Contracts can be deactivated or reactivated many times. Also, contracts can be deactivated and never reactivated again.

table of records:

id | pid | id_type | start_date
===+=====+=========+===========
20 |     |       1 | 2021-01-01 --> contract 20 started and active
38 |  20 |       2 | 2021-02-15 --> contract 20 temporarily deactivated
42 |  20 |       3 | 2021-02-25 --> contract 20 activated again
54 |  20 |       2 | 2021-04-01 --> contract 20 temporarily deactivated
95 |  20 |       3 | 2021-04-15 --> contract 20 activated again
30 |     |       1 | 2021-01-12 --> contract 30 started and active

I need SQL query which will return whether the contract is active or deactivated on a given date.

For example for date 2021-02-20 I should get that contract 20 is inactive.

I made some tries with LAG/LEAD functions but without success.

oracle sql
1 Answers

You can get the most recent row on or before a particular date using:

select t.*
from (select t.*,
             row_number() over (partition by coalesce(pid, id) order by start_date desc) as seqnum
      from t
      where start_date <= date '2021-02-20'
     ) t
where seqnum = 1;

If you only want the status and date, then you can also use group by and keep:

select coalesce(pid, id), max(start_date),
       max(id_type) keep (dense_rank first order by start_date desc) as id_type
from t
where start_date <= date '2021-02-20'
group by coalesce(pid, id)
user_11440350
Related