Here's one way ...
declare
v_start date;
v_end date;
v_increment number(4) := 90; -- in minutes
v_cnt number(10);
begin
v_start := trunc ( sysdate );
loop
-- Since "between" in inclusive, subtract one second
v_end := v_start + (v_increment/1440) - 1/86400;
select count(*)
into v_cnt
from messages_table
where time_tag between v_start and v_end;
dbms_output.put_line (
rpad ( to_char ( v_start ,'HH24:MI:SS' ) ,15 )
|| rpad ( to_char ( v_end ,'HH24:MI:SS' ) ,15 )
|| to_char ( v_cnt ,'99999' )
);
v_start := v_start + (v_increment/1440);
exit when v_start >= trunc ( sysdate ) + 1;
end loop;
end;
/
Jon Knight
Senior Database Analyst
2525 Horizon Lake Drive, Suite 120
Memphis, TN 38133
[Email address protected]
901.371.8000 - Phone
800.238.7675 - Phone
901.380.8336 - Fax
www.FirstData.com
First Data's merger with Concord creates "One Company" with enhanced choice,
voice and innovation for all customers.
-----Original Message-----
From: darryl
[mailto:oracledba-ezmlmshield-x73499135.[Email address protected]
Sent: Friday, December 03, 2004 5:28 PM
To: LazyDBA Discussion
Subject: Need some PL/SQL help
Hi All:
I need some assistance with writing a PL/SQL script.
I need to create a script that would out put the following format.
Basically I need to set a time range and count the total number of messages
within that range. The message are time tagged.
Actually it needs to be flexible in that any a user can it enter any range
as
input. Such as 5 minute increments or 10 increments. But it must be for the
whole day.
0000-2359.
Any help would be appreciated. At this point I am stuck.
Here is a printout of the final product.
Begin Time End Time # of Messages
0000 0014 12
0015 0029 9
0030 0044 21
0045 0059 22
0100 0114 23
0115 0129 8
0130 0144 7
0145 0159 4
0200 0214 2
....
....
....
2330 2344 22
2345 2359 11
thanks
dlj
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page