Update a calculated value based on 2 other columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update a calculated value based on 2 other columns

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2010-01-21 : 10:27:47

Hi,

I have a table similar to this

ref,years,miles,special,price
1,2,10000,0,100
1,2,10000,1,100
1,2,20000,0,0
1,2,20000,1,0
1,2,30000,0,200
1,2,30000,1,200

I want to loop through the table and for each ref calculate the 20,000 figures based on the 10,000 and 30,000 price of the same parameters (years,miles,special)

The calculation is the ((30000 price - 10000 price) / 20 * 10) + 10000 price

So in this example ((200 - 100) / 20 * 10) + 100 = 150

Really stuck how to go about it.

Thanks

Kristen
Test

22859 Posts

Posted - 2010-01-21 : 10:47:17

This perhaps?


SELECT ref,years,miles,special,price,
       ((T30000.price - T10000.price) / 20 * 10) + T10000.price AS CalcPrice
FROM MyTable AS T10000
     JOIN MyTable AS T30000
         ON T30000.years = T10000.years
        AND T30000.special = T10000.special
        AND T30000.miles = 30000
WHERE T10000.miles = 10000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources