SQL Server 2008: Need to do math on the previous row

Question

Working in SQL Server 2008 so the analytical functions are not an option.

Basically I have amount financed and payment made, but need to calculate interest for the first row - which is done, but need for the next row so need to grab the balance from the previous row.

The problem is that SQL should not have any built in notion of "previous row". SQL is set-based. Order is something that you have to build in. Don't be deceived by the fact that a query brings back data in a table-like structure. — duffymo, May 12 '16 at 20:13
Your friends are called `CTE` and `ROW_NUMBER`. But without your actual table's structure and some test data you won't get much help... — Shnugo, May 12 '16 at 20:16
Look at http://stackoverflow.com/questions/11310877/calculate-running-total-running-balance Probably it's what you're looking for. — Serg, May 12 '16 at 20:18
Here is another reference that I just used earlier today to do this https://sqlscope.wordpress.com/2014/05/26/lag-and-lead-for-sql-server-2008/ — msheikh25, May 12 '16 at 20:22

Michael L. · Answer 1 · 2016-05-17T13:30:21.347

Without any schema context, I can only provide a general structure, but in SQL Server 2008 you should be able to do something like this:

-- This is called a CTE (Common Table Expression)
-- Think of it as a named sub-query
;WITH computed_table AS (
    -- The ROW_NUMBER() function produces an ordered computed
    -- column ordered by the values in the column specified in
    -- the OVER clause
    SELECT ROW_NUMBER() OVER(ORDER BY Id) AS row_num
          ,*
    FROM my_table
)
SELECT *
      -- perform calculations on t1 and t2
      ,(t1.amount - t2.amount) AS CalculatedAmt -- example calcuation
FROM computed_table t1
OUTER APPLY (
    SELECT *
    FROM computed_table t2
    WHERE t2.row_num = t1.row_num - 1
) AS prev

The CTE and the ROW_NUMBER() function are necessary to make sure you have a perfectly ordered column with no gaps, something which can't be guaranteed with a primary key field since rows could be deleted. The OUTER APPLY allows you to perform a table-valued operation on the individual values of the rows in the left hand table.

EDIT: To insert the results into a table, rather than just selecting them, you can add a INSERT clause after the SELECT clause:

...(CTE HERE)...
SELECT *
      -- perform calculations on t1 and t2
      ,(t1.amount - t2.amount) AS CalculatedAmt -- example calcuation
-- This INSERT clause will insert the result set into my_table. Make 
-- sure the column aliases in the SELECT clause match the column names 
-- in my_table.
INTO my_table 
FROM computed_table t1
...(REST OF QUERY HERE)...

I like your solution and I must admit, that I changed my (much to complicated) approach to the `row_num - 1` you are using... Voted your's up for this... — Shnugo, May 12 '16 at 20:38
It's looking good so far. Have to leave work, but will finish up adding more of the real columns tomorrow. Thanks Will vote you up regardless, as this is the direction I needed. — Rogue, May 12 '16 at 21:02
How would I use the insert statement on the query? I am getting the data I want, but need to insert into a table. — Rogue, May 16 '16 at 22:14
I updated my answer to show how to insert the results instead of selecting them. — Michael L., May 17 '16 at 13:30

Shnugo · Answer 2 · 2016-05-12T20:36:44.820

0

Try this example

DECLARE @tbl TABLE(ID INT, Test VARCHAR(100),SortKey INT);
INSERT INTO @tbl VALUES(1,'Test 1 3',3),(2,'Test 2 4',4),(3,'Test 3 1',1),(4,'Test 4 2',2);

WITH Sorted AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY SortKey) AS Nr
          ,*
    FROM @tbl      
)
SELECT s.Test
      ,(SELECT prev.Test FROM Sorted AS prev WHERE s.Nr=prev.Nr+1) AS PreviousRow
      ,(SELECT nxt.Test  FROM Sorted AS nxt  WHERE s.Nr=nxt.Nr-1)  AS NextRow
FROM sorted AS s

Attention

ROW_NUMBER() OVER() will only work as expected, if the values you are sorting after are unique!

The result

Test     PreviousRow    NextRow
Test 3 1    NULL        Test 4 2
Test 4 2    Test 3 1    Test 1 3
Test 1 3    Test 4 2    Test 2 4
Test 2 4    Test 1 3    NULL

edited May 12 '16 at 20:36

answered May 12 '16 at 20:22

Shnugo

66,100
9
53
114

If the column you're sorting on doesn't contain unique values, you can add additional sort columns (e.g. `ROW_NUMBER() OVER(ORDER BY col1, col2, col3,...)`) until you achieve a set of columns which will always have unique values. – Michael L. May 12 '16 at 20:50
@MichaelL. I know this, that's why I wrote "value **s** ". But there are tables, where you do not have uniqueness even with all columns together. In this case one could set a CTE before this CTE and calculate a `ROW_NUMBER` first and use this as unique sort crit afterwards. This would be random in the first go, but should suffice for this purpose... – Shnugo May 12 '16 at 20:59

SQL Server 2008: Need to do math on the previous row

2 Answers2

Attention