Sql Student-446896 (1/15/2013)
TableX-------
Name Chg Amount PrevAmt
ABC 1 100 0
ABC 2 300 100
I want to dynamically calculate ChangeAmt from the above table for the Name "ABC".
Example: Between Chg 1 and Chg2, the Amount increased by 200.
Do you want to do this "on the fly"? What is "previous"? Keep in mind that a table has no concept of order, it must be defined in your query.
In order to provide any real assistance we need to have some ddl and sample data. The answer will depend GREATLY on your ddl.
If it is as you posted simply add a column:
Amount - PrevAmt as ChangeAmt
If as I suspect you don't have PrevAmt in your table (and I hope for your sanity you don't) you will need to do something different. most likely a recursive cte would work but without more details I am not going to put together an example.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/