Re: Duration Question

Dave411 wrote:
> Hmm.. If there's a class that offers this, sign me up .. I might have
> misstated it, because I'm pretty sure it's not that easy.
>
> The idea is, with just the start, end, and duration, have a report that
> would show the following:
>
> The maximum number of cars on the road, in the hour of:
> hour 00 01 02 03 04 05 06 07 (AM) ......
> ------------------------------------------------
> num cars 10 12 15 3 0 11 19 31 .......
>
> So the idea is to have the report generate a 24 hour snapshot of the
> max numbers - each hour. As I write this I realize that the original
> question wasn't clear about the layout , but this is my goal. Is it
> still something that I could use count(*), between, group by? Do I
> need to do some kind of self join? The table is very large (millions
> of rows), so I'm also concerned about performance.
>
>
>
> DA Morgan wrote:

>> dave.howland_at_gmail.com wrote:
>>> Okay, I guess I could have been more specific - how would one go about
>>> doing it?
>>> It seems like it would be one of those SQL tricks.
>>> Michel Cadot wrote:
>>>> <dave.howland_at_gmail.com> a écrit dans le message de news: 1165033982.461507.272230_at_80g2000cwy.googlegroups.com...
>>>> | Hello everyone:
>>>> |
>>>> | I have a db table that contains three pieces of information ( let's say
>>>> | I was tracking car trips), a start time(date), an end time(date) and a
>>>> | duration(int)  What I want to know (two things) (a) in every hour of
>>>> | every day, is there a way that I could tell the maximum number of
>>>> | concurrent cars on the road? (b) what is the total number of cars on
>>>> | the road in each hour?
>>>> |
>>>> | Can this be done is SQL?
>>>> |
>>>>
>>>> Yes.
>>>>
>>>> Regards
>>>> Michel Cadot
>> There are many solutions and this question looks and feels like
>> homework or a class final.
>>
>> One way would be to look at each time at which you wish to
>> check and do a count using BETWEEN.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damorgan_at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org

And real reporting software isn't available? And this must be in SQL: Not PL/SQL?
And it doesn't sound like school work to you?

Break down what you need to do.
1. Find the count by hour
2. Cross-tabulate the result into separate columns

The first part I answered.
The second can be done using DECODE.

• look at the crosstab demos at: http://www.psoug.org/reference/decode_case.html -- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org