SQL: переназначить число до 100
У меня есть следующий запрос:
WITH CTE
AS (
SELECT CASE
WHEN shareClassdata.valueDate IS NULL
THEN NULL
ELSE performanceData.valueDate
END AS valueDate
,CASE
WHEN shareClassdata.benchmarkTypeName IS NULL
THEN NULL
ELSE performanceData.benchmarkTypeName
END AS benchmarkTypeName
,CASE
WHEN shareClassdata.NAVLocal IS NULL
THEN NULL
ELSE performanceData.NAVLocal
END AS NAVLocal
FROM getPerformances(2, 12045, 0, 308, 31) AS performanceData
LEFT JOIN (
SELECT *
FROM getPerformances(2, 12045, 0, 308, 31)
) shareClassdata ON shareClassdata.shareClassGroupId = performanceData.shareClassGroupId
AND shareClassdata.currencyId = performanceData.currencyId
AND shareClassdata.financialStructureGroupId = performanceData.financialStructureGroupId
AND shareClassdata.valueDate = performanceData.valueDate
AND shareClassdata.benchmarkTypeName = 'Fund'
WHERE performanceData.shareClassGroupId = 22050
AND performanceData.valueDate <= '2017-06-30 00:00:00.000'
AND (
isnull(performanceData.valueDate, '') <> ''
AND (
performanceData.benchmarkTypeName = 'Benchmark'
OR performanceData.benchmarkTypeName = 'Fund'
)
)
)
SELECT valueDate
,benchmarkTypeName
,NAVLocal
FROM CTE AS a
WHERE valueDate IS NOT NULL
AND benchmarkTypeName IS NOT NULL
ORDER BY benchmarkTypeName ASC
,valueDate ASC
Дать следующий результат:
+------------+-------------------+---------------+
| valueDate | benchmarkTypeName | NAVLocal |
| 2016-11-30 | Benchmark | 3005.96900000 |
| 2016-12-01 | Benchmark | 2994.49800000 |
| 2016-12-02 | Benchmark | 2981.91900000 |
| 2016-12-05 | Benchmark | 2981.43800000 |
| 2016-12-07 | Benchmark | 3020.05600000 |
| 2016-12-09 | Benchmark | 3110.80600000 |
| 2016-12-12 | Benchmark | 3086.44800000 |
| 2016-12-13 | Benchmark | 3097.51400000 |
| 2016-12-14 | Benchmark | 3069.05100000 |
| 2016-12-15 | Benchmark | 3151.47600000 |
| 2016-12-16 | Benchmark | 3147.68300000 |
| 2016-12-19 | Benchmark | 3145.64400000 |
| 2016-12-20 | Benchmark | 3175.35200000 |
| 2016-12-21 | Benchmark | 3150.92500000 |
| 2016-12-22 | Benchmark | 3138.26200000 |
| 2016-12-23 | Benchmark | 3140.90700000 |
| 2016-12-28 | Benchmark | 3142.34100000 |
| 2016-12-29 | Benchmark | 3112.91600000 |
| 2016-12-30 | Benchmark | 3081.54600000 |
| 2017-01-03 | Benchmark | 3156.68300000 |
| 2017-01-04 | Benchmark | 3147.51700000 |
| 2017-01-05 | Benchmark | 3108.02700000 |
| 2017-01-09 | Benchmark | 3121.82400000 |
| 2017-01-10 | Benchmark | 3111.07500000 |
| 2017-01-11 | Benchmark | 3156.88600000 |
| 2017-01-12 | Benchmark | 3095.46800000 |
| 2017-01-13 | Benchmark | 3109.50500000 |
| 2017-01-16 | Benchmark | 3109.50500000 |
| 2017-01-17 | Benchmark | 3080.41900000 |
| 2017-01-18 | Benchmark | 3086.20700000 |
| 2017-01-19 | Benchmark | 3098.86800000 |
| 2017-01-20 | Benchmark | 3089.86100000 |
| 2017-01-23 | Benchmark | 3062.10800000 |
| 2017-01-24 | Benchmark | 3079.78000000 |
| 2017-01-25 | Benchmark | 3108.25500000 |
| 2017-01-26 | Benchmark | 3127.41500000 |
| 2017-01-27 | Benchmark | 3114.02100000 |
| 2017-01-30 | Benchmark | 3099.60200000 |
| 2017-01-31 | Benchmark | 3063.61700000 |
| 2016-11-30 | Fund | 280.77300000 |
| 2016-12-01 | Fund | 279.07500000 |
| 2016-12-02 | Fund | 278.43100000 |
| 2016-12-05 | Fund | 279.70400000 |
| 2016-12-07 | Fund | 284.04200000 |
| 2016-12-09 | Fund | 290.47600000 |
| 2016-12-12 | Fund | 289.81900000 |
| 2016-12-13 | Fund | 292.15500000 |
| 2016-12-14 | Fund | 290.95000000 |
| 2016-12-15 | Fund | 291.62200000 |
| 2016-12-16 | Fund | 292.25700000 |
| 2016-12-19 | Fund | 293.07300000 |
| 2016-12-20 | Fund | 294.23700000 |
| 2016-12-21 | Fund | 293.81300000 |
| 2016-12-22 | Fund | 292.81400000 |
| 2016-12-23 | Fund | 293.08400000 |
| 2016-12-28 | Fund | 294.89500000 |
| 2016-12-29 | Fund | 294.22000000 |
| 2016-12-30 | Fund | 295.24100000 |
| 2017-01-03 | Fund | 296.37400000 |
| 2017-01-04 | Fund | 294.59900000 |
| 2017-01-05 | Fund | 295.30700000 |
| 2017-01-09 | Fund | 294.18400000 |
| 2017-01-10 | Fund | 294.42100000 |
| 2017-01-11 | Fund | 294.96700000 |
| 2017-01-12 | Fund | 293.68800000 |
| 2017-01-13 | Fund | 295.94300000 |
| 2017-01-16 | Fund | 294.73900000 |
| 2017-01-17 | Fund | 294.24300000 |
| 2017-01-18 | Fund | 295.48600000 |
| 2017-01-19 | Fund | 294.83300000 |
| 2017-01-20 | Fund | 294.29800000 |
| 2017-01-23 | Fund | 293.80800000 |
| 2017-01-24 | Fund | 294.64100000 |
| 2017-01-25 | Fund | 296.76600000 |
| 2017-01-26 | Fund | 297.37800000 |
| 2017-01-27 | Fund | 297.26900000 |
| 2017-01-30 | Fund | 294.67800000 |
| 2017-01-31 | Fund | 292.99700000 |
+------------+-------------------+---------------+
Мне нужно перебазировать третий столбец до 100, поэтому я написал следующее, которое отлично работает:
WITH CTE
AS (
SELECT CASE
WHEN shareClassdata.valueDate IS NULL
THEN NULL
ELSE performanceData.valueDate
END AS valueDate
,CASE
WHEN shareClassdata.benchmarkTypeName IS NULL
THEN NULL
ELSE performanceData.benchmarkTypeName
END AS benchmarkTypeName
,CASE
WHEN shareClassdata.NAVLocal IS NULL
THEN NULL
ELSE performanceData.NAVLocal
END AS NAVLocal
FROM getPerformances(2, 12045, 0, 308, 31) AS performanceData
LEFT JOIN (
SELECT *
FROM getPerformances(2, 12045, 0, 308, 31)
) shareClassdata ON shareClassdata.shareClassGroupId = performanceData.shareClassGroupId
AND shareClassdata.currencyId = performanceData.currencyId
AND shareClassdata.financialStructureGroupId = performanceData.financialStructureGroupId
AND shareClassdata.valueDate = performanceData.valueDate
AND shareClassdata.benchmarkTypeName = 'Fund'
WHERE performanceData.shareClassGroupId = 22050
AND performanceData.valueDate <= '2017-06-30 00:00:00.000'
AND (
isnull(performanceData.valueDate, '') <> ''
AND (
performanceData.benchmarkTypeName = 'Benchmark'
OR performanceData.benchmarkTypeName = 'Fund'
)
)
)
SELECT valueDate
,benchmarkTypeName
,(
sum(a.NAVLocal) / (
SELECT TOP 1 b.NAVLocal
FROM CTE AS b
WHERE b.NAVLocal != 0
AND a.benchmarkTypeName = b.benchmarkTypeName
ORDER BY b.valueDate ASC
)
) * 100 AS NAVLocal
FROM CTE AS a
WHERE valueDate IS NOT NULL
AND benchmarkTypeName IS NOT NULL
GROUP BY valueDate
,benchmarkTypeName
ORDER BY benchmarkTypeName ASC
,valueDate ASC
это дает следующий результат (примечание: столбец NAVLocal просто показывает вам старое и предыдущее значение):
+------------+-------------------+----------+------------------+
| valueDate | benchmarkTypeName | NAVLocal | NAVLocal rebased |
| 2016-11-30 | Benchmark | 3005.969 | 100 |
| 2016-12-01 | Benchmark | 2994.498 | 99.6183 |
| 2016-12-02 | Benchmark | 2981.919 | 99.1999 |
| 2016-12-05 | Benchmark | 2981.438 | 99.1839 |
| 2016-12-07 | Benchmark | 3020.056 | 100.4686 |
| 2016-12-09 | Benchmark | 3110.806 | 103.4876 |
| 2016-12-12 | Benchmark | 3086.448 | 102.6773 |
| 2016-12-13 | Benchmark | 3097.514 | 103.0454 |
| 2016-12-14 | Benchmark | 3069.051 | 102.0985 |
| 2016-12-15 | Benchmark | 3151.476 | 104.8406 |
| 2016-12-16 | Benchmark | 3147.683 | 104.7144 |
| 2016-12-19 | Benchmark | 3145.644 | 104.6465 |
| 2016-12-20 | Benchmark | 3175.352 | 105.6348 |
| 2016-12-21 | Benchmark | 3150.925 | 104.8222 |
| 2016-12-22 | Benchmark | 3138.262 | 104.401 |
| 2016-12-23 | Benchmark | 3140.907 | 104.489 |
| 2016-12-28 | Benchmark | 3142.341 | 104.5367 |
| 2016-12-29 | Benchmark | 3112.916 | 103.5578 |
| 2016-12-30 | Benchmark | 3081.546 | 102.5142 |
| 2017-01-03 | Benchmark | 3156.683 | 105.0138 |
| 2017-01-04 | Benchmark | 3147.517 | 104.7088 |
| 2017-01-05 | Benchmark | 3108.027 | 103.3951 |
| 2017-01-09 | Benchmark | 3121.824 | 103.8541 |
| 2017-01-10 | Benchmark | 3111.075 | 103.4965 |
| 2017-01-11 | Benchmark | 3156.886 | 105.0205 |
| 2017-01-12 | Benchmark | 3095.468 | 102.9773 |
| 2017-01-13 | Benchmark | 3109.505 | 103.4443 |
| 2017-01-16 | Benchmark | 3109.505 | 103.4443 |
| 2017-01-17 | Benchmark | 3080.419 | 102.4767 |
| 2017-01-18 | Benchmark | 3086.207 | 102.6692 |
| 2017-01-19 | Benchmark | 3098.868 | 103.0904 |
| 2017-01-20 | Benchmark | 3089.861 | 102.7908 |
| 2017-01-23 | Benchmark | 3062.108 | 101.8675 |
| 2017-01-24 | Benchmark | 3079.78 | 102.4554 |
| 2017-01-25 | Benchmark | 3108.255 | 103.4027 |
| 2017-01-26 | Benchmark | 3127.415 | 104.0401 |
| 2017-01-27 | Benchmark | 3114.021 | 103.5945 |
| 2017-01-30 | Benchmark | 3099.602 | 103.1149 |
| 2017-01-31 | Benchmark | 3063.617 | 101.9177 |
| 2016-11-30 | Fund | 280.773 | 100 |
| 2016-12-01 | Fund | 279.075 | 99.3952 |
| 2016-12-02 | Fund | 278.431 | 99.1658 |
| 2016-12-05 | Fund | 279.704 | 99.6192 |
| 2016-12-07 | Fund | 284.042 | 101.1642 |
| 2016-12-09 | Fund | 290.476 | 103.4558 |
| 2016-12-12 | Fund | 289.819 | 103.2218 |
| 2016-12-13 | Fund | 292.155 | 104.0538 |
| 2016-12-14 | Fund | 290.95 | 103.6246 |
| 2016-12-15 | Fund | 291.622 | 103.8639 |
| 2016-12-16 | Fund | 292.257 | 104.0901 |
| 2016-12-19 | Fund | 293.073 | 104.3807 |
| 2016-12-20 | Fund | 294.237 | 104.7953 |
| 2016-12-21 | Fund | 293.813 | 104.6443 |
| 2016-12-22 | Fund | 292.814 | 104.2885 |
| 2016-12-23 | Fund | 293.084 | 104.3846 |
| 2016-12-28 | Fund | 294.895 | 105.0296 |
| 2016-12-29 | Fund | 294.22 | 104.7892 |
| 2016-12-30 | Fund | 295.241 | 105.1529 |
| 2017-01-03 | Fund | 296.374 | 105.5564 |
| 2017-01-04 | Fund | 294.599 | 104.9242 |
| 2017-01-05 | Fund | 295.307 | 105.1764 |
| 2017-01-09 | Fund | 294.184 | 104.7764 |
| 2017-01-10 | Fund | 294.421 | 104.8608 |
| 2017-01-11 | Fund | 294.967 | 105.0553 |
| 2017-01-12 | Fund | 293.688 | 104.5998 |
| 2017-01-13 | Fund | 295.943 | 105.4029 |
| 2017-01-16 | Fund | 294.739 | 104.9741 |
| 2017-01-17 | Fund | 294.243 | 104.7974 |
| 2017-01-18 | Fund | 295.486 | 105.2401 |
| 2017-01-19 | Fund | 294.833 | 105.0076 |
| 2017-01-20 | Fund | 294.298 | 104.817 |
| 2017-01-23 | Fund | 293.808 | 104.6425 |
| 2017-01-24 | Fund | 294.641 | 104.9392 |
| 2017-01-25 | Fund | 296.766 | 105.696 |
| 2017-01-26 | Fund | 297.378 | 105.914 |
| 2017-01-27 | Fund | 297.269 | 105.8752 |
| 2017-01-30 | Fund | 294.678 | 104.9523 |
| 2017-01-31 | Fund | 292.997 | 104.3536 |
+------------+-------------------+----------+------------------+
Однако запрос выполняется очень и очень медленно на большом наборе данных. Есть ли лучший способ выполнить этот расчет?
Напоминание о перебазировании номеров:
Для каждой серии бенчмарков результат сортируется по возрастанию на дату валютирования. Наиболее важным значением является самая старая запись каждой серии. Каждое значение NAV серии делится на самый старый NAV, умножается на 100
Вы можете получить доступ к этой таблице с помощью формулы https://docs.google.com/spreadsheets/d/1esChCFPTLa9df3xWuLmT5-JONsS8j8vTZpAjsx7fb9M/edit?usp=sharing
Спасибо
1 ответ
Решение
Оконные функции могут помочь улучшить производительность, я начинаю с первого вывода, но вы должны понять, как этого можно достичь с помощью оконной функции.
USE
tempdb
GO
DROP TABLE IF EXISTS Rebase;
CREATE TABLE Rebase(
valueDate DATE NOT NULL
,benchmarkTypeName VARCHAR(9) NOT NULL
,NAVLocal NUMERIC(8,3) NOT NULL
);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-11-30','Benchmark',3005.969);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-01','Benchmark',2994.498);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-02','Benchmark',2981.919);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-05','Benchmark',2981.438);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-07','Benchmark',3020.056);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-09','Benchmark',3110.806);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-12','Benchmark',3086.448);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-13','Benchmark',3097.514);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-14','Benchmark',3069.051);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-15','Benchmark',3151.476);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-16','Benchmark',3147.683);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-19','Benchmark',3145.644);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-20','Benchmark',3175.352);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-21','Benchmark',3150.925);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-22','Benchmark',3138.262);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-23','Benchmark',3140.907);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-28','Benchmark',3142.341);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-29','Benchmark',3112.916);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-30','Benchmark',3081.546);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-03','Benchmark',3156.683);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-04','Benchmark',3147.517);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-05','Benchmark',3108.027);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-09','Benchmark',3121.824);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-10','Benchmark',3111.075);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-11','Benchmark',3156.886);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-12','Benchmark',3095.468);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-13','Benchmark',3109.505);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-16','Benchmark',3109.505);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-17','Benchmark',3080.419);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-18','Benchmark',3086.207);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-19','Benchmark',3098.868);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-20','Benchmark',3089.861);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-23','Benchmark',3062.108);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-24','Benchmark',3079.78);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-25','Benchmark',3108.255);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-26','Benchmark',3127.415);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-27','Benchmark',3114.021);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-30','Benchmark',3099.602);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-31','Benchmark',3063.617);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-11-30','Fund',280.773);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-01','Fund',279.075);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-02','Fund',278.431);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-05','Fund',279.704);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-07','Fund',284.042);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-09','Fund',290.476);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-12','Fund',289.819);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-13','Fund',292.155);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-14','Fund',290.95);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-15','Fund',291.622);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-16','Fund',292.257);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-19','Fund',293.073);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-20','Fund',294.237);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-21','Fund',293.813);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-22','Fund',292.814);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-23','Fund',293.084);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-28','Fund',294.895);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-29','Fund',294.22);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2016-12-30','Fund',295.241);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-03','Fund',296.374);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-04','Fund',294.599);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-05','Fund',295.307);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-09','Fund',294.184);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-10','Fund',294.421);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-11','Fund',294.967);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-12','Fund',293.688);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-13','Fund',295.943);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-16','Fund',294.739);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-17','Fund',294.243);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-18','Fund',295.486);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-19','Fund',294.833);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-20','Fund',294.298);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-23','Fund',293.808);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-24','Fund',294.641);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-25','Fund',296.766);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-26','Fund',297.378);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-27','Fund',297.269);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-30','Fund',294.678);
INSERT INTO Rebase(valueDate,benchmarkTypeName,NAVLocal) VALUES ('2017-01-31','Fund',292.997);
SELECT
valueDate
, benchmarkTypeName
, NAVLocal
, FIRST_VALUE(NAVLocal) OVER(PARTITION BY benchmarkTypeName ORDER BY valueDate) AS Oldest
, (NAVLOCAL / FIRST_VALUE(NAVLocal) OVER(PARTITION BY benchmarkTypeName ORDER BY valueDate)) * 100 aS RebaseValue
FROM
Rebase