How to use Lag with while to calculate the effectiveness of discount , SQL

Question

I want to calculate the effectiveness of a discount.

I want to update the last column as 'pozitive' if s_quantity increased and negative, if decreased. Neutral, if no change. I've written the code:

DECLARE @count AS INT

SET @count=1

WHILE @count< 316

BEGIN

IF product_id = @count

WHEN s_quantity > (LAG(s_quantity) OVER (ORDER BY product_id ASC, discount ASC))

UPDATE [SampleRetail].[sale].[Analiz] SET hesap_kitap = 'pozitif'

SET @count +=1

IF @count > 316
BREAK
ELSE
CONTINUE
END

Where do I make the mistake? Can you help me?

Answer 1

We can do this with the function LAG() in a SELECT.
(see the dbFiddle link below for the test schema.)

SELECT
  period,
  sales, 
  LAG(sales) OVER (ORDER BY period) p_1,
  sales - LAG(sales) OVER (ORDER BY period) increase
FROM sales
ORDER BY period;

period | sales |  p_1 | increase
-----: | ----: | ---: | -------:
     1 |   100 | null |     null
     2 |   200 |  100 |      100
     3 |   300 |  200 |      100
     4 |   250 |  300 |      -50
     5 |   500 |  250 |      250
     6 |   500 |  500 |        0

WITH sale as (
  SELECT
    period,
    sales, 
    LAG(sales) OVER (ORDER BY period) p_1
  FROM sales)
  
SELECT 
  period,
  sales,
  sales - p_1 increase,
  CASE WHEN sales < p_1 THEN 'negative'
       WHEN sales = p_1 THEN 'stable'
       ELSE 'positive' END AS "change"
FROM sale;

period | sales | increase | change  
-----: | ----: | -------: | :-------
     1 |   100 |     null | positive
     2 |   200 |      100 | positive
     3 |   300 |      100 | positive
     4 |   250 |      -50 | negative
     5 |   500 |      250 | positive
     6 |   500 |        0 | stable  

db<>fiddle here