sql server - SQL UPDATE statement does not update all rows in one execution

Question

I have a table in MS SQL Server where the EXPECTED result should look like this:

Prior to expected result/query execution, all FieldX values are NULL. When I run my query, FieldX is only updated from row 2 to 8.

I need to UPDATE FieldX using a set of rules, which I define as such:

WITH cte_previous_rows AS (
    SELECT Date, Staff_Id, LAG(FieldX) OVER (partition by Staff_Id ORDER by [date]) as Prev_Row
FROM Sales 
) UPDATE Sales
SET FieldX = (CASE 
    WHEN Staff_id_sales < 1500 AND ClosedSale = 0 THEN 0
    WHEN Staff_id_sales = 1500 and ClosedSale = 0 THEN 5
    WHEN Staff_id_sales <= 3000 and Staff_id_sales > 1500 and ClosedSale = 0 THEN 1
    WHEN Staff_id_sales > 3000 and (c.Prev_Row = 1 OR c.Prev_Row = 0) THEN 2
    WHEN Staff_id_sales > 3000 and (c.Prev_Row = 2 or c.Prev_Row = 3) THEN 3 
    ELSE FieldX
END) 
FROM Sales
JOIN cte_previous_rows as c ON Sales.staff_id = c.staff_id AND Sales.Date = c.Date;

This query works just fine. But the problem lies in the last two WHEN statements. The reason for this, is of course that c.Prev_Row (previous row) is used in the rule set for these two last WHEN statements..

How can I edit my query so that the above rule set is applied on to all 50k rows in a SINGLE execution? Perhaps a new method is required..

Answer 1

A recursive CTE that works from the earliest row for each Staff_Id forward may be the ticket:

Note: This query was not run on an image of the data, so it might have some errors.