SQL Server 2005: Finding/accessing value of (currentrow-N) & implementing a formula ?

Question

I have a table with four columns (Year,Month,Name & Value)

I want to add another column (new_value) which is calculated by the following formula

NEW_VALUE = (VALUE of currentMonth/VALUE of (currentMonth-4))^1/3

Example

Year    Month NAME VALUE NEW_VALUE 
2008      01  A     4.412     ?
2008      02   B     4.941
2008      03   C     4.815
2008      04   D     4.246
2008      05   E     4.100
2008      06   F     4.490
2008      07  G     4.465
2008      08  H     4.636
2008      09  I     4.045
2008      10  J     5.543
2008      11  K     5.722
2008      12  L     5.326

e.g:For month 08

NEW VALUE = (4.636/4.246)^1/3

Ignore calculation for first four months

How do I get the 'currentMonth-4'th value programmatically in SQL ?

Thanks

Answer 1

Just join the table to itself on the month (and possibly other columns, I don't know your data)

select TB.Name, TB.Month, (TB.Value / TA.Value) ^ 1/3 -- This calculation won't work, but your parameters are there
from YourTable TA
join YourTable TB
on TA.Month = TB.Month - 4

Answer 2

Similar to @Tetraneutron:

SELECT tb.Name, tb.Value, POWER(tb.value / ta.value, .3333) AS NewValue
FROM
    yourtable ta,
    yourtable tb
WHERE
    (tb.month>4 AND ta.month=tb.month-4 AND tb.year=ta.year)
    OR (tb.month<=4 AND ta.month=(8+tb.month) AND tb.year=(ta.year+1))