Re: Duration Question

"Dave411" <dave.howland_at_gmail.com> a écrit dans le message de news: 1165118095.839264.240350_at_73g2000cwn.googlegroups.com... 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.

If a car you have a trip from 1:00 to 1:30 and for another one a trip from 1:35 to 1:50, do you have 1 or 2 cars for 01 slot?

About your last sentence. Developing an application for all rdbms is an illusion and SQL does not even guarantee this as each rdbms has its extensions especially for performances purpose. And above all, your application architecture depends on your rdbms as each one has its own locking and concurrency mechanism and satisfies different isolation level and so on.

Regards
Michel Cadot Received on Sun Dec 03 2006 - 00:44:00 CST