1

I have the following case (excel format):

Date Start: 2016-01-01
Date Finish: 2016-12-31
Max Value: 96
Point Value: 0.0972

So If I change those parameter above, the record will change. For instance result below:

Date           Value
-----------------------------
2016-01-01     96     --> This value is taken from MaxValue Parameter
2016-01-02     95.9   --> It requires a formula, which is (1 - PointValue) * Previous Value
2016-01-03     95.8
ff.
2016-12-31     67.3   --> Last Record, Based on Date Finish. The formula is the same

Does anyone have an idea how to do this in SQL Script?
Thank you.

Haminteu
  • 1,292
  • 4
  • 23
  • 49
  • Possible duplicate of [Calculate running total / running balance](http://stackoverflow.com/questions/11310877/calculate-running-total-running-balance) – Liesel Apr 05 '16 at 04:56

1 Answers1

2

You can solve this using a CTE.

DECLARE @StartDate DATE = '2016-01-01'
        ,@EndDate  DATE = '2016-12-31'
        ,@MaxValue  float  = 96
        ,@PointValue float =  0.0972
;WITH ctetest AS (
    SELECT @StartDate AS CDate,@MaxValue AS Value
    UNION ALL
    SELECT dateadd(day,1,CDate) ,(1-@PointValue) * Value
    FROM ctetest 
    WHERE dateadd(day,1,CDate)<=@EndDate
)
SELECT * FROM ctetest OPTION (MAXRECURSION 0)

Note:- Please check the foluma, and change it if required.

Abdul Rasheed
  • 6,486
  • 4
  • 32
  • 48
  • Msg 530, Level 16, State 1, Line 5 The statement terminated. The maximum recursion 100 has been exhausted before statement completion. – Haminteu Apr 05 '16 at 04:51
  • You'll have to add `OPTION (MAXRECURSION 400)` to the cte as it will run for more than a default recursion of 100. – Amit Sukralia Apr 05 '16 at 04:52
  • Modified the query, MAXRECURSION added. Also you can modify the data type of MaxValue and PointValue as per your requirement. – Abdul Rasheed Apr 05 '16 at 04:54