Skip to content
Advertisement

SQL Server check for prior record only

I have these columns: type(char), date , price. I would like to check if the same type’s price has not changed in the previous record.

I tried this for start:

SELECT *
FROM table t1, table t2
WHERE t1.date > t2.date and t1.type = t2.type

I only want to check for the date prior not for every date that is smaller.

This table stores price changes.

product_type change_date price
‘A’ 2020.02.18 500
‘A’ 2020.02.20 750
‘A’ 2020.02.22 800
‘B’ 2020.02.22 500
‘B’ 2020.02.27 900
‘C’ 2020.02.22 400
‘C’ 2020.02.29 700

I want to put this table next to it self to see if the price remained unchanged like this:

product_type change_date price product_type change_date price
‘A’ 2020.02.20 750 ‘A’ 2020.02.18 500
‘A’ 2020.02.22 800 ‘A’ 2020.02.20 750
‘B’ 2020.02.27 900 ‘B’ 2020.02.22 500
‘C’ 2020.02.29 700 ‘C’ 2020.02.22 400

I only want to display rows that show no price change, so price = previous price.

Advertisement

Answer

Try this:

select 
    t.*, 
    lag(price) over(partition by type order by date) as previous_price 
from table t
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement