TechQA.

SQL : Rebase number to 100

730 views Asked by At

I have the following query :

 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

Giving the following result :

+------------+-------------------+---------------+
| 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  |
+------------+-------------------+---------------+

I need to rebase the third column to 100 so i wrote the following which work perfectly :

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

it gives the following result ( note : the column NAVLocal is just there to show you the old vs previous value ) :

+------------+-------------------+----------+------------------+
| 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         |
+------------+-------------------+----------+------------------+

However the query is running very very very slow on a large dataset. Do there is a better way to perform this calculation ?

A reminder about rebasing numbers :
For each series of benchmark, the result is sorted ascending on the value date.The most important value is the oldest record of each serie. Each NAV value of the serie is divided by the oldest NAV, multiply by 100

You can access this spreadsheet with the formula https://docs.google.com/spreadsheets/d/1esChCFPTLa9df3xWuLmT5-JONsS8j8vTZpAjsx7fb9M/edit?usp=sharing

Thank you

sql sql-server sql-server-2016 rebasing
Original Q&A
1

There are 1 answers

2
mhep On BEST ANSWER

Window functions may help improve the performance, I am starting from the first output but you should get the idea of how it can be achieved with a window function

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

Related Questions in SQL

Related Questions in SQL-SERVER

Related Questions in SQL-SERVER-2016

Related Questions in REBASING

Popular Questions

Popular Tags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs node.js arrays c asp.net json python-3.x ruby-on-rails .net sql-server swift django angular objective-c pandas excel

Trending Questions