CODEWITHSUNDEEP
sqlsql-server
KS Java
KS Java

Reputation: 31

Sales difference between date and date-1 at product level

I am struggling to find the difference in sales between the date and date-1 where I have thousands of products

enter image description here

The O/p I am willing to have is the difference between day 2 to day 1 but at the product level,

Thank you in advance for the help,

Upvotes: 0

Views: 149

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270483

The logic using lag() looks like:

select t.*,
       (sales -
        (case when lag(date) over (partition by product_name order by date) = dateadd(day, -1, date)
              then lag(sales) over (partition by product_name order by date)
         end)
       ) as diff
from t;

Note the logic to handle missing dates for a product. This also assumes there are no duplicate dates for a product, which is consistent with how the question is phrased.

Upvotes: 1

A Redfearn
A Redfearn

Reputation: 528

As mentioned in the comments you could use LAG but if you are using an earlier version than 2012 you can self join to the table using the date - 1 day and the product name. 

CREATE TABLE #TEMP(ProductName varchar(1), [Date] Date, Sales int)

INSERT INTO #TEMP VALUES
('a', '07/01/2019', 5), 
('a', '07/02/2019', 10),
('a', '07/03/2019', 15), 
('a', '07/04/2019', 20),
('b','07/01/2019', 5),
('b','07/02/2019', 10),
('b','07/03/2019', 15),
('b','07/04/2019', 20)

--drop table #TEMP

SELECT 
    a.ProductName, 
    a.[Date], 
    a.Sales,    
    b.[Date],
    b.Sales,
    a.Sales - b.Sales as difference
FROM #TEMP a
LEFT JOIN #TEMP b ON b.[Date] = DATEADD(day, -1, a.[Date]) 
    AND b.ProductName = a.ProductName

Output

ProductName Date       Sales       Date       Sales       difference
----------- ---------- ----------- ---------- ----------- -----------
a           2019-07-01 5           NULL       NULL        NULL
a           2019-07-02 10          2019-07-01 5           5
a           2019-07-03 15          2019-07-02 10          5
a           2019-07-04 20          2019-07-03 15          5
b           2019-07-01 5           NULL       NULL        NULL
b           2019-07-02 10          2019-07-01 5           5
b           2019-07-03 15          2019-07-02 10          5
b           2019-07-04 20          2019-07-03 15          5

Upvotes: 1

Related Questions