Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calculation Expand / Collapse
Author
Message
Posted Tuesday, January 15, 2013 9:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 12, 2014 1:53 PM
Points: 69, Visits: 187
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.
Post #1407345
Posted Tuesday, January 15, 2013 10:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 13,103, Visits: 11,933
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 Moden's 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)
Post #1407373
Posted Friday, April 5, 2013 7:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 13,103, Visits: 11,933
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 Moden's 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)
Post #1439226
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse