[Solved] SQL Server 2008 : calculate date and price change

by


Something like: 

DECLARE @artSales TABLE (artid int, dt date, price money);

INSERT @artSales
VALUES 
(1, '20170102', 10), (1, '20170108', 10), (1, '20170112', 8.50), (1, '20170115', 8.50),
(2, '20170102', 20), (2, '20170109', 20), (2, '20170112', 35), (2, '20170116', 40),
(3, '20170101', 500), (3, '20170111', 500), (3, '20170130', 500);

SELECT 
  artid,
  dt,
  oldPrice = price,
  PriceChange =
  CASE 
    LAG(price, 1, price) OVER (PARTITION BY artid ORDER BY dt) 
    WHEN price THEN 0 ELSE 1
  END,
  NewPrice = LAG(price, 1, price) OVER (PARTITION BY artid ORDER BY dt)
FROM @artSales;

Results:

artid       dt         oldPrice              PriceChange NewPrice
----------- ---------- --------------------- ----------- ---------------------
1           2017-01-02 10.00                 0           10.00
1           2017-01-08 10.00                 0           10.00
1           2017-01-12 8.50                  1           10.00
1           2017-01-15 8.50                  0           8.50
2           2017-01-02 20.00                 0           20.00
2           2017-01-09 20.00                 0           20.00
2           2017-01-12 35.00                 1           20.00
2           2017-01-16 40.00                 1           35.00
3           2017-01-01 500.00                0           500.00
3           2017-01-11 500.00                0           500.00
3           2017-01-30 500.00                0           500.00

UPDATE – FOR PRE-2012 SYSTEMS:

WITH prev AS
(
  SELECT rn = ROW_NUMBER() OVER (PARTITION BY artid ORDER BY dt), *
  FROM @artSales
)
SELECT 
  as1.artid,
  as1.dt,
  OldPrice    = as1.price,
  PriceChange = CASE WHEN as1.price <> as2.price THEN 1 ELSE 0 END,
  NewPrice    = ISNULL(as2.price, as1.price)
FROM prev as1
LEFT JOIN prev as2 ON as1.artid = as2.artid AND as1.rn = as2.rn+1;

2

solved SQL Server 2008 : calculate date and price change