CODEWITHSUNDEEP
sql-servert-sql
Ammar
Ammar

Reputation: 1068

Can SQL Server return the wrong order for a temp table?

I have an odd issue on production that I am unable to reproduce in my local development. Note that we have two applications running based on same DB but they return different data (with old application being the correct one)

I have a line like the following

UPDATE #ResultTempTable 
SET @@beginningBal = CLoanBal = @@beginningBal + NetChange

The goal of that is to go over the rows by date and calculate the Loan Balance over time.

The issue that I am running into is that it is starting the calculation from the opposite side. My temp table is ordered by DESC but the calculation is started based on an ASC order.

Anything obvious that might stand out? I am limited when it comes to testing Production.

Upvotes: 0

Views: 124

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

This is the wrong way to do what you want. The correct way is to use window functions:

WITH toupdate as (
      SELECT rtt.*,
             SUM(NetChange) OVER (ORDER BY ?) as RunningNetChange
      FROM #ResultTempTable rtt
     )
UPDATE toupdate
    SET CLoanBal = @beginningBal + RunningNetChange;

The ? is for the column that specifies the ordering of the table. SQL tables represent unordered sets, so a column is needed to specify the ordering.

Upvotes: 4

Related Questions