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


i want check continuity of contracts partitioned contract_id, i'd check contracts flg = 0 , compare whole range (with flg=1). example i'll take first date_to row flg=1 , compare every range of dates. if date_to between range want qualified contract. next date_to , same situation. have no idea how bite problem. big problem can't use pl/sql. can sql? have idea how check continuity of contracts in partition contract_id? don't know how order records in partition. tried few codes , giving up...

thanks 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 example: have 3 records flg=0, take first date_to after order. i'll use '2016-08-22' , correct procedure should check date every range of dates:

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

if 1 of these condition true contract 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 qualified because '2017-04-30 00:00:00.0' between 1 of ranges:

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

first 3 rows have 1 beacuse flg=1

if understand correctly looking records flg=1 , can find record date_from column value falls in date range of other record.

don't know how identify individual records assume have id column , table named contracts.

select id contracts c1 c1.flg=1  , exists    (select 1     contracts c2    c1.contrac_id=c2.contrac_id    , c1.id != c2.id    , c1.date_from between c2.date_from , c2.date_to)

Comments

Post a Comment

Popular posts from this blog

php - Vagrant up error - Uncaught Reflection Exception: Class DOMDocument does not exist -

i've set bento/ubuntu-16.04 vagrant box using php 5.6, , getting following error quite few times when try , run shell script: "uncaught reflection exception: class domdocument not exist". i've tried adding following top of script (not together), each no success: "sudo apt-get install php5.6-xml", "sudo apt-get install php5-xml", "sudo apt-get install php-dom" i've tried resetting server "sudo service apache2 restart". does have suggestions?
Read more

vue.js - Create hooks for automated testing -

Image
qa guys complained can't automate frontend testing. because our html looks same outside. lazy hack did this <template> <div :role="$options.name"> ... </div> </template> <script> export default { name: 'vmcomponentname' } </script> which takes name script , applies html. in browser get: <div role="vmcomponentname"> ... </div> line :role="$options.name" goes in every component. is there dry er solutions? please share. additional details i'll explain in details looking for. imagine have vmusercreate form creates user. test case can create user . to automatically test without role, have use following selector: .wrapper > .wrapper > .wrapper > .submit-button the test extremely brittle (will break lot). if use roles can use [role="vmusercreateform"] .submit-button selector. many folds less brittle. so basically, i'm ...
Read more

Add new key value to json node in java -

i have json file : { "id":1, "name":"abc", "addressdetails": { "city":"newyork" } } i wanted add 1 more key-value ("pincode" : "414141") node 'addressdetails'. i tried using : objectmapper mapper = new objectmapper(); jsonnode root = mapper.readtree(new file("d://test.json")); objectnode node = mapper.createobjectnode(); node.with("addressdetails").put("pincode", "414141"); but it's not getting added, there way can ? you can try you should modify file path objectmapper mapper = new objectmapper(); objectnode nodes = mapper.readvalue(new file("d:\\test.txt"), objectnode.class); nodes.with("addressdetails").put("pincode", "414141"); mapper.writer().writevalue(new file("d:\\test.txt"), nodes);
Read more