SELECT count(*) as num_apps, to_date(created_ts,'DD-Mon-RR') as app_date, to_char(created_ts,'HH24') as app_hour
FROM accounts
WHERE To_Date(created_ts,'DD-Mon-RR') >= To_Date('16-Aug-12','DD-Mon-RR')
GROUP BY To_Date(created_ts,'DD-Mon-RR'), To_Char(created_ts,'HH24')
ORDER BY app_date, app_hour

SELECT NVL(c.num_apps,0) as num_apps, t.app_date, t.app_hour
    FROM time_table t
    LEFT OUTER JOIN
    (
    SELECT count(*) as num_apps, to_date(created_ts,'DD-Mon-RR') as app_date, to_char(created_ts,'HH24') as app_hour
    FROM accounts
    WHERE To_Date(created_ts,'DD-Mon-RR') >= To_Date('16-Aug-12','DD-Mon-RR')
    GROUP BY To_Date(created_ts,'DD-Mon-RR'), To_Char(created_ts,'HH24')
    ORDER BY app_date, app_hour
    ) c ON (t.app_date = c.app_date AND t.app_hour = c.app_hour)

select level
FROM Dual
CONNECT BY level <= 10
ORDER BY level;

select 0 as num_apps, (To_Date('16-09-12','DD-MM-RR') + level / 24) as created_ts
FROM dual
CONNECT BY level <= (sysdate - To_Date('16-09-12','DD-MM-RR')) * 24 ;

SELECT SUM(num_apps) as num_apps, to_date(created_ts,'DD-Mon-RR') as app_date, to_char(created_ts,'HH24') as app_hour
FROM(
  SELECT count(*) as num_apps, created_ts
  FROM accounts
  WHERE To_Date(created_ts,'DD-Mon-RR') >= To_Date('16-09-12','DD-MM-RR')
UNION ALL
  select 0 as num_apps, (To_Date('16-09-12','DD-MM-RR') + level / 24) as created_ts
  FROM dual
  CONNECT BY level <= (sysdate - To_Date('16-09-12','DD-MM-RR')) * 24 ;
)
GROUP BY To_Date(created_ts,'DD-Mon-RR'), To_Char(created_ts,'HH24')
ORDER BY app_date, app_hour
;

create table dbo.sequence
(
  id int not null primary key clustered ,
)

create table dbo.SomeTable
(
  account_id   int  not null primary key clustered ,
  date_created date not null ,
  time_created time not null ,
)

select year_created  = years.id  ,
       month_created = months.id ,
       day_created   = days.id   ,
       hour_created  = hours.id  ,
       volume        = t.volume
from       ( select * ,
                    is_leap_year = case
                                   when id % 400 = 0 then 1
                                   when id % 100 = 0 then 0
                                   when id %   4 = 0 then 1
                                   else                   0
                                   end
             from dbo.sequence
             where id between 1980 and year(current_timestamp)
           ) years
cross join ( select *
             from dbo.sequence
             where id between 1 and 12
           ) months
left  join ( select *
             from dbo.sequence
             where id between 1 and 31
           ) days on days.id <= case months.id
                                when  2 then 28 + years.is_leap_year
                                when  4 then 30
                                when  6 then 30
                                when  9 then 30
                                when 11 then 30
                                else         31
                                end
cross join ( select *
             from dbo.sequence
             where id between 0 and 23
           ) hours
left join ( select date_created ,
                   hour_created = datepart(hour,time_created ) ,
                   volume = count(*)
            from dbo.SomeTable
            group by date_created ,
                     datepart(hour,time_created)
          ) t on datepart( year  , t.date_created ) = years.id
             and datepart( month , t.date_created ) = months.id
             and datepart( day   , t.date_created ) = days.id
             and t.hour_created                     = hours.id
order by 1,2,3,4

SELECT count(*) as num_apps, to_char(created_ts,'DD-Mon-RR') as app_date, to_char(created_ts,'HH24') as app_hour
FROM (select level-1 as hour FROM Dual CONNECT BY level <= 24) h
  LEFT JOIN accounts a on h.hour = to_number(to_char(a.created_ts,'HH24'))
WHERE created_ts >= To_Date('16-Aug-12','DD-Mon-RR')
GROUP BY trunc(created_ts), h.hour
ORDER BY app_date, app_hour