devpal - 9 months ago 21

SQL Question

**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.

Answer

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]
```