Re: SQL or PL/SQL

Thanks a lot Fumi !

fumi a écrit dans le message <828pvi$lfi$3_at_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 Mon Dec 06 1999 - 14:45:36 CET