Hi, I have data containing a task name and it's start and end date.
Now, I want new data based on above that will tell how many tasks were running at any given date.
Assuming that time stamp for each date is 00:00:00, and tasks start at Start_Dt but end just before End_dt (lower bound included, upper bound not)
Input:
Task Start_Dt End_D
A 2013-12-01 2013-12-05
B 2013-12-03 2013-12-04
C 2013-12-03 2013-12-04
D 2013-12-03 2013-12-06
E 2013-12-04 2013-12-05
F 2013-12-06 2013-12-07
Output:
| Start | End | Count | (Jobs) |
| 2013-12-01 | 2013-12-03 | 1 | (A) |
| 2013-12-03 | 2013-12-04 | 4 | (A,B,C,D) |
| 2013-12-04 | 2013-12-05 | 3 | (A,D,E) |
| 2013-12-05 | 2013-12-06 | 1 | (D) |
| 2013-12-06 | 2013-12-07 | 1 | (F) |
In the above, Task B should not be in 3rd row since it ended on 12/04 and hence not to be included in the 12/04 - 12/05 range (and B was not running in this period)
alter session set nls_date_format = 'YYYY-MM-DD';
select tmp.* from
(
select 'A' TASK, to_date('2013-12-01','YYYY-MM-DD') START_DT, to_date('2013-12-05','YYYY-MM-DD') END_DT from dual
union
select 'B' TASK, to_date('2013-12-03','YYYY-MM-DD') START_DT, to_date('2013-12-04','YYYY-MM-DD') END_DT from dual
union
select 'C' TASK, to_date('2013-12-03','YYYY-MM-DD') START_DT, to_date('2013-12-04','YYYY-MM-DD') END_DT from dual
union
select 'D' TASK, to_date('2013-12-03','YYYY-MM-DD') START_DT, to_date('2013-12-06','YYYY-MM-DD') END_DT from dual
union
select 'E' TASK, to_date('2013-12-04','YYYY-MM-DD') START_DT, to_date('2013-12-05','YYYY-MM-DD') END_DT from dual
union
select 'F' TASK, to_date('2013-12-06','YYYY-MM-DD') START_DT, to_date('2013-12-07','YYYY-MM-DD') END_DT from dual
)tmp
order by 2,3
;
How to write the sql on the input ('tmp' table)?
Thanks,
-srinivas y.
Message was edited by: ysri
Message was edited by: ysri. Updated tasks and dates.