Adding a Value of a previous row to the current row

[ehenry]

I have a table that holds some pricing data, there is a column that contains a settlement price. This table is updated continuously throughout the day, so I was wondering if there was a way to make a view that could select all the columns from this table, but for the Settlement column I want it to add the value of the previous row to it. If anyone can help I would appreciate it. Thank you.

 

[bborissov]

What defines "Previous" record?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[ehenry]

The table is ordered by a contract column, so if I have Mar-2010 in contract And 5.75 in settle for one row, then APR-2010 and .25 in the next row, the APR-2010 settle row should have 5.75+.25. basically the value for settle column should be the value in the table + the value in the previous row. Let me know if you need any more clairifcation, I can attach an excel file if necessary. Thanks.

 

[bborissov]

Is there ANY column better that this DATA that show you that this record is Previous?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[ehenry]

Right now I am creating a rowNumber column. here is what I have so far:


WITH CTE_MD AS(
SELECT ROW_NUMBER() OVER(Partition By Product ORDER BY [Product], CASE
WHEN RTRIM(LTRIM([Contract])) = 'Mar10' Then 1
WHEN RTRIM(LTRIM([Contract])) = 'Feb10-Mar10' Then 2
WHEN RTRIM(LTRIM([Contract])) = 'Mar10-Apr10' Then 3
WHEN RTRIM(LTRIM([Contract])) = 'Apr10-May10' Then 4
WHEN RTRIM(LTRIM([Contract])) = 'May10-Jun10' Then 5
WHEN RTRIM(LTRIM([Contract])) = 'Jun10-Jul10' Then 6
WHEN RTRIM(LTRIM([Contract])) = 'Jul10-Aug10' Then 7
WHEN RTRIM(LTRIM([Contract])) = 'Aug10-Sep10' Then 8
WHEN RTRIM(LTRIM([Contract])) = 'Sep10-Oct10' Then 9
WHEN RTRIM(LTRIM([Contract])) = 'Oct10-Nov10' Then 10
WHEN RTRIM(LTRIM([Contract])) = 'Nov10-Dec10' Then 11
ELSE (SELECT COUNT([Contract]) FROM [RMS].[dbo].[MarketData]) + 1
END) As RowNumber, Product, [Contract], Settle
From MarketData)

SELECT currow.RowNumber, currow.Product, currow.[Contract],
CASE WHEN currow.RowNumber = 1 Then currow.Settle
ELSE (currow.Settle + prevrow.Settle) END As Settle
FROM CTE_MD currow
LEFT JOIN CTE_MD prevrow
ON currow.RowNumber = prevrow.RowNumber + 1

 

[ehenry]

Thanks for you help, I have found the solution.