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

Question

Hallo,

I want to calculate the effectiveness of a discount.

I want to write 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

for 'positive' step. Where do I make the mistake? Can you help me? Thank you in advance..

Answer 1

;With cte As
(Select a.product_id, a.discount, a.s_quantity, a.hesap_kitap, Lag(a.s_quantity) Over(Partition By a.product_id Order by a.discount) As LagQuantity
From SampleRetail.sale.Analiz a)
Update c
Set c.hesap_kitap = Case When c.s_quantity=c.LagQuantity Or c.LagQuantity Is Null Then 'Neutral'
   When c.s_quantity < c.LagQuantity Then 'Negative'
   Else 'Pozitiv' End
From cte c;

Tom

Answer 2

Hi，@messer gatti

Welcome to Microsoft T-SQL Q&A Forum!

Please check this：

update [SampleRetail].[sale].[Analiz]  
set hesap_kitap =  
case when  s_quantity > (LAG(s_quantity) OVER (ORDER BY product_id ASC, discount ASC))   
Then 'pozitif'  
when s_quantity < (LAG(s_quantity) OVER (ORDER BY product_id ASC, discount ASC))  
Then 'Negative'  
else 'Neutral'  
end  

Note: Since you did not provide the test data, I can only write the test code according to your description. If the test result is wrong, please put the test code and table building statement in the comments, and we will continue to test for you.
Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.