CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
4Logik
4Logik

Reputation: 177

Trying avoid using cursor

I have been given a query and trying to figure out a way to remove the cursor yet maintaining functionality, because the starting table can get into the millions of rows.

Example of data in table:

ID   DollarValue  Month     RowNumber
1     $10         1/1/2014  1
1     $15         2/1/2014  2
1    -$40         3/1/2014  3
1     $50         4/1/2014  4
2    -$11         1/1/2014  1
2     $11         2/1/2014  2
2     $5          3/1/2014  3

Expected results:

ID   DollarValue  Month     RowNumber  TestVal
1     $10         1/1/2014  1           1 
1     $15         2/1/2014  2           0
1    -$40         3/1/2014  3          -1
1     $50         4/1/2014  4           1
2    -$11         1/1/2014  1          -1
2     $11         2/1/2014  2           0
2     $5          3/1/2014  3           1

Here is the logic (pseudocode)that happens inside the cursor:

If a @ID <> @LastId AND @Month <> @LastMonth
  Set @RunningTotal = @DollarValue
  Set @LastMonth = '12/31/2099'
  Set @LastID = @ID
  Set @TestVal = Sign(@DollarValue)
Else
  If Sign(@RunningTotal) = Sign(@RunningTotal + @DollarValue)
    Set @TestVal = 0
Else
   Set @TestVal = Sign(@DollarValue)

Set @RunningTotal = @RunningTotal + @DollarValue

Any idea how I can change this to set based?

Upvotes: 0

Views: 87

Answers (2)

milan minarovic
milan minarovic

Reputation: 123

This is 2008 solution

WITH CTE AS (
SELECT 
  AA.[ID]
 ,AA.[Month]
 ,AA.[RowNumber]
 ,AA.[DollarValue]
 ,SIGN(SUM(BB.[DollarValue])) AS RunTotalSign

FROM YourTable AS AA
LEFT JOIN YourTable AS BB
       ON (AA.[ID] = BB.[ID] AND BB.[RowNumber] <= AA.[RowNumber])
GROUP BY  AA.[ID],AA.[Month],AA.[DollarValue],AA.[RowNumber])
)

SELECT 
  AA.[ID]
 ,AA.[Month]
 ,AA.[RowNumber]
 ,AA.[DollarValue]
 ,CASE WHEN AA.RunTotalSign = CC.RunTotalSign Then 0
       ELSE AA.RunTotalSign
       END
  AS TestVal
FROM CTE AS AA
LEFT JOIN CTE AS CC
       ON (AA.[ID] = CC.[ID] AND AA.[RowNumber] = CC.[RowNumber]+1)

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

You can use the windowed version of SUM to calculate running totals:

;WITH CTE AS (
   SELECT ID, DollarValue, Month, RowNumber,
          SUM ( DollarValue ) OVER (PARTITION BY ID ORDER BY RowNumber) as RunningTotal
   FROM #mytable
)
SELECT C1.ID, C1.DollarValue, C1.Month, C1.RowNumber,
       CASE WHEN C1.RowNumber = 1 THEN SIGN(C1.DollarValue)
            WHEN SIGN(C1.RunningTotal) = SIGN(C2.RunningTotal) THEN 0
            ELSE SIGN(C1.RunningTotal) 
       END AS TestVal        
FROM CTE AS C1
LEFT JOIN CTE AS C2 ON C1.ID = C2.ID AND C1.RowNumber = C2.RowNumber + 1

Using LEFT JOIN on RowNumber you can get the previous record and compare the current running total with the previous one. Then use a simple CASE to apply rules pertinent to changes in SIGN of running total.

SQL FIDDLE Demo

P.S. It seems the above solution wont work in versions prior to SQL Server 2012. In this case the running total calculation inside the CTE has to be replaced by the "conventional" version.

Upvotes: 2

Related Questions