CODEWITHSUNDEEP
sqloracle-databaserecursion
B-Rad
B-Rad

Reputation: 35

Oracle SQL create records based on DATES

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

enter image description here

Desired Ouput

enter image description here

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;

Upvotes: 0

Views: 48

Answers (2)

astentx
astentx

Reputation: 6750

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
ITEM LOC QTY TYPE_ EFF DISC
item1 loc1 280 before_promo 2022-11-29 2023-01-08
item1 loc1 280 promo 2023-01-08 2023-01-10
item1 loc1 300 promo 2023-01-08 2023-01-14
item1 loc1 310 promo 2023-01-08 2023-01-16
item1 loc1 310 after_promo 2023-01-16 2999-01-01

See SQL fiddle

UPD: If it is possible to have non-overlapping intervals, then you need to check this condition before date calculation. Below is updated script.

with prep as (
  select
    item, loc, qty,
    case
      when promo_prep < disc and promostart > eff
      then eff
    end as eff1,
    case
      when promo_prep < disc and promostart > eff
      then least(disc, promo_prep)
    end as disc1,
    case
      when promo_prep < disc and promostart > eff
      then least(disc, promo_prep)
    end as eff2,
    case
      when promo_prep < disc and promostart > eff
      then least(disc, promostart)
    end as disc2,
    case
      when promo_prep < disc and promostart > eff
      then least(disc, promostart)
    end as eff3,
    case
      when promo_prep < disc and promostart > eff
      then disc
    end as disc3,
    case
      when promo_prep >= disc or promo_prep is null or promostart <= eff or promostart is null
      then eff
    end as eff4,
    case
      when promo_prep >= disc or promo_prep is null or promostart <= eff or promostart is null
      then disc
    end as disc4
  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',
    (eff4, disc4) as 'no_promo'
  )
)
/*Include only valid combinations*/
where eff < disc

which for this sample data

ITEM LOC QTY EFF DISC PROMO_PREP PROMOSTART
item4 loc4 280 2022-11-29 2023-01-10 2023-01-08 2023-01-09
item4 loc4 300 2023-01-10 2023-01-14 2023-01-10 2023-01-25
item4 loc4 310 2023-01-14 2023-01-25 2023-01-10 2023-01-25
item4 loc4 311 2023-01-25 2023-10-01 2023-01-10 2023-01-25
item4 loc4 312 2023-10-01 2999-01-01 2023-01-10 2023-01-25

returns this output

ITEM LOC QTY TYPE_ EFF DISC
item4 loc4 280 before_promo 2022-11-29 2023-01-08
item4 loc4 280 promo 2023-01-08 2023-01-09
item4 loc4 280 after_promo 2023-01-09 2023-01-10
item4 loc4 300 promo 2023-01-10 2023-01-14
item4 loc4 310 promo 2023-01-10 2023-01-25
item4 loc4 311 no_promo 2023-01-25 2023-10-01
item4 loc4 312 no_promo 2023-10-01 2999-01-01

Upvotes: 1

onepan
onepan

Reputation: 954

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
ITEM LOC QTY EFF DISC
item1 loc1 280 2022-11-29T00:00:00Z 2023-01-08T00:00:00Z
item1 loc1 300 2023-01-10T00:00:00Z 2023-01-14T00:00:00Z
item1 loc1 310 2023-01-14T00:00:00Z 2999-01-01T00:00:00Z
item1 loc1 280 2023-01-08T00:00:00Z 2023-01-10T00:00:00Z

sqlfiddle

Upvotes: 0

Related Questions