How could I re-write this cursor to calculate running total?

Question

So I have some code which drops and re-creates a table each time, then uses a cursor to go through and calculate a running total of stock amounts. The code for the cursor is as below (table names obscured):

DECLARE @Cust_Name nvarchar(250),
              @Cust_Postcode nvarchar(50),
              @MatchCode_Product nvarchar(50),
              --@Stock int,
              @DateKey nvarchar(8),
              @Ordered int

DECLARE StockCursor CURSOR 
  LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR 
SELECT
       Cust_Name, Cust_Postcode, MatchCode_Product, DateKey
FROM
       WRK_TFT_DEPOT_STOCK
WHERE
    Cust_Name NOT LIKE {redacted}

OPEN StockCursor
FETCH NEXT FROM StockCursor INTO @Cust_Name, @Cust_Postcode, @MatchCode_Product, @DateKey

WHILE @@FETCH_STATUS = 0
BEGIN
       UPDATE
              WRK_TFT_DEPOT_STOCK
       SET

                     Ordered = isnull((
                                  SELECT
                                         sum(cast(MO.Quantity as int))
                                  FROM
                                         {redacted }DE
                                         INNER JOIN {redacted} MO
                                         ON DE.Order_No = MO.Order_No
                                  WHERE
                                         DE.Cust_Name = @Cust_Name
                                         AND
                                         DE.Cust_Postcode = @Cust_Postcode
                                         AND
                                         MatchCode_Product = @MatchCode_Product
                                         AND
                                         CAST(Year(DE.Delivery_Date) AS VARCHAR) + RIGHT('0' + CAST(Month(DE.Delivery_Date) AS VARCHAR), 2) + 
                                            RIGHT('0' + CAST(Day(DE.Delivery_Date) AS VARCHAR), 2)  <= @DateKey
                                         AND DE.Cust_Name NOT LIKE {redacted}
                                     ),0)
       WHERE
              Cust_Name = @Cust_Name
              AND
              Cust_Postcode = @Cust_Postcode
              AND
              MatchCode_Product = @MatchCode_Product
              AND
              DateKey = @DateKey 

       FETCH NEXT FROM StockCursor INTO @Cust_Name, @Cust_Postcode, @MatchCode_Product, @DateKey
END

CLOSE StockCursor
DEALLOCATE StockCursor

UPDATE
       WRK_TFT_DEPOT_STOCK
SET
       Stock = StartStock + Ordered

The code works fine but my issue with it is that it takes ages to run, and the time it takes to run is increasing over time as I'm loading daily data into the source tables.

Would anyone be able to help me out with re-writing this more efficiently? From looking around it looks like windowed functions would help a lot but I'm not familiar with them.

The column 'Stock' is the running total, 'StartStock' is the initial amount and 'Ordered' is what we need to add when it comes in.

Recursive CTE is best alternative for cursors, https://stackoverflow.com/questions/6824669/why-is-cte-better-than-cursor-derived-table-subqueries-temp-table-etc — Ven, Nov 22 '17 at 14:12
@BHouse would it even need to be recursive? In this case looks like `update..from` would work. — Rigerta, Nov 22 '17 at 14:13
@RigertaDemiri I agree a non-recursion will do. but it has while loop , it would d enhance query process quicker by setting max recursion — Ven, Nov 22 '17 at 14:15
I'm not really sure how to go about that, I wrote the cursor originally because I wasn't sure how else to keep a running total but I didn't expect it to run so slowly — GraveyardWorker21005, Nov 22 '17 at 14:20
use tsql functions instead - I tend to use ROWS UNBOUNDED PRECEDING. Go to this link for more information https://stackoverflow.com/questions/11310877/calculate-running-total-running-balance — SqlKindaGuy, Nov 22 '17 at 14:28

score 1 · Accepted Answer · answered Nov 22 '17 at 14:33

1

An example with rows unbounded preceding:

SELECT TOP 1000 [Dato]
      ,[Department]
      ,[Amount]
      ,runningtotal = SUM(amount) over(order by dato ROWS UNBOUNDED PRECEDING)
  FROM [LegOgSpass].[dbo].[amounts]

Result

answered Nov 22 '17 at 14:33

SqlKindaGuy

3,501
2
12
29

And if you want running total within the year, you just add an partition by year(dato). But its hard to tell what you exactly want when we dont know how your data looks like. – SqlKindaGuy Nov 22 '17 at 14:36
Wow that looks so much simpler than what I was doing. Thanks a lot I appreciate that, I'll give it a go using that function. – GraveyardWorker21005 Nov 22 '17 at 14:37

How could I re-write this cursor to calculate running total?

1 Answers1