-1

HI all, I am new to Oracle. My table structure is as follows. I need to find the end date/effective to date for each line. If no new records are there then end date should be SYSDATE. What will be the simple select query for this?

Please note effective to date should be next effective from date -1

Thanks in advance.

enter image description here

My sample output should be like

enter image description here

0

I assume this is per item_code, item_cost_gp. You can use analytical functions for this:

SELECT item_code, ...
     , coalesce(lead(eff_frm_date) 
                over (partition by item_code, item_cost_gp
                      order by eff_frm_date) - 1
               , SYSDATE) as eff_to_date
FROM ...

untested since you did not provide any ddl to test with

  • you should probably move the accept to @balazs-papp 's answer. The idea is similar, but he added more info that is of use for anyone looking at the accepted answer. – Lennart Jul 14 at 9:23
1

LEAD

Sample data:

with data as
(
  select 1010 as item_code, 'UAECOSTGP' as item_cost_gp, 5 as item_cost, 'AVK' as created_by, date'2018-09-12' as eff_frm_date from dual union all
  select 1010 as item_code, 'UAECOSTGP' as item_cost_gp, 5.3 as item_cost, 'AVK' as created_by, date'2019-03-29' as eff_frm_date from dual union all
  select 1010 as item_code, 'UAECOSTGP' as item_cost_gp, 5.8 as item_cost, 'AVK' as created_by, date'2019-06-06' as eff_frm_date from dual union all
  select 1010 as item_code, 'QARCOSTGP' as item_cost_gp, 3 as item_cost, 'AVK' as created_by, date'2019-03-29' as eff_frm_date from dual
)
select * from data;

 ITEM_CODE ITEM_COST  ITEM_COST CRE EFF_FRM_DA
---------- --------- ---------- --- ----------
      1010 UAECOSTGP          5 AVK 2018-09-12
      1010 UAECOSTGP        5.3 AVK 2019-03-29
      1010 UAECOSTGP        5.8 AVK 2019-06-06
      1010 QARCOSTGP          3 AVK 2019-03-29

Desired output:

with data as
(
  select 1010 as item_code, 'UAECOSTGP' as item_cost_gp, 5 as item_cost, 'AVK' as created_by, date'2018-09-12' as eff_frm_date from dual union all
  select 1010 as item_code, 'UAECOSTGP' as item_cost_gp, 5.3 as item_cost, 'AVK' as created_by, date'2019-03-29' as eff_frm_date from dual union all
  select 1010 as item_code, 'UAECOSTGP' as item_cost_gp, 5.8 as item_cost, 'AVK' as created_by, date'2019-06-06' as eff_frm_date from dual union all
  select 1010 as item_code, 'QARCOSTGP' as item_cost_gp, 3 as item_cost, 'AVK' as created_by, date'2019-03-29' as eff_frm_date from dual
)
select
  item_code, item_cost_gp, item_cost, created_by, eff_frm_date, 
  lead(eff_frm_date, 1, sysdate + 1) over (partition by item_code, item_cost_gp order by  eff_frm_date) -1 as eff_to_date
from data
order by 1, 2 desc, 5;

 ITEM_CODE ITEM_COST  ITEM_COST CRE EFF_FRM_DA EFF_TO_DAT
---------- --------- ---------- --- ---------- ----------
      1010 UAECOSTGP          5 AVK 2018-09-12 2019-03-28
      1010 UAECOSTGP        5.3 AVK 2019-03-29 2019-06-05
      1010 UAECOSTGP        5.8 AVK 2019-06-06 2019-07-14
      1010 QARCOSTGP          3 AVK 2019-03-29 2019-07-14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.