this doesn't call for the model clause. this calls for
a) something to generate the set of times - a with clause, or connect by on dual
b) a join
c) an aggregate involving listagg.
ops$tkyte%ORA11GR2> with times
2 as
3 (select trunc(sysdate,'mm')+ level * 15/24/60 tm
4 from dual
5 connect by level <= 24*4-1)
6 select tm, listagg(id,',') within group (order by id) ids
7 from (
8 select *
9 from times, t
10 where times.tm between to_date(to_char(t.sdate,'hh24miss'), 'hh24miss') and to_date( to_char( t.edate, 'hh24miss'), 'hh24miss' )
11 )
12 group by tm
13 order by tm
14 /
TM IDS
------------------- ---------------------------------------------------------------------------
05/01/2013 07:15:00 2196
05/01/2013 07:30:00 2196
05/01/2013 07:45:00 1957,2129,2196
05/01/2013 08:00:00 1559,1957,2078,2129,2196
05/01/2013 08:15:00 1559,1957,2078,2129,2130,2131,2176,2177,2178,2196,2210
05/01/2013 08:30:00 1559,1957,2078,2129,2130,2131,2176,2177,2178,2196,2210
05/01/2013 08:45:00 1559,1957,2078,2129,2130,2131,2176,2177,2178,2196,2210
05/01/2013 09:00:00 1559,1957,2078,2131,2176,2196,2200,2210
05/01/2013 09:15:00 1559,1639,1806,1957,2078,2131,2176,2196,2200,2210
05/01/2013 09:30:00 1639,1806,1957,2078,2131,2176,2196,2200,2210
05/01/2013 09:45:00 1639,1806,1957,2078,2131,2176,2196,2200,2210
05/01/2013 10:00:00 1639,1806,1957,2078,2131,2176,2196,2200,2210
05/01/2013 10:15:00 1639,1806,1957,2078,2131,2176,2196,2200,2210
05/01/2013 10:30:00 1639,1806,1957,2078,2131,2176,2196,2200,2210
05/01/2013 10:45:00 1639,1806,1957,2078,2131,2176,2196,2200,2210
05/01/2013 11:00:00 1639,1806,1957,2078,2131,2176,2196,2200,2210
05/01/2013 11:15:00 1639,1806,1957,2078,2131,2176,2196,2200,2210
05/01/2013 11:30:00 1639,1806,1957,2078,2131,2133,2176,2196,2200,2210
05/01/2013 11:45:00 1639,1806,1957,2078,2131,2133,2176,2196,2200,2210
05/01/2013 12:00:00 1639,1806,1957,2078,2131,2133,2176,2196,2200,2210
05/01/2013 12:15:00 1639,1806,1957,2078,2131,2133,2176,2196,2200,2210
05/01/2013 12:30:00 1639,1806,1957,2078,2131,2133,2176,2196,2200,2210
05/01/2013 12:45:00 1639,1806,1957,2078,2131,2133,2176,2196,2200,2210
05/01/2013 13:00:00 1639,1806,1957,2078,2131,2133,2176,2196,2200,2210
05/01/2013 13:15:00 1639,1806,1957,2078,2131,2133,2176,2196,2200,2210
05/01/2013 13:30:00 1639,1806,1957,2078,2131,2133,2176,2196,2200,2210
05/01/2013 13:45:00 1639,1806,1957,2078,2131,2133,2176,2196,2200,2210
05/01/2013 14:00:00 1639,1806,1957,2078,2131,2133,2176,2196,2200,2210
05/01/2013 14:15:00 1559,1639,1806,1957,2078,2131,2133,2176,2196,2200,2210
05/01/2013 14:30:00 1559,1639,1806,1957,2078,2131,2176,2196,2200,2210
05/01/2013 14:45:00 1559,1639,1806,1957,2078,2131,2176,2196,2200,2210
05/01/2013 15:00:00 1559,1639,1806,1957,2078,2131,2176,2196,2200,2210
05/01/2013 15:15:00 1559,1639,1806,1865,1957,2078,2130,2131,2176,2177,2178,2196,2200,2210
05/01/2013 15:30:00 1559,1639,1806,1865,1957,2078,2130,2131,2176,2177,2178,2196,2200,2210
05/01/2013 15:45:00 1559,1639,1806,1865,1957,2078,2130,2131,2176,2177,2178,2196,2200,2210
05/01/2013 16:00:00 1559,1806,1865,1957,2078,2130,2131,2176,2177,2178,2196,2200,2210
05/01/2013 16:15:00 1559,1806,1865,1957,2078,2130,2131,2176,2177,2178,2196,2200,2210
05/01/2013 16:30:00 1559,1806,1865,1957,2078,2130,2131,2176,2177,2178,2196,2200,2210
05/01/2013 16:45:00 1559,1806,1865,1957,2078,2130,2131,2176,2177,2178,2196,2200,2210
05/01/2013 17:00:00 1559,1865,1957,2078,2130,2131,2176,2177,2178,2196,2200,2210
05/01/2013 17:15:00 1559,2130,2131,2176,2177,2178,2196,2200
05/01/2013 17:30:00 2130,2131,2196,2200
05/01/2013 17:45:00 2130,2131,2196
05/01/2013 18:00:00 2130,2131,2196
44 rows selected.
I had to make an assumption that the date just didn't matter. we could simplify this if the date is going to be passed in.
ops$tkyte%ORA11GR2> variable input varchar2(30)
ops$tkyte%ORA11GR2> exec :input := '09/05/2013';
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> with times
2 as
3 (select to_date(:input,'mm/dd/yyyy')+ level * 15/24/60 tm
4 from dual
5 connect by level <= 24*4-1)
6 select tm, listagg(id,',') within group (order by id) ids
7 from (
8 select *
9 from times, t
10 where times.tm between t.sdate and t.edate
11 and t.sdate <= to_date(:input,'mm/dd/yyyy')+1
12 and t.edate >= to_date(:input,'mm/dd/yyyy')
13 )
14 group by tm
15 order by tm
16 /
TM IDS
------------------- ---------------------------------------------------------------------------
09/05/2013 07:15:00 2196
09/05/2013 07:30:00 2196
09/05/2013 07:45:00 1957,2129,2196
09/05/2013 08:00:00 1559,1957,2078,2129,2196
09/05/2013 08:15:00 1559,1957,2078,2129,2130,2131,2176,2177,2178,2196,2210
09/05/2013 08:30:00 1559,1957,2078,2129,2130,2131,2176,2177,2178,2196,2210
09/05/2013 08:45:00 1559,1957,2078,2129,2130,2131,2176,2177,2178,2196,2210
09/05/2013 09:00:00 1559,1957,2078,2131,2176,2196,2200,2210
09/05/2013 09:15:00 1559,1639,1806,1957,2078,2131,2176,2196,2200,2210
09/05/2013 09:30:00 1639,1806,1957,2078,2131,2176,2196,2200,2210
09/05/2013 09:45:00 1639,1806,1957,2078,2131,2176,2196,2200,2210
09/05/2013 10:00:00 1639,1806,1957,2078,2131,2176,2196,2200,2210
09/05/2013 10:15:00 1639,1806,1957,2078,2131,2176,2196,2200,2210
09/05/2013 10:30:00 1639,1806,1957,2078,2131,2176,2196,2200,2210
09/05/2013 10:45:00 1639,1806,1957,2078,2131,2176,2196,2200,2210
09/05/2013 11:00:00 1639,1806,1957,2078,2131,2176,2196,2200,2210
09/05/2013 11:15:00 1639,1806,1957,2078,2131,2176,2196,2200,2210
09/05/2013 11:30:00 1639,1806,1957,2078,2131,2133,2176,2196,2200,2210
09/05/2013 11:45:00 1639,1806,1957,2078,2131,2133,2176,2196,2200,2210
09/05/2013 12:00:00 1639,1806,1957,2078,2131,2133,2176,2196,2200,2210
09/05/2013 12:15:00 1639,1806,1957,2078,2131,2133,2176,2196,2200,2210
09/05/2013 12:30:00 1639,1806,1957,2078,2131,2133,2176,2196,2200,2210
09/05/2013 12:45:00 1639,1806,1957,2078,2131,2133,2176,2196,2200,2210
09/05/2013 13:00:00 1639,1806,1957,2078,2131,2133,2176,2196,2200,2210
09/05/2013 13:15:00 1639,1806,1957,2078,2131,2133,2176,2196,2200,2210
09/05/2013 13:30:00 1639,1806,1957,2078,2131,2133,2176,2196,2200,2210
09/05/2013 13:45:00 1639,1806,1957,2078,2131,2133,2176,2196,2200,2210
09/05/2013 14:00:00 1639,1806,1957,2078,2131,2133,2176,2196,2200,2210
09/05/2013 14:15:00 1559,1639,1806,1957,2078,2131,2133,2176,2196,2200,2210
09/05/2013 14:30:00 1559,1639,1806,1957,2078,2131,2176,2196,2200,2210
09/05/2013 14:45:00 1559,1639,1806,1957,2078,2131,2176,2196,2200,2210
09/05/2013 15:00:00 1559,1639,1806,1957,2078,2131,2176,2196,2200,2210
09/05/2013 15:15:00 1559,1639,1806,1865,1957,2078,2130,2131,2176,2177,2178,2196,2200,2210
09/05/2013 15:30:00 1559,1639,1806,1865,1957,2078,2130,2131,2176,2177,2178,2196,2200,2210
09/05/2013 15:45:00 1559,1639,1806,1865,1957,2078,2130,2131,2176,2177,2178,2196,2200,2210
09/05/2013 16:00:00 1559,1806,1865,1957,2078,2130,2131,2176,2177,2178,2196,2200,2210
09/05/2013 16:15:00 1559,1806,1865,1957,2078,2130,2131,2176,2177,2178,2196,2200,2210
09/05/2013 16:30:00 1559,1806,1865,1957,2078,2130,2131,2176,2177,2178,2196,2200,2210
09/05/2013 16:45:00 1559,1806,1865,1957,2078,2130,2131,2176,2177,2178,2196,2200,2210
09/05/2013 17:00:00 1559,1865,1957,2078,2130,2131,2176,2177,2178,2196,2200,2210
09/05/2013 17:15:00 1559,2130,2131,2176,2177,2178,2196,2200
09/05/2013 17:30:00 2130,2131,2196,2200
09/05/2013 17:45:00 2130,2131,2196
09/05/2013 18:00:00 2130,2131,2196
44 rows selected.