SQL List previous sales of dates in the current month-CodePudding

On the sql side, as seen in the table below, I want to subtract the sale of the day before today's date and have it printed in a separate column.

example; Subtract the sale on 2022-04-17 from the sale on 2022-04-18 and write it in the side column.

subtract the sales from the previous day until you find the last date in the table in a loop.

CodePudding user response：

You can try to use LEAD window function with CASE WHEN expression

SELECT *,
    CASE WHEN LEAD(n_Sales) OVER(PARTITION BY CustomerId,ProductId ORDER BY Date DESC) IS NULL 
        THEN Sales ELSE 
        Sales - LEAD(n_Sales) OVER(PARTITION BY CustomerId,ProductId ORDER BY Date DESC) 
    END
FROM T