Using the Lag Function to retrieve values between dates of specific gap period

Question

I have a dataset (given below).

SELL_DATE   PRODUCT_CODE    OLD_PRICE   NEW_PRICE
01/01/2016  A1               20          25
02/01/2016  A1               18          23
03/01/2016  A1               20          25
10/01/2016  A1               30          37
17/01/2016  A1               33          38
20/01/2016  A1               40          55
01/01/2016  A2               100         113
02/01/2016  A2               100         119
03/01/2016  A2               99          118
10/01/2016  A2               115         150
17/01/2016  A2               120         152
20/01/2016  A2               130         175

My output dataset should have a field that pulls the old price of a product 10 days prior to the sell date. It should look like as follows:

SELL_DATE   PRODUCT_CODE    OLD_PRICE   NEW_PRICE   OLD_PRICE_10_DAYS_AGO
01/01/2016  A1                  20        25          -
02/01/2016  A1                  18        23          -
03/01/2016  A1                  20        25          -
10/01/2016  A1                  30        37          20
17/01/2016  A1                  33        38          -
20/01/2016  A1                  40        55          30
01/01/2016  A2                  100      113          -
02/01/2016  A2                  100      119          -
03/01/2016  A2                  99       118          -
10/01/2016  A2                  115      150          100
17/01/2016  A2                  120      152          -
20/01/2016  A2                  130      175          115

I have tried the lag function partitioned by product_code, but stuck to get the lag function working for 10 day gap period. Please help.

Answer 1

Just use left join:

select t.*, t10.old_price
from t left join
     t t10
     on t.productcode = t10.productcode and
        t.selldate = dateadd(day, 10, t10.selldate);