user3299124 user3299124 - 2 months ago 6
SQL Question

Previous row end date as the next row start date in SQL

Need Some help Please,

I have a Field called 'hist_lastupdated' that contains the last updated date of the modification of the price of a product.

Based in this field, i want to extract the start date and the end date of the modification.
enter image description here

In fact i have this:

**Product_id , Price , hist_lastupdated**
284849 18.95 2015-05-29 00:53:55
284849 15.95 2015-08-14 01:04:46
284849 18.95 2016-06-11 00:50:31
284849 15.95 2016-08-24 00:45:11


And i want to get the result like that :

**Product_id , Price , hist_lastupdated ,start_date , End_date**
284849 18.95 2015-05-29 00:53:55 2014-05-01 00:00:00 2015-05-29 00:53:55
284849 15.95 2015-08-14 01:04:46 2015-05-29 00:53:55 2015-08-14 01:04:46
284849 18.95 2016-06-11 00:50:31 2015-08-14 01:04:46 2016-06-11 00:50:31
284849 15.95 2016-08-24 00:45:11 2016-06-11 00:50:31 2016-08-24 00:45:11


In two word, the start date is the end date of the previous line
i have many product id

Answer

enter image description here

This is the solution that i find it,i wanted to work with the lag function but the result is not what i wanted to have.

The solution :

WITH 
price_table_1 as (
   select
   -1 + ROW_NUMBER() over (partition by t1.product_id,t1.id ,t1.channel_id)  as rownum_w1,
   t1.id,
   t1.product_id,
   t1.channel_id,
   t1.member_id,
   t1.quantity,
   t1.price,
   t1.promo_dt_start,
   t1.promo_dt_end,
   t1.hist_lastupdated
FROM dwh_prod.hist_prices t1
where   t1.channel_id='1004' and t1.product_id = '5896'  and t1.quantity = '1' and t1.promo_dt_start is null
order by t1.product_id,t1.channel_id,t1.hist_lastupdated
),price_table_2 as (
   select
   ROW_NUMBER() over (partition by t2.product_id,t2.id ,t2.channel_id) as     rownum_w2,
   t2.id,
   t2.product_id,
   t2.channel_id,
   t2.member_id,
   t2.quantity,
   t2.price,
   t2.promo_dt_start,
   t2.promo_dt_end,
   t2.hist_lastupdated
FROM dwh_prod.hist_prices t2
where    t2.channel_id='1004' and t2.product_id = '5896'  and t2.quantity = '1' and t2.promo_dt_start is null
order by t2.product_id,t2.channel_id,t2.hist_lastupdated
)

   select
   t1.id,
   t1.product_id,
   t1.channel_id,
   t1.member_id,
   t1.quantity,
   t1.price,
   t1.promo_dt_start,
   t1.promo_dt_end,
   t2.hist_lastupdated as start_date,
   t1.hist_lastupdated as end_date

FROM price_table_1 t1
inner join price_table_2 t2
on t2.product_id = t1.product_id and t2.id = t1.id and t2.channel_id =        t1.channel_id
and rownum_w1 = (rownum_w2)
UNION ALL
select
   t1.id,
   t1.product_id,
   t1.channel_id,
   t1.member_id,
   t1.quantity,
   t1.price,
   t1.promo_dt_start,
   t1.promo_dt_end,
   CONVERT(TIMESTAMP,'2014-01-01') as start_date,
   t1.hist_lastupdated as end_date

FROM price_table_1 t1 
where rownum_w1 = '0';