Sorry for the imprecise title, but I'm not sure how to describe my issue concisely.
I have a relation table which links "modules" to any combination of three different types of "multipliers". Each row in the table includes a start and end date for the relation, and each type of multipler can be changed independently of the others. If I select out a single module-ID, the result might look something like this:
| ModuleID | MultiplierType | StartDate | EndDate | MultiplierValue |
|---|
| 254 | A | 19.01.2016 | null | 5 |
| 254 | B | 20.02.2015 | 30.03.2016 | 200 |
| 254 | B | 30.03.2016 | null | 100 |
| 254 | C | 15.09.2014 | null | 7 |
I want to write a query that will take these rows and output a table with the total multiplier for each time period. I.E.:
| StartDate | EndDate | TotalMultiplier |
|---|
| 15.09.2014 | 20.02.2015 | 7 |
| 20.02.2015 | 19.01.2016 | 1400 |
| 19.01.2016 | 30.03.2016 | 7000 |
| 30.03.2016 | null | 3500 |
Can someone suggest how to tackle this problem?
Database version: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0