Sql Update And Case Statement Does Not Work

I have the following table 'Sales' in MS SQL Server, where [FieldX] is not updated properly: (..[My comment] is of course not an actual column in the table) The reason why the las

Solution 1:

Please check the next query:

WITH cte_previous_rows AS (
    SELECTDate, Staff_Id, LAG(FieldX) OVER (partition by Staff_Id ORDERby [date]) as Prev_Row
FROM Sales 
) UPDATE Sales
SET FieldX = (CASEWHEN Staff_id_sales < 1500AND ClosedSale = 0THEN0WHEN Staff_id_sales = 1500and ClosedSale = 0THEN5WHEN Staff_id_sales > 3000and (c.Prev_Row = 1OR c.Prev_Row = 0) THEN2WHEN Staff_id_sales > 3000and (c.Prev_Row = 2or c.Prev_Row = 3) THEN3ELSE FieldX
END) 
FROM Sales
JOIN cte_previous_rows as c ON Sales.staff_id = c.staff_id AND Sales.Date = c.Date;

result:

+============+==========+================+============+========+|Date|Staff_Id|Staff_id_sales|ClosedSale|FieldX|+============+==========+================+============+========+|2000-01-01|1|500|0|0|+------------+----------+----------------+------------+--------+|2001-01-01|2|200|0|0|+------------+----------+----------------+------------+--------+|2001-02-26|3|500|0|0|+------------+----------+----------------+------------+--------+|2001-01-25|4|1500|0|5|+------------+----------+----------------+------------+--------+|2001-03-25|4|1500|0|5|+------------+----------+----------------+------------+--------+|2001-03-25|5|2500|0|1|+------------+----------+----------------+------------+--------+|2001-04-25|5|3000|0|1|+------------+----------+----------------+------------+--------+|2001-05-25|5|3500|0|2|+------------+----------+----------------+------------+--------+|2001-05-26|5|4000|0|3|+------------+----------+----------------+------------+--------+|2001-05-27|5|5000|0|3|+------------+----------+----------------+------------+--------+|2001-05-28|5|7500|0|3|+------------+----------+----------------+------------+--------+