| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Duration Question
Okay, so I'm looking for a count. Count what?
I have start_time, end_time, and duration.
So if a car trip starts at 7:01 and ends at 7:45, it's easy to count
that because the start and/or end time actually contain the value
7:xx. So my filter could look like 'where start_time between 07:00 and
07:59', but that presents me with a problem.
What if the start_time was 5:00 and the end time was 8:00? 7:00 is
nowhere in there. How can I do where start_time 'contains' 7:00??
Any car that starts a job should be counted in each hour that it was running, if it ran from 8am to 10:20 am, it should be counted in the 8,9,10 timeslot. It's easy to count the starts:
select count(*), to_char(start_time,hh24)
from table
group by to_char(start_time,hh24)
But this absolutely does not give you representation of those cases when the jobs span across multiple hours.
This can't be done in pl/sql, in case of other db types. If possible, in SQL is the way to go.
DA Morgan wrote:
> 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
>
>
>
 |
 |