devpal devpal - 7 months ago 5
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.

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]