Calculate drawdown and volatility on 3 years

I have a table with daily prices as below and in this SQLfiddle. Each record is associated to a ShareClassID, a currency, a date, and an amount. In my real scenario I can have at least 10 years of daily prices.

The goal is to calculate the drawdown and volatility over a 3 year period rather than the full period.

The query below is doing the calculation on all records. For a given date, I want to do the calculation only on the past 3 years. I am not sure how to do it.

Adding AND valueDate >= DATEADD(yy, -3, GETDATE()) to the WHERE clause doesn't give me what I need: I need to perform 3 years calculation from the current valueDate record. On each row, I want to perform the calculation from current row to -3 years.

Sample data

```
CREATE TABLE [dbo].ShareClassData2(
valueDate [date] NOT NULL,
NAVLocal numeric NULL,
currency_fk [bigint] NOT NULL,
vehicleShareClassGroup_fk [bigint] NOT NULL,
importTransactionID [bigint] NOT NULL
)

INSERT INTO [dbo].ShareClassData2 VALUES ( '2014-09-29',113.49,12,22370,1 );
INSERT INTO [dbo].ShareClassData2 VALUES ( '2014-09-30',113.75,12,22370,1 );
INSERT INTO [dbo].ShareClassData2 VALUES ( '2014-10-01',113.56,12,22370,1 );
INSERT INTO [dbo].ShareClassData2 VALUES ( '2014-10-02',113.65,12,22370,1 );
INSERT INTO [dbo].ShareClassData2 VALUES ( '2014-10-03',113.61,12,22370,1 );
INSERT INTO [dbo].ShareClassData2 VALUES ( '2014-10-06',113.53,12,22370,1 );
I

While waiting for support for window syntax like RANGE BETWEEN INTERVAL 3 YEARS PRECEDING AND CURRENT ROW, one approach would be to expand the three year window for each source row explicitly:

SELECT
    SCD.valueDate,
    SCD.currency_fk,
    SCD.vehicleShareClassGroup_fk,
    Q3.Volatility,
    Q3.Drawdown,
    DrawdownPercentage = Q3.Drawdown * 100,
    Q3.MaxDrawdown,
    Q3.MinDrawdown,
    MaxDrawdownPercentage = Q3.MaxDrawdown * 100,
    MinDrawdownPercentage = Q3.MinDrawdown * 100
FROM dbo.ShareClassData2 AS SCD
CROSS APPLY
(
    -- Aggregates
    SELECT
        Drawdown = (SCD.NAVLocal / MAX(Q2.PrevNAVLocal)) - 1,
        MaxDrawdown = MIN(Q2.Drawdown),
        MinDrawdown = MAX(Q2.Drawdown),
        Volatility = STDEV(Q2.LogCalc) * SQRT(COUNT_BIG(Q2.NAVLocal))
    FROM 
    (
        -- Calculations
        SELECT
            Q1.NAVLocal,
            Q1.PrevNAVLocal,
            Drawdown = Q1.NAVLocal / MAX(Q1.PrevNAVLocal) OVER (
                ORDER BY Q1.valueDate 
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) -1,
            LogCalc = ISNULL(LOG(Q1.PrevNAVLocal / Q1.NAVLocal), 0)
        FROM 
        (
            -- Data range for the current row
            SELECT
                SCD3.valueDate,
                SCD3.NAVLocal,
                PrevNAVLocal = LAG(SCD3.NAVLocal, 1) OVER (ORDER BY SCD3.valueDate)
            FROM dbo.ShareClassData2 AS SCD3
            WHERE
                SCD3.currency_fk = SCD.currency_fk
                AND SCD3.vehicleShareClassGroup_fk = SCD.vehicleShareClassGroup_fk
                AND SCD3.valueDate BETWEEN DATEADD(YEAR, -3, SCD.valueDate) AND SCD.valueDate
                AND SCD3.NAVLocal IS NOT NULL
                AND SCD3.NAVLocal <> 0
        ) AS Q1
    ) AS Q2
) AS Q3;

Performance may or may not be acceptable. The following index should help:

CREATE INDEX i 
ON dbo.ShareClassData2 
(
    currency_fk, 
    vehicleShareClassGroup_fk, 
    valueDate
) 
INCLUDE 
(
    NAVLocal
) 
WHERE NAVLocal <> 0;

It might also be possible to adapt some of the techniques (including a SQLCLR function) described in the related Q & A Date range rolling sum using window functions.

StackExchange Database Administrators Q#208274, answer score: 4