I have two tables, first table section with schema as:
SecID | Date | SecReturn
-------|---------------|--------------
208 | 2015-04-01 | 0.00355
208 | 2015-04-02 | -0.00578
208 | 2015-04-03 | 0.00788
208 | 2015-04-04 | 0.08662
105 | 2015-04-01 | 0.00786
and the second table SectionDates with schema as:
SecID | MonthlyDate | DailyDate
------|---------------|-------------
208 | 2015-04-02 | 2015-04-03
105 | 2015-04-01 | 2015-04-01
I want to calculate the running product on SecReturn column of the table Section with date range (DailyDate to MonthlyDate) from second table SectionDates.
Running product will be calculated for each sectionID based on formula :
Date | SecReturn | SectionTotal
-----------|---------------|--------------------
2015-04-01 | X (lets say) | (1+x)-1
2015-04-01 | Y | (1+x)(1+y)-1
2015-04-01 | Z | (1+x)(1+y)(1+z)-1
After applying above calculation values will be computed in SectionTotal column as for date 2015-04-01 computed value will be (1+0.00355)-1. Similarly, for date 2015-04-02 computed value will be (1+0.00355)(1+-0.00578)-1 and for date 2015-04-03 computed value will be (1+0.00355)(1+-0.00578)(1+0.00788)-1 and so on.
The final output:
SecID | Date | SectionTotal
-------|------------|-----------------
105 | 2015-04-01 | 0.00786
208 | 2015-04-01 | 0.00355
208 | 2015-04-02 | -0.0022505
208 | 2015-04-03 | 0.0056117
Aucun commentaire:
Enregistrer un commentaire