5

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](26, 8) NULL,
currency_fk   [bigint] NOT NULL,
vehicleShareClassGroup_fk   [bigint] NOT NULL,
 importTransactionID    [bigint] NOT NULL
)

INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-09-29',113.49,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-09-30',113.75,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-01',113.56,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-02',113.65,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-03',113.61,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-06',113.53,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-07',113.55,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-08',113.58,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-09',113.61,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-10',113.49,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-13',113.74,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-14',113.78,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-15',113.17,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-16',113.26,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-17',113.12,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-20',113.26,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-21',113.37,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-22',113.34,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-23',113.34,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-24',113.4,12,22370,1   );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-27',113.4,12,22370,1   );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-28',113.43,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-29',113.65,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-30',113.95,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-31',113.84,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-03',113.91,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-04',113.86,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-05',113.92,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-06',113.99,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-07',114.01,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-10',114.11,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-11',114.1,12,22370,1   );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-12',114.09,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-13',114.16,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-14',114.13,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-17',114.08,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-18',113.9,12,22370,1   );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-19',114.07,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-20',114.37,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-21',114.43,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-24',114.61,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-25',114.65,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-26',114.94,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-27',115.02,12,22370,1  );

Current query

     WITH stage1
          AS (
          SELECT valueDate
               , NAVLocal
               , currency_fk
               , vehicleShareClassGroup_fk 
               , LAG(NAVLocal) OVER(PARTITION BY currency_fk
                                               , vehicleShareClassGroup_fk ORDER BY valueDate) AS previousInvestorLevelNAV

               , isnull(LOG(LAG(NAVLocal) OVER(PARTITION BY currency_fk
                                                          , vehicleShareClassGroup_fk ORDER BY valueDate) / NAVLocal), 0) AS            logCalc
               , SQRT(COUNT(NAVLocal) OVER(PARTITION BY currency_fk
                                                      , vehicleShareClassGroup_fk ORDER BY valueDate)) AS                               sqrtTimeSeries
          FROM   ShareClassData2
          WHERE  NAVLocal IS NOT NULL
                 AND NAVLocal != 0),
          stage2
          AS (
          SELECT   *
           , NAVLocal / MAX(previousInvestorLevelNAV) OVER(PARTITION BY currency_fk
                                                                         , vehicleShareClassGroup_fk ORDER BY valueDate) - 1 AS                               drawdown


          FROM      stage1  

),
          stage3
          AS (
          SELECT *
               , STDEV(logCalc) OVER(PARTITION BY currency_fk
                                                , vehicleShareClassGroup_fk ORDER BY valueDate) AS                                                 standardDeviationCalc
               , MIN(drawdown) OVER(PARTITION BY currency_fk
                                               , vehicleShareClassGroup_fk ORDER BY valueDate ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING) AS maximumDrawdown
               , MAX(drawdown) OVER(PARTITION BY currency_fk
                                               , vehicleShareClassGroup_fk ORDER BY valueDate ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING) AS minimumDrawdown
          FROM   stage2),
          stage4
          AS (
          SELECT a.*
               , a.standardDeviationCalc * a.sqrtTimeSeries AS volatility
          FROM   stage3 AS a)
          SELECT valueDate
               , currency_fk
               , vehicleShareClassGroup_fk 
               , data.volatility
               , data.drawdown
               , data.drawdown * 100 AS        drawdownPercentage
               , data.maximumDrawdown
               , data.minimumDrawdown
               , data.maximumDrawdown * 100 AS maximumDrawdownPercentage
               , data.minimumDrawdown * 100 AS minimumDrawdownPercentage
          FROM   stage4 AS data 
          where data.vehicleShareClassGroup_fk=22370 
0
3

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;

Execution plan

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.