Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Query to get record details beased on recent update

SK KFeb 22 2025 — edited Feb 22 2025

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:

  1. 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.
  2. Based on update_date, determine what is the most recent record.
  3. 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.
  4. 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.
  5. Aagin 4th record is same as 1st record for some portion of peiord untill we find next most recent version.
  6. 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.
This post has been answered by Frank Kulash on Feb 22 2025
Jump to Answer

Comments

Post Details

Added on Feb 22 2025
7 comments
261 views