SQL Query Help (Advanced - for me!)

Question

I have a question about a SQL query I am trying to write.

I need to query data from a database. The database has, amongst others, these 3 fields:

Account_ID #, Date_Created, Time_Created

I need to write a query that tells me how many accounts were opened per hour.

I have written said query, but there are times that there were 0 accounts created, so these "hours" are not populated in the results.

For example:

Volume Date__Hour
435 12-Aug-12 03
213 12-Aug-12 04
125 12-Aug-12 06

As seen in the example above, hour 5 did not have any accounts opened.

Is there a way that the result can populate the hour but and display 0 accounts opened for this hour? Example of how I want my results to look like:

Volume Date_Hour
435 12-Aug-12 03
213 12-Aug-12 04
0 12-Aug-12 05
125 12-Aug-12 06

Thanks!

Update: This is what I have so far

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

Answer 1

It's not clear to me if created_ts is a datetime or a varchar. If it's a datetime, you shouldn't use to_date; if it's a varchar, you shouldn't use to_char.

Assuming it's a datetime, and borrowing @jakub.petr's FROM Dual CONNECT BY level trick, I suggest:

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

Answer 2

I believe the best solution is not to create some fancy temporary table but just use this construct:

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

This will give you (in ten rows): 1 2 3 4 5 6 7 8 9 10

For hours interval just little modification:

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 ;  

And just for the fun of it adding solution for you(I didn't try syntax, so I'm sorry for any mistake, but the idea is clear):

 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
;

Answer 3

It can be useful to have a "sequence table" kicking around, for all sorts of reasons, something that looks like this:

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

Load it up with million or so rows, covering positive and negative values.

Then, given a table that looks like this

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

Your query is then as simple as (in SQL Server):

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

Answer 4

To get the results you want, you will need to create a table (or use a query to generate a "temp" table) and then use a left join to your calculation query to get rows for every hour - even those with 0 volume.

For example, assume I have a table with app_date and app_hour fields. Also assume that this table has a row for every day/hour you wish to report on.

The query would be:

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)

Answer 5

You can also use a CASE statement in the SELECT to force the value you want.