SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calculation


Calculation

Author
Message
Sql Student-446896
Sql Student-446896
SSC Veteran
SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)

Group: General Forum Members
Points: 265 Visits: 201
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62065 Visits: 17954
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62065 Visits: 17954
chetan.aegis (4/5/2013)
A calculation is deliberate process that transforms one or more inputs into one or more results with variable change. The term calculation is used in a variety of senses, from the very definite arithmetical calculation of using an algorithm.


I am going to assume that since your response added nothing to the conversation that the reason for your post was to spam the link in your signature.

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search