Re: SQL or PL/SQL

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: 3 Dec 1999 16:09:22 GMT
Message-ID: <828pvi$lfi$3@news.seed.net.tw>

<vigi98_at_my-deja.com> wrote in message news: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

SQL> select to_char(datehour, 'YYYY/MM/DD')||

  2      decode(to_char(datehour, 'HH24'), '06', ' Day', ' Night'),
  3      sum(decode(severity, 1, cnt, 0)) as severity1,
  4      sum(decode(severity, 2, cnt, 0)) as severity2,
  5      sum(decode(severity, 3, cnt, 0)) as severity3
  6    from (
  7      select floor((datehour-to_date('06', 'hh'))*2)/2+
  8          to_date('06', 'hh') as datehour,
  9          severity, count(*) as cnt
 10        from test
 11        group by floor((datehour-to_date('06', 'hh'))*2), severity)

 12 group by datehour;

TO_CHAR(DATEHOUR SEVERITY1 SEVERITY2 SEVERITY3

---------------- --------- --------- ---------
1999/11/30 Night        29        29        29
1999/12/01 Day          67        67        67
1999/12/01 Night        67        68        67
1999/12/02 Day          67        67        67
1999/12/02 Night        68        67        67
1999/12/03 Day          35        36        36

6 rows selected.

SQL> Received on Fri Dec 03 1999 - 10:09:22 CST