Re: SQL or PL/SQL

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Thu, 2 Dec 1999 15:04:25 +0100
Message-ID: <825u6e$8d7n$1@oceanite.cybercable.fr>

Try this:
create table alarm (alarm_id number, timestamp date, severity number);

insert into alarm values(1, trunc(sysdate), 2);
insert into alarm values(2, trunc(sysdate)+1/24, 1);
insert into alarm values(3, trunc(sysdate)+2/24, 1);
insert into alarm values(4, trunc(sysdate)+3/24, 2);
insert into alarm values(5, trunc(sysdate)+4/24, 3);
insert into alarm values(6, trunc(sysdate)+5/24, 2);

insert into alarm values(7, trunc(sysdate)+6/24, 1);
insert into alarm values(8, trunc(sysdate)+7/24, 1);
insert into alarm values(9, trunc(sysdate)+8/24, 2);
insert into alarm values(10, trunc(sysdate)+9/24, 2);
insert into alarm values(11, trunc(sysdate)+10/24, 1);
insert into alarm values(12, trunc(sysdate)+11/24, 3);
insert into alarm values(13, trunc(sysdate)+12/24, 3);
insert into alarm values(14, trunc(sysdate)+13/24, 2);
insert into alarm values(15, trunc(sysdate)+14/24, 2);
insert into alarm values(16, trunc(sysdate)+15/24, 1);

insert into alarm values(17, trunc(sysdate)+16/24, 1); insert into alarm values(18, trunc(sysdate)+17/24, 2);

insert into alarm values(19, trunc(sysdate)+18/24, 2);
insert into alarm values(20, trunc(sysdate)+19/24, 3);
insert into alarm values(21, trunc(sysdate)+20/24, 3);
insert into alarm values(22, trunc(sysdate)+21/24, 1);
insert into alarm values(23, trunc(sysdate)+22/24, 1);
insert into alarm values(24, trunc(sysdate)+23/24, 1);

commit;

v734> select substr(to_char(trunc(timestamp-21599/86400), 'DD-MON-YYYY'),1,11) "Date",
  2 decode(trunc((to_number(to_char(timestamp,'SSSSS'))-21599+   3
decode(sign(to_number(to_char(timestamp,'SSSSS'))-21599),

  4                              -1, 86400, 0))/43200),
  5                0, 'Yes', 'No') "Between",
  6         sum(decode(severity,1,1,0)) "Sev 1",
  7         sum(decode(severity,2,1,0)) "Sev 2",
  8         sum(decode(severity,3,1,0)) "Sev 3"

  9 from alarm
 10 group by trunc(timestamp-21599/86400),

 11           trunc((to_number(to_char(timestamp,'SSSSS'))-21599+
 12                  decode(sign(to_number(to_char(timestamp,'SSSSS'))-21599),
 13                         -1, 86400, 0))/43200)

 14 /

Date Bet Sev 1 Sev 2 Sev 3

----------- --- ---------- ---------- ----------
01-DEC-1999 No           2          3          1
02-DEC-1999 Yes          5          5          2
02-DEC-1999 No           3          1          2

3 rows selected.

86400 is the number of seconds in a day and 21599 is 5h59mn59s.

--
Have a nice day
Michel

<vigi98_at_my-deja.com> a écrit dans le message : 825mku$37j$1_at_nnrp1.deja.com...
> 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 answers
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Dec 02 1999 - 08:04:25 CST