Oracle version:
Oracle 19c Enterprise edition Realease 19.0.0.0.0
Create table script :
create table item_dtls(
item_id number,
detailed_desc varchar2(100),
legal_org char(16),
related_order varchar2(100),
update_date timestamp(6),
start_date timestamp(6),
end_date timestamp(6)
);
Insert statements :
Insert into BATCH2.ITEM_DTLS (ITEM_ID,DETAILED_DESC,LEGAL_ORG,RELATED_ORDER,UPDATE_DATE,START_DATE,END_DATE) values (101,'TEST123','200898 ','2309',to_timestamp('04-02-2020 10:50:25.412000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('01-02-2022 10:50:30.735000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('22-02-2026 10:50:35.931000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'));
Insert into BATCH2.ITEM_DTLS (ITEM_ID,DETAILED_DESC,LEGAL_ORG,RELATED_ORDER,UPDATE_DATE,START_DATE,END_DATE) values (101,'TEST Item','200897 ',null,to_timestamp('01-01-2023 10:50:25.412000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('10-01-2023 10:50:30.735000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('22-04-2024 10:50:35.931000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'));
Insert into BATCH2.ITEM_DTLS (ITEM_ID,DETAILED_DESC,LEGAL_ORG,RELATED_ORDER,UPDATE_DATE,START_DATE,END_DATE) values (101,null,null,'120',to_timestamp('02-02-2024 10:50:25.412000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('01-02-2024 10:50:30.735000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('22-04-2025 10:50:35.931000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'));
Insert into BATCH2.ITEM_DTLS (ITEM_ID,DETAILED_DESC,LEGAL_ORG,RELATED_ORDER,UPDATE_DATE,START_DATE,END_DATE) values (101,'Fleet sale',null,null,to_timestamp('21-02-2025 11:07:49.996000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('01-02-2026 10:50:30.735000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('31-12-2100 11:08:26.292000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'));
Insert into BATCH2.ITEM_DTLS (ITEM_ID,DETAILED_DESC,LEGAL_ORG,RELATED_ORDER,UPDATE_DATE,START_DATE,END_DATE) values (101,'IT projects','200820 ','2132',to_timestamp('21-02-2019 11:07:49.996000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('01-02-2024 10:50:30.735000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('31-12-2100 11:08:26.292000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'));
Insert into BATCH2.ITEM_DTLS (ITEM_ID,DETAILED_DESC,LEGAL_ORG,RELATED_ORDER,UPDATE_DATE,START_DATE,END_DATE) values (102,'compliance','3000 ','123',to_timestamp('04-02-2020 10:50:25.412000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('01-02-2020 10:50:25.412000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('31-12-2100 11:08:26.292000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'));
Insert into BATCH2.ITEM_DTLS (ITEM_ID,DETAILED_DESC,LEGAL_ORG,RELATED_ORDER,UPDATE_DATE,START_DATE,END_DATE) values (102,'Debt colln','200 ','111',to_timestamp('04-02-2018 10:50:25.412000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('01-01-2018 10:50:25.412000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('31-12-2100 11:08:26.292000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'));
Insert into BATCH2.ITEM_DTLS (ITEM_ID,DETAILED_DESC,LEGAL_ORG,RELATED_ORDER,UPDATE_DATE,START_DATE,END_DATE) values (103,'TEST Item1','100 ','12',to_timestamp('04-02-2018 10:50:25.412000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('01-01-2018 10:50:25.412000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'),to_timestamp('31-12-2100 11:08:26.292000000 AM','DD-MM-RRRR fmHH12:fmMI:SSXFF AM'));
expected output :

In this table, based on item id we can see details of that item.
This table has different start date and end date for ech item. sometimes you may see that different rows have overlapping periods (start and end date)
Below is the expected result for item_id=101:
- Get min(start_date) and max(end_date) to determine the full life of the item (each item has more than one row with different or overlapping start and end dates). In every step below, recent record is identified by update_date column.
- Based on update_date, determine what is the most recent record.
- 1st record in the screenshot is for begining period (start date): 01-02-2020. For the same record, you will see end date as 22-02-2026. But in my expected result end date will change to 09-01-2023. This is because, 10-01-2023 onwards there is another version of record with latest update date. hence that record and its values to be considered.
- For the 2nd record, end date will be 31-01-2024 because there is another most recent record starts from 01-02-2024 based on update date.. Also note that for the 2nd record, column value - RELATED_ORDER value has come from previous row because current row has null values. Whenever there is null value, we will retrive previous version value.
- Aagin 4th record is same as 1st record for some portion of peiord untill we find next most recent version.
- record with update_date 21-02-2019 is ignored because it is older record and we found most recent than that for the start and end dates
Same steps followed for item_id=102 & 103.
can you kindly assist me on query to get desired results? Thanks a lot in advance.