recursive common table expression in SQL Server

Question

I have a table that contains two pieces of information BusinessDate and DailyPerf where DailyPerf shows percentage change in the value of a price I am tracking. The first few rows of the data looks like this

BusinessDate   DailyPerf 
Jan 3, 2017     -0.0356%
Jan 4, 2017     -0.4325%
Jan 5, 2017     -0.3953%
Jan 6, 2017     -0.8469%
Jan 9, 2017     -0.5050%

What I am trying to do is to calculate another column YearFac that shows the current percentage of my initial value I have left

Done in Excel the data would look like this:

BusinessDate DailyPerf  YearFac
Jan 2, 2017  NULL       100%
Jan 3, 2017  -0.0356%   99.9644%
Jan 4, 2017  -0.4325%   99.5321%
Jan 5, 2017  -0.3953%   99.1386%
Jan 6, 2017  -0.8469%   98.2989%
Jan 9, 2017  -0.5050%   97.8025%

So, I need to prime my query with a YearFac of 100% and then recursively calculate the factor. The one caveat is that the dates are not necessarily sequential (there are no entries over weekends or on holidays) - so I cannot assume that next day = this day + 1 just that the next day is the next larger date

I tried the following

WITH   cte
AS     (
        SELECT cast(1 as int) RowCnt,
               cast('3 jan 2017' as date) BusinessDate, 
               cast(1.0 as float) YearFac -- anchor member
        UNION ALL
        select  cast(ROW_NUMBER() OVER(ORDER BY p.BusinessDate ASC) + 1 as int) as RowCnt,
                p.BusinessDate,                                                       -- recursive member
                cast(cte.YearFac*(1.0 + p.DailyPerc) as float) YearFac
        from cte
        inner join dbo.MsfsDailyPnl p 
        on 
        cte.RowCnt = ROW_NUMBER() OVER(ORDER BY p.BusinessDate ASC) + 1
        where p.BusinessDate < sysutcdatetime()
       )
SELECT RowCnt,BusinessDate, YearFac
FROM   cte

But, of course, this fails because I cannot reference the row number in the join. Could anyone suggest a modification that will get this query to work?

Gordon Linoff · Accepted Answer · 2017-12-11 12:28:18Z

1

Use a cumulative sum:

select BusinessDate, DailyPerf,
       1 - exp(sum(log(1 + DailyPerf)) over (order by BusinessDate desc)
from (select cast('2017-01-03' as date) as BusinessDate, cast(0 as float) as DailyPerf
      union all
      select BusinessDate, DailyPerf from dbo.MsfsDailyPnl
     ) p;

edited Dec 11, 2017 at 12:28

answered Dec 11, 2017 at 12:04

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michał Turczyn Over a year ago

I guess there are some easier function to accomplish the task :) Such as window functions :)

Radim Bača · Accepted Answer · 2017-12-11 12:14:23Z

1

Use window function

select *, sum(DailyPerf) over (order by BusinessDate) + 100 YearFac 
from (
     select dateadd(day, -1, min(BusinessDate)) BusinessDate, 0  DailyPerf from data
     union all 
     select * from data) t

dbfiddle demo

RESULT

BusinessDate        DailyPerf   YearFac
-------------------------------------------
02/01/2017 00:00:00 0.0000      100.0000
03/01/2017 00:00:00 -0.0356     99.9644
04/01/2017 00:00:00 -0.4325     99.5319
05/01/2017 00:00:00 -0.3953     99.1366
06/01/2017 00:00:00 -0.8469     98.2897
09/01/2017 00:00:00 -0.5050     97.7847

edited Dec 11, 2017 at 12:14

answered Dec 11, 2017 at 12:08

Radim Bača

10.7k1 gold badge21 silver badges35 bronze badges

1 Comment

Richard B Over a year ago

Summing the daily values isn't the same as compounding but your answer was the most succinct if I sum log(1+return)

Michał Turczyn · Accepted Answer · 2017-12-11 12:11:56Z

0

Here's what you might want to try. I attached also definition of table, so you know how it looks like and what can be done easily to accomplish your task.

declare @x table(BusinessDate date, DailyPerf float)
insert into @x values
('Jan 2, 2017',     NULL),
('Jan 3, 2017',     -0.0356),
('Jan 4, 2017',     -0.4325),
('Jan 5, 2017',     -0.3953),
('Jan 6, 2017',     -0.8469),
('Jan 9, 2017',     -0.5050)

select *,
    100 + SUM(isnull(DailyPerf,0)) over (partition by (select null) order by 
        BusinessDate rows between unbounded preceding and current row)
from @x

answered Dec 11, 2017 at 12:11

Michał Turczyn

41.2k18 gold badges58 silver badges87 bronze badges

Collectives™ on Stack Overflow

recursive common table expression in SQL Server

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related