Re: SQL or PL/SQL

In article <825ltc$2r5$1_at_nnrp1.deja.com>,   vigi98_at_my-deja.com wrote:
> Hi all,
>
> I'm not very good at doing SQL requests or PL/SQL scripts. Could you
> solve the following problem :
>
> I've got three fields in a table : alarm_id,date-hour and severity.
> Severity is an integer between 1 and 3.
>
> What I would like to obtain is what follows : for each period of 12h
> (from 6AM to 6PM, then from 6PM to 6AM) I would have the number of
> alarms per severity number.
>
> Typically, it would give a table with the following fields :
>
> date of the first part of the 12h period ; flag to indicate if the
hour
> of the alarm is between 6AM and 6PM or not ; number of alarms of
> severity 1 ; number of alarms of severity 2 ; number of alarms of
> severity 3
>
> Thank you very much for your responses.
>

I doing this off the cuff, but:

select count(*), date-hour, severity
from my_alarm_table
group by date-hour, severity;

will get you the number of alarms at a given date-hour with a given severity.

Now, if I understand correctly, you want to split date-hours into two periods of 6:00-18:00 and 18:00-6:00. So you need a function f that maps a date d into one of d1 and d2, where d1 is d (6:00-18:00), and d2 is d (18:00-06:00). NOTE: This function must be in a package in order to be used in an SQL statement.

PRAGMA RESTRICT_REFERENCES (f, WNDS, WNPS); create or replace function f (
date_in in date)
return date
is

begin

if date_in < to_date(to_char(date_in, 'YYYYMMDD') || ' 06:00:00', 'YYYYMMDD HH24:MI:SS') then

    dbms_output.put_line(to_char(date_in - 1, 'YYYYMMDD') || ' 18:00-06:00');
elsif date_in < to_date(to_char(date_in, 'YYYYMMDD') || ' 18:00:00', 'YYYYMMDD HH24:MI:SS') then

    dbms_output.put_line(to_char(date_in, 'YYYYMMDD') || ' 06:00-18:00');
elsif date_in < to_date(to_char(date_in + 1, 'YYYYMMDD') || ' 00:00:00', 'YYYYMMDD HH24:MI:SS') then

    dbms_output.put_line(to_char(date_in, 'YYYYMMDD') || ' 18:00-06:00');
end if;

end;

Then you can say:

select count(*), f(date-hour), severity from my_alarm_table
group by f(date-hour), severity;

Salaam Yitbarek

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Dec 02 1999 - 10:26:23 CST