# 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](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
``````

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.