In desperate need of help from all Oracle SQL guru's. Need to generate output for effective and discontinue dates factoring in 2 other dates namely promo_prep date and promo_start date.

I need to check if promo_prep is greater than eff and less DISC. If it is then become the new disc and the new eff for the next row. Up until the promostart date, so if eff and disc changes happen between promo dates they should remain. Only when promo dates fall between EFF and DISC do they need to be applied and create a new record. Images shown below. Appreaciate any help with this query.

Thank you

Table view before transformation

Desired Ouput

DDL

CREATE TABLE TEST1
( item        varchar2(20),
  loc         varchar2(20),
  qty         number,
  eff         date,
  disc        date,
  promo_prep  date,
  promostart  date
);
--
Insert into TEST1
   (ITEM, LOC, QTY, EFF, DISC, 
    PROMO_PREP, PROMOSTART)
 Values
   ('item1', 'loc1', 280, TO_DATE('11/29/2022', 'MM/DD/YYYY'), TO_DATE('1/10/2023', 'MM/DD/YYYY'), 
    TO_DATE('1/8/2023', 'MM/DD/YYYY'), TO_DATE('1/16/2023', 'MM/DD/YYYY'));
Insert into TEST1
   (ITEM, LOC, QTY, EFF, DISC, 
    PROMO_PREP, PROMOSTART)
 Values
   ('item1', 'loc1', 300, TO_DATE('1/10/2023', 'MM/DD/YYYY'), TO_DATE('1/14/2023', 'MM/DD/YYYY'), 
    TO_DATE('1/8/2023', 'MM/DD/YYYY'), TO_DATE('1/16/2023', 'MM/DD/YYYY'));
Insert into TEST1
   (ITEM, LOC, QTY, EFF, DISC, 
    PROMO_PREP, PROMOSTART)
 Values
   ('item1', 'loc1', 310, TO_DATE('1/14/2023', 'MM/DD/YYYY'), TO_DATE('1/1/2999', 'MM/DD/YYYY'), 
    TO_DATE('1/8/2023', 'MM/DD/YYYY'), TO_DATE('1/16/2023', 'MM/DD/YYYY'));
COMMIT;

CodePudding user response：

You may generate three possible pairs of effective and discontinue dates for each row and then unpivot the result:

with prep as (
  select
    item, loc, qty,
    eff as eff1,
    least(disc, promo_prep) as disc1,
    least(disc, promo_prep) as eff2,
    least(disc, promostart) as disc2,
    least(disc, promostart) as eff3,
    disc as disc3
  from test1
)
select *
from prep
unpivot (
  (eff, disc) for type_ in (
    (eff1, disc1) as 'before_promo',
    (eff2, disc2) as 'promo',
    (eff3, disc3) as 'after_promo'
  )
)
/*Include only valid combin*/
where eff < disc

See SQL fiddle

CodePudding user response：

You can do this pretty straightforwardly by just appending a row when promo_prep is between the eff and disc dates

select
  item,
  loc,
  qty,
  eff,
  case 
    when promo_prep between eff and disc then promo_prep
    else disc
  end as disc
from test1

union all

select
  item,
  loc,
  qty,
  promo_prep as eff,
  disc
from test1
where 
  promo_prep between eff and disc
  

sqlfiddle