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

Question

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.

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 1

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';

Answer 2

I would use one of these solutions with MS SQL Server. Hopefully one of them will apply to your problem.

Pure SQL statement would look like this:

select
    t.product_id, t.price, s.start_date, t.end_date
from 
    product t
    outer apply
    (
        select top 1 
            end_date start_date
        from
            product o
        where
            o.end_date < t.end_date
        order by
            o.end_date desc
    ) s

The cross apply for each record returned can be a performance problem even with good indexing.

If your SQL server supports the LAG function:

select
    t.product_id, t.price, 
    LAG(T.end_date) over (order by t.end_date),
    t.end_date
from 
    product t

Or you may find a way to do the same thing with variables in an update statement to "remember" the value in the previously updated record like the T-SQL:

-- Insert the desired output into a table variable that also has a start_date field.
-- Be sure to insert the records ordered by the date value.

declare @output table (product_id int, price numeric(10,2), [start_date] datetime, [end_date] datetime)

insert @output (product_id, price, end_date)
select 1, 10, '1/1/2015'
union all select  2, 11, '2/1/2015'
union all select 3, 15, '3/1/2015'
union all select 4, 20, '4/1/2015'
order by 3

-- Update the start date using the end date from the previous record
declare @start_date datetime, @end_date datetime

update 
    @output
set
    @start_date = @end_date,
    @end_date = end_date,
    start_date = @start_date

select * from @output

I don't think this technique is recommended by Microsoft, but it has served me well and worked consistently. I only used this technique with table variables. I would be less inclined to trust the update sequence of records in in an actual table. Now I would use LAG() instead.

Answer 3

Something like this:

select Product_id, 
       Price,        
       hist_lastupdated, 
       lag(hist_lastupdated) over (partition by product_id order by hist_lastupdated) as start_date, 
       hist_lastupdated as end_date
from the_table

You didn't explain where the start_date for the first column is calculated. If that is beginning of the month from hist_lastupdated you can do something like this:

lag(hist_lastupdated, 1, date_trunc('month', hist_lastupdated)) over (...)

Answer 4

I'm not sure how you would do this with just SQL but if you're able to do a bit of scripting you can write up a quick program that goes something like this (pseudocode):

lines = execute(SELECT product_id, price, hist_lastupdated FROM ProductTable)

startDate = 00:00:00 2014-05-01

outputLines = []

for row in lines:

    outLine = []
    outline.append(row[0])
    outline.append(row[1])
    outline.append(row[2])
    outline.append(startDate)
    outline.append(row[2])

    startDate = row[2]

#Now do what you want with the output you have in a nice list of lists in the format you need, insert into a different table, write to a file, whatever you want.