Re: SQL or PL/SQL

From: <rtproffitt_at_my-deja.com>
Date: Fri, 03 Dec 1999 00:05:57 GMT
Message-ID: <8271h3$43m$1_at_nnrp1.deja.com>

Try something like this:

Select
  Decode (

     trunc( to_number( to_char(date_hour,'hh24.mi'))/6 ),
     0, to_char( date_hour - 1, 'yyyymmdd')||'PM',
      Decode (
         trunc( to_number( to_char(date_hour,'hh24.mi'))/18 ),
         0, to_char( date_hour, 'yyyymmdd')||'AM',
            to_char( date_hour, 'yyyymmdd')||'PM') ) TimeFlag,

  Severity,
  Count(alarm_id) AlarmCnt
From TableName
Group By
  Decode (

     trunc( to_number( to_char(date_hour,'hh24.mi'))/6 ),
     0, to_char( date_hour - 1, 'yyyymmdd')||'PM',
      Decode (
         trunc( to_number( to_char(date_hour,'hh24.mi'))/18 ),
         0, to_char( date_hour, 'yyyymmdd')||'AM',
            to_char( date_hour, 'yyyymmdd')||'PM') ),

  Severity
Order by 1,2

The decode looks complicated, but can be explained easily. Here are some sample times and the return result created:

199912010154	19991130PM
199912010600	19991201AM
199912010754	19991201AM
199912011800	19991201PM
199912011923	19991201PM
199912010000	19991130PM

(I used the assumption that periods 6am to 6pm means 6am up to but not including exactly 6 pm and 6 pm to 6 am means 6pm up to but not including 6am).

Here is how the DECODE works:
-> trunc( to_number( to_char(date_hour,'hh24.mi'))/6 ), convert to 24 hour time and create a number, and divide by 6 (6am). Truncate. Anything less than 6 am will be zero (midnight to 6am), -> to_char( date_hour - 1, 'yyyymmdd')||'PM', if 0, it belongs to yesterday, so create a sort key based on yesterday "PM".
If not 0, continue:
-> trunc( to_number( to_char(date_hour,'hh24.mi'))/18 ), convert again and divide 6pm (1800 hours). Anything less will be 0.
-> to_char( date_hour, 'yyyymmdd')||'AM', therefore belongs to "today AM". Create sort key. Otherwise, it is 6pm or after,
create "Today PM" sort key.
-> to_char( date_hour, 'yyyymmdd')||'PM')

Good Luck,
Robert Proffitt
Beckman Coulter
RTProffitt "AT" beckman "DOT" com

In article <825mom$3g7$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 answers
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Dec 03 1999 - 01:05:57 CET