Re: SQL count by time intervals

From: HansF <News.Hans_at_telus.net>
Date: Wed, 09 Nov 2005 22:55:19 GMT
Message-Id: <pan.2005.11.09.22.55.18.538611@telus.net>

On Wed, 09 Nov 2005 14:19:26 -0800, Schroeder wrote:

>
> Yes Hans, that's a possibility.
>
> I'm using Oracle 8i.
 

Thought so. It had the faint feeling of an obsolete approach. <g>

You DO realize that 8i is not currently supported (except under pain of really high extra support charges), right?

> By the way all, my initial approach was borrowed from a SQLServer group
> which offered the following example which counts the number of times
> different people worked in each of three shifts:

Ah, yes ... yet another proof point that Oracle is not SQL Server.

Sound like you may be doing some conversion. Have you had a chance to read Tom Kyte's books?

I like Michael's approach. Very elegant. I'd probably have started with an inline view that created three groups - using pseudocode something like

SELECT
  FROM (Select 'First' shift, cols

          from MyTable
         where date_time_rec-trunc(date_time_rec) 
               between fst_shift_start and fst_shift_end
        union
        Select 'Second' shift, cols
          from MyTable
         where date_time_rec-trunc(date_time_rec) 
               between snd_shift_start and snd_shift_end
        union
        Select 'Third' shift, cols
          from MyTable
         where date_time_rec-trunc(date_time_rec) 
               between thd_shift_start and thd_shift_end
       ) ...


-- 
Hans Forbrich                           

Canada-wide Oracle training and consulting mailto: Fuzzy.GreyBeard_at_gmail.com
*** Top posting relies guarantees I won't respond. *** Received on Wed Nov 09 2005 - 16:55:19 CST