Oracle SQL - how to check continuation of contracts without PL/SQL

Question

I want to check continuity of contracts partitioned by CONTRACT_ID, but I'd like to check contracts only where FLG = 0 and compare it by whole range (with FLG=1). For example I'll take the first date_to from row where FLG=1 and compare it by every range of dates. If date_to is between some range then I want to qualified the contract. Next date_to and the same situation. I have no idea how bite the problem. A big problem is that I can't use PL/SQL. Can I do it with SQL? Do somebody have idea how to check continuity of contracts in partition by contract_id? I don't know how to order records in partition. I tried few codes and I am giving up...

Thanks for every help.

  | CONTRAC_ID  |             DATE_FROM  |               DATE_TO  | FLG      | 
  |------------ |----------------------- |----------------------- |--------- |
  |          1  | 2016-01-01 00:00:00.0  | 2016-03-11 00:00:00.0  |       1  |
  |          1  | 2016-04-15 00:00:00.0  | 2016-06-05 00:00:00.0  |       1  |
  |          1  | 2016-05-10 00:00:00.0  | 2016-07-25 00:00:00.0  |       1  |
  |          1  | 2016-07-25 00:00:00.0  | 2016-08-22 00:00:00.0  |       0  |
  |          1  | 2017-01-19 00:00:00.0  | 2017-05-21 00:00:00.0  |       0  |
  |          1  | 2017-03-05 00:00:00.0  | 2017-04-30 00:00:00.0  |       0  |

EDIT:

On the example: We have three records where FLG=0, so we take a first date_to after some order. I'll use '2016-08-22' and correct procedure should check the date with every range of dates:

'2016-08-22' between '2016-01-01 00:00:00.0'  AND  '2016-03-11 00:00:00.0'
'2016-08-22' between '2016-04-15 00:00:00.0'  AND  '2016-06-05 00:00:00.0'
'2016-08-22' between '2016-05-10 00:00:00.0'  AND  '2016-07-25 00:00:00.0'
'2016-08-22' between '2017-01-19 00:00:00.0'  AND  '2017-05-21 00:00:00.0'
'2016-08-22' between '2017-03-05 00:00:00.0'  AND  '2017-04-30 00:00:00.0'

If one of these condition is TRUE then the contract will be qualify, else no.

Correct SELECT statement results:

  | CONTRAC_ID  |             DATE_FROM  |               DATE_TO  | FLG      | QUAL|
  |------------ |----------------------- |----------------------- |--------- |-----|
  |          1  | 2016-01-01 00:00:00.0  | 2016-03-11 00:00:00.0  |       1  |  1  |
  |          1  | 2016-04-15 00:00:00.0  | 2016-06-05 00:00:00.0  |       1  |  1  |
  |          1  | 2016-05-10 00:00:00.0  | 2016-07-25 00:00:00.0  |       1  |  1  |
  |          1  | 2016-07-25 00:00:00.0  | 2016-08-22 00:00:00.0  |       0  |  0  |
  |          1  | 2017-01-19 00:00:00.0  | 2017-05-21 00:00:00.0  |       0  |  0  |
  |          1  | 2017-03-05 00:00:00.0  | 2017-04-30 00:00:00.0  |       0  |  1  |

The last contract will be qualified because '2017-04-30 00:00:00.0' is between one of ranges:

2017-01-19 00:00:00.0  | 2017-05-21 00:00:00.0

First three rows have 1 beacuse FLG=1

Which version of Oracle are you using? This sort of thing is a lot easier with 12c due to the MATCH RECOGNIZE feature. — APC
MATCH RECOGNIZE compare next or previous value of current row and I need to check every date_to with all range of dates. — caruzo
Actually MATCH RECOGNIZE is a lot more flexible than you seem to think. — APC
Anyway, it would help if you were to annotate your sample data with the rows you want to compare, which ones fit the criteria, which ones don't. Also, please explain what you mean by qualifying the contract. What would that look like in a SELECT statement? — APC

Rene Rene · Accepted Answer · 2017-07-28T08:34:53

If I understand correctly you are looking for records with FLG=1 and for which you can find another record where the date_from column value falls in the date range of that other record.

Don't know how you identify individual records so I assume they have an ID column and that you table is named CONTRACTS.

select ID
from contracts c1
where c1.FLG=1 
and exists 
  (select 1 
   from contracts c2
   where c1.contrac_id=c2.contrac_id
   and c1.ID != c2.ID
   and c1.date_from between c2.date_from and c2.date_to)

Oracle SQL - how to check continuation of contracts without PL/SQL

1 Answers