CODEWITHSUNDEEP
sqlsql-serversql-server-2014
Alessandro
Alessandro

Reputation: 3761

Calculate discount between weeks

I have a table containing product price data, like that:

ProductId   RecordDate                  Price
46          2015-01-17 14:35:05.533     112.00
47          2015-01-17 14:35:05.533     88.00
45          2015-01-17 14:35:05.533     134.00

I have been able to group data by week and product, with this query:

SET DATEFIRST 1;

SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, [RecordDate]), 0) AS [Week], ProductId, MIN([Price]) AS [MinimumPrice]
FROM [dbo].[ProductPriceHistory]
GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, 0, [RecordDate]), 0), ProductId
ORDER BY ProductId, [Week]

obtaining this result:

Week                       Product    Price
2015-01-12 00:00:00.000    1          99.00
2015-01-19 00:00:00.000    1          98.00
2015-01-26 00:00:00.000    1          95.00
2015-02-02 00:00:00.000    1          95.00
2015-02-09 00:00:00.000    1          95.00
2015-02-16 00:00:00.000    1          95.00
2015-02-23 00:00:00.000    1          80.00
2015-03-02 00:00:00.000    1          97.00
2015-03-09 00:00:00.000    1          85.00
2015-01-12 00:00:00.000    2          232.00
2015-01-19 00:00:00.000    2          233.00
2015-01-26 00:00:00.000    2          194.00
2015-02-02 00:00:00.000    2          194.00
2015-02-09 00:00:00.000    2          199.00
2015-02-16 00:00:00.000    2          199.00
2015-02-23 00:00:00.000    2          199.00
2015-03-02 00:00:00.000    2          214.00

Now for each product I'd like to get the difference between the last two week values, so that I can calculate the discount. I don't know how to write this as a SQL Query!

EDIT: Expected output would be something like that:

Product    Price
1          -12.00
2          15.00

Thank you!

Upvotes: 1

Views: 99

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93704

since you are using Sql Server 2014 you can use LAG or LEAD window function to do this.

Generate Row number to find the last two weeks for each product.

;WITH cte 
     AS (SELECT *, 
                Row_number()OVER(partition BY product ORDER BY weeks DESC)rn 
         FROM   Yourtable) 
SELECT product, 
       price 
FROM   (SELECT product, 
               Price=price - Lead(price)OVER(partition BY product ORDER BY rn) 
        FROM   cte a 
        WHERE  a.rn <= 2) A 
WHERE  price IS NOT NULL

Traditional solution, can be used before Sql server 2012

;WITH cte
     AS (SELECT *,
                Row_number()OVER(partition BY product
                    ORDER BY weeks DESC)rn
         FROM   Yourtable)
SELECT a.Product,
       b.Price - a.Price
FROM   cte a
       LEFT JOIN cte b
              ON a.Product = b.Product
                 AND a.rn = b.rn + 1
WHERE  a.rn <= 2
       AND b.Product IS NOT NULL

Upvotes: 2

Related Questions