Re: SQL or PL/SQL

From: <rtproffitt_at_my-deja.com>
Date: Tue, 07 Dec 1999 17:38:53 GMT
Message-ID: <82jgnd$isj$1@nnrp1.deja.com>

Vigi
Here is my approach also:

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 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

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Dec 07 1999 - 11:38:53 CST