devpal - 5 months ago 3x
SQL Question

# sql (beginner) - use value calculated from above cell

EDIT

the values in the table can be negative numbers (sorry for the oversight when asking the question)

Having exhausted all search efforts, I am very stuck with the following:

I would like to calculate a running total based on the initial value. For instance:

My table would look like:

``````Year  Percent   Constant
====  =====     ========

2000  1.40      100
2001  -1.08     100
2002  1.30      100
``````

And the desired results would be:

``````Year  Percent   Constant  RunningTotal
====  ======    ========  ============

2000  1.40      100       140
2001  -1.08     100       128.8
2002  1.30      100       167.44
``````

Taking the calculated value of 1.40*100 and multiplying it with percent of the next line, 1.08 and so on.

I am using Sql Server 2012. I've looked into using a common table expression, but can't seem to get the correct syntax sadly.

You can accomplish this task using a recursive CTE

``````;WITH values_cte AS (
SELECT [Year]
,[Percent]
,[Constant]
,CASE WHEN [v].[Percent] < 0 THEN
[v].[Constant] - (([v].[Percent] + 1) * [v].[Constant])
ELSE
[v].[Percent] * [v].[Constant]
END
AS [RunningTotal]
FROM  [#tmp_Values] v
WHERE [v].[Year] = 2000
UNION ALL
SELECT v2.[Year]
,v2.[Percent]
,v2.[Constant]
,CASE WHEN [v2].[Percent] < 0 THEN
[v].[RunningTotal] + (([v2].[Percent] + 1) * [v].[RunningTotal])
ELSE
[v2].[Percent] * [v].[RunningTotal]
END
AS [RunningTotal]
FROM  values_cte v
INNER JOIN [#tmp_Values] v2 ON v2.[Year] = v.[Year] + 1
)
SELECT *
FROM  [values_cte]
``````