Re: SQL or PL/SQL

En fait mes dates sont en secondes depuis le 1/1/70. Pourriez-vous adapter la requête en fonction de cette nouvelle donnée. J'ai essayé, mais pour l'instant les résultats ne sont pas très probants.

Merci d'avance.

Michel Cadot a écrit dans le message
<825uil$8phf$1_at_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 :
825ltc$2r5$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 responses.
>>
>>
>> Sent via Deja.com http://www.deja.com/
>> Before you buy.
>
>
Received on Fri Dec 03 1999 - 00:58:37 CST