November 23, 2010 at 7:08 pm
I have found a couple of examples of this but they do not work quite right . Basically what I want to do is this:
col1 col2 col3
1 10 0
2 25 15
3 45 20
4 66 21
Column 3 is the calculated column that is the diff between a row in column 2 and it's preceding row.
Any ideas?
November 23, 2010 at 8:45 pm
Sure... updatable CTE joined on itself with the row_number function to assure sequential numbering.
-- See how you start off by actually creating a table and then
-- inserting the data into it? Your doing this makes it a lot easier
-- for all of us volunteers to help you. So, help us help you.
-- See http://www.sqlservercentral.com/articles/Best+Practices/61537/
-- for more details on how to do all of this.
if object_id('tempdb..#test') IS NOT NULL DROP TABLE #test;
CREATE TABLE #test (Col1 INT PRIMARY KEY CLUSTERED, Col2 INT, Col3 INT);
INSERT INTO #test (Col1, Col2)
SELECT 1, 10 UNION ALL
SELECT 2, 25 UNION ALL
SELECT 3, 45 UNION ALL
SELECT 4, 66;
SELECT *
FROM #test;
WITH CTE AS
(
-- ensure that items are sequentially numbered
SELECT Col1,
Col2,
Col3,
RN = ROW_NUMBER() OVER (ORDER BY Col1)
FROM #test
)
UPDATE t1
SET Col3 = IsNull(t1.Col2 - t2.Col2,0)
FROM CTE t1
LEFT JOIN CTE t2
ON t1.RN = t2.RN+1;
SELECT *
FROM #test;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply