All,
I have a table with periods and a value for an ID.
An ID can have multiple periods. For example:
| ID | begin_date | end_date | value |
|---|
| 1 | 01-Jul-16 | 14-Jul-16 | 40 |
| 1 | 15-Jul-16 | 31-jul-16 | 50 |
| 2 | 12-aug-16 | 23-aug-16 | 40 |
| 3 | 01-jul-16 | 31-jul-16 | 90 |
For a given date range, I want to calculate the date difference between the periods and the given date range for the ID.
Say for example date range is 01-jul-16 untill 29-jul-16
Then the given output should be:
| ID | begin_date | end_date | value | date_diff |
|---|
| 1 | 01-Jul-16 | 14-Jul-16 | 40 | 14 |
| 1 | 15-Jul-16 | 31-jul-16 | 50 | 15 |
| 2 | 12-aug-16 | 23-aug-16 | 40 | 0 |
| 3 | 01-jul-16 | 31-jul-16 | 90 | 29 |
How can I achieve this?, Is it possible?
Create table t_period (id number
,begin_date date
,end_date date
,value number
);
insert into t_period(id,begin_date,end_date,value)
values(1,'01-jul-16','14-jul-16',40);
insert into t_period(id,begin_date,end_date,value)
values(1,'15-jul-16','31-jul-16',50);
insert into t_period(id,begin_date,end_date,value)
values(3,'01-jul-16','31-jul-16',90);
insert into t_period(id,begin_date,end_date,value)
values(2,'12-aug-16','23-aug-16',40);
commit;
Thanks,
Diana