Home » SQL & PL/SQL » SQL & PL/SQL » Group by Hours - Show Missing Hours (Oracle 9i (9.2.0.4) HP-UX B.11.11 )
icon5.gif  Group by Hours - Show Missing Hours [message #430359] Tue, 10 November 2009 03:28 Go to next message
alfa_mark
Messages: 2
Registered: November 2009
Location: Markfield, England
Junior Member
Hi all,

I would like some help with a query that I am struggling with please.

I am tring to create some counts by hours for a spreadsheet, so it is important that I also get a count of 0 where no data exists. I had planned to outer join to a hours table, containing the values '00' to '23', but I am not succeeding.

create table hours (hour varchar2(2));
insert into hours values('00');
insert into hours values('01');
insert into hours values('02');
insert into hours values('03');
insert into hours values('04');
insert into hours values('05');
insert into hours values('06');
insert into hours values('07');
insert into hours values('08');
insert into hours values('09');
insert into hours values('10');
insert into hours values('11');
insert into hours values('12');
insert into hours values('13');
insert into hours values('14');
insert into hours values('15');
insert into hours values('16');
insert into hours values('17');
insert into hours values('18');
insert into hours values('19');
insert into hours values('20');
insert into hours values('21');
insert into hours values('22');
insert into hours values('23');

create table incoming_data
(TRANSACTION_NUMBER VARCHAR2(20 BYTE) NOT NULL,
 INTERFACE_NAME VARCHAR2 (50 BYTE) NOT NULL,
 DATE_ENTERED VARCHAR2 (16 BYTE) NOT NULL);

insert into incoming_data values ('00001', 'PlaceOrder', '2009-11-05 23:05');
insert into incoming_data values ('00003', 'PlaceOrder', '2009-11-05 23:46');

insert into incoming_data values ('00007', 'PlaceOrder', '2009-11-06 00:21');
insert into incoming_data values ('00008', 'PlaceOrder', '2009-11-06 00:22');

insert into incoming_data values ('00011', 'PlaceOrder', '2009-11-06 01:21');
insert into incoming_data values ('00012', 'PlaceOrder', '2009-11-06 01:22');

insert into incoming_data values ('00021', 'PlaceOrder', '2009-11-06 03:21');
insert into incoming_data values ('00022', 'PlaceOrder', '2009-11-06 03:22');
insert into incoming_data values ('00023', 'PlaceOrder', '2009-11-06 03:23');
insert into incoming_data values ('00024', 'PlaceOrder', '2009-11-06 03:24');

insert into incoming_data values ('00035', 'PlaceOrder', '2009-11-06 04:25');

insert into incoming_data values ('00041', 'PlaceOrder', '2009-11-06 05:21');

insert into incoming_data values ('00042', 'PlaceOrder', '2009-11-06 06:22');

insert into incoming_data values ('00053', 'PlaceOrder', '2009-11-06 07:23');
insert into incoming_data values ('00054', 'PlaceOrder', '2009-11-06 07:24');

insert into incoming_data values ('00063', 'PlaceOrder', '2009-11-06 08:23');
insert into incoming_data values ('00064', 'PlaceOrder', '2009-11-06 08:24');
insert into incoming_data values ('00065', 'PlaceOrder', '2009-11-06 08:25');
insert into incoming_data values ('00066', 'PlaceOrder', '2009-11-06 08:25');
insert into incoming_data values ('00073', 'PlaceOrder', '2009-11-06 08:33');
insert into incoming_data values ('00074', 'PlaceOrder', '2009-11-06 08:34');
insert into incoming_data values ('00075', 'PlaceOrder', '2009-11-06 08:35');
insert into incoming_data values ('00077', 'PlaceOrder', '2009-11-06 08:35');

insert into incoming_data values ('00083', 'PlaceOrder', '2009-11-06 11:23');
insert into incoming_data values ('00084', 'PlaceOrder', '2009-11-06 11:24');

insert into incoming_data values ('00095', 'PlaceOrder', '2009-11-06 12:25');

insert into incoming_data values ('00103', 'PlaceOrder', '2009-11-06 14:23');
insert into incoming_data values ('00104', 'PlaceOrder', '2009-11-06 14:24');

insert into incoming_data values ('00115', 'PlaceOrder', '2009-11-06 15:25');

insert into incoming_data values ('00123', 'PlaceOrder', '2009-11-06 17:23');
insert into incoming_data values ('00124', 'PlaceOrder', '2009-11-06 17:24');

insert into incoming_data values ('00135', 'PlaceOrder', '2009-11-06 18:25');

insert into incoming_data values ('00143', 'PlaceOrder', '2009-11-06 21:23');

insert into incoming_data values ('00154', 'PlaceOrder', '2009-11-06 22:24');
insert into incoming_data values ('00155', 'PlaceOrder', '2009-11-06 22:46');

insert into incoming_data values ('00170', 'PlaceOrder', '2009-11-06 23:25');
insert into incoming_data values ('10170', 'OtherStuff', '2009-11-06 23:44');

insert into incoming_data values ('00180', 'PlaceOrder', '2009-11-07 00:05');

select count(*), substr(date_entered,1,13), hour
from incoming_data, hours
where interface_name = 'PlaceOrder'
and substr(date_entered,1,10) > '2009-11-05'
and substr(date_entered,1,10) < '2009-11-07'
and substr(date_entered(+),12,2) = hours.hour
group by substr(date_entered,1,13), hour
order by substr(date_entered,1,13) asc;

This returns me the following
2, 2009-11-06 00, 00
2, 2009-11-06 01, 01
4, 2009-11-06 03, 03
1, 2009-11-06 04, 04
1, 2009-11-06 05, 05
1, 2009-11-06 06, 06
2, 2009-11-06 07, 07
8, 2009-11-06 08, 08
2, 2009-11-06 11, 11
1, 2009-11-06 12, 12
2, 2009-11-06 14, 14
1, 2009-11-06 15, 15
2, 2009-11-06 17, 17
1, 2009-11-06 18, 18
1, 2009-11-06 21, 21
2, 2009-11-06 22, 22
1, 2009-11-06 23, 23


where as I need to return the extra rows for the missing hours as so:
2, 2009-11-06 00, 00
2, 2009-11-06 01, 01
0, 2009-11-06 02, 02
4, 2009-11-06 03, 03
1, 2009-11-06 04, 04
1, 2009-11-06 05, 05
1, 2009-11-06 06, 06
2, 2009-11-06 07, 07
8, 2009-11-06 08, 08
0, 2009-11-06 09, 09
0, 2009-11-06 10, 10
2, 2009-11-06 11, 11
1, 2009-11-06 12, 12
0, 2009-11-06 13, 13
2, 2009-11-06 14, 14
1, 2009-11-06 15, 15
0, 2009-11-06 16, 16
2, 2009-11-06 17, 17
1, 2009-11-06 18, 18
0, 2009-11-06 19, 19
0, 2009-11-06 20, 20
1, 2009-11-06 21, 21
2, 2009-11-06 22, 22
1, 2009-11-06 23, 23

I am stuck with the format of the incoming_data table, with the date being character. I have added the hours table just as a means to an end, but it may be completely the wrong way to approach this problem.
I had hoped to solve this using just a single query. In the end this query will be run from a spreadsheet using MS Query to populate a range of cells and produce a graph. The dates will parameterised, but initially I just need to be able to return a full 24 hours of data per date.
Any help would be much appreciated.

I have typed the SQL statements in by hand, so apologies if there is a syntax error in there, but I think you ge the jist.

Thanks in advance.
Re: Group by Hours - Show Missing Hours [message #430369 is a reply to message #430359] Tue, 10 November 2009 03:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The problem is that your hour table doesn't let you match to the whole date.

I'd try doing it like this:
select count(date_entered), src_dte
from   incoming_data
      ,(select to_date('2009-11-05','yyyy-mm-dd') + level/24 src_dte from dual connect by level <=48)
where  trunc(to_Date(date_entered(+),'yyyy-mm-dd hh24:mi'),'HH') = src_dte
group by src_dte
order by src_dte;
Re: Group by Hours - Show Missing Hours [message #430374 is a reply to message #430359] Tue, 10 November 2009 03:55 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
select count(interface_name), substr(nvl(date_entered,'2009-11-06 '||hour||':00'),1,13), hour 
from incoming_data, hours
where interface_name(+) = 'PlaceOrder'
and substr(date_entered(+),1,10) > '2009-11-05'
and substr(date_entered(+),1,10) < '2009-11-07'
and substr(date_entered(+),12,2) = hours.hour
group by substr(nvl(date_entered,'2009-11-06 '||hour||':00'),1,13), hour 
order by substr(nvl(date_entered,'2009-11-06 '||hour||':00'),1,13) asc

[Updated on: Tue, 10 November 2009 03:58]

Report message to a moderator

Re: Group by Hours - Show Missing Hours [message #430377 is a reply to message #430374] Tue, 10 November 2009 04:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How would you make that query work if you wanted to look at a 48 hour period?
Re: Group by Hours - Show Missing Hours [message #430396 is a reply to message #430369] Tue, 10 November 2009 05:23 Go to previous messageGo to next message
alfa_mark
Messages: 2
Registered: November 2009
Location: Markfield, England
Junior Member
Hi, thanks for the replies quys, both provided some good results.

I have not used the level command before and was experimenting with it. I had to tweak to list from 00 to 23 rather than 01 to 00 of the next day as so

select count(date_entered), src_dte
from   incoming_data
      ,(select to_date('2009-11-05','yyyy-mm-dd') + level/24 -1/24 src_dte from dual connect by level <=48)
where  trunc(to_Date(date_entered(+),'yyyy-mm-dd hh24:mi'),'HH') = src_dte
group by src_dte
order by src_dte;


What I could not do however is lists more than 50 hours worth. Is there a limit on the connect by level command?

The other solution worked well too, but will require passing the date parameter into the select part of the query as well as the and statements; that may be a challenge too!

Thanks for your help both of you.
Re: Group by Hours - Show Missing Hours [message #430405 is a reply to message #430396] Tue, 10 November 2009 05:38 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Just change the 'WHERE level <= 48' to 'WHERE level <= 100' or whatever else you need.
Previous Topic: How to return dynamic # of columns from stored procedure?
Next Topic: running total
Goto Forum:
  


Current Time: Wed Sep 28 00:37:50 CDT 2016

Total time taken to generate the page: 0.09890 seconds