Re: SQL: Duration Question

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 4 Dec 2006 13:43:17 -0700
Message-ID: <45749675$1@news.victoria.tc.ca>

dave.howland_at_gmail.com wrote:
: 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?

Why record the start, end, _and_ duration? Surely the duration is simply end-start (no?).

Anyway, my first thoughts would be

• make a view that lists every trip, and for each of those trips select the number of trips that over lap in time. Only trips that overlap can be separate cars

pseudo code, not carefully thought out

view OVERLAPS
select

	t1.trip_id, 
	t1.date_to_record_trip_against,
	( select count(*) from trips t2
	  where t1.start < t2.end
	  and              t2.start < t1.end
	) COUNT_OF_OVERLAPS

from trips t1;

• now look for the max overlaps in the desired period.

select max(COUNT_OF_OVERLAPS)
from OVERLAPS
where date_to_record_trip_against between start_of_hr and end_of_hr

Expand that to do a group by based on hourly increments, perhaps group by truncate of the dates to the hour level.

$0.10 - No claim it's best, or even works. Received on Mon Dec 04 2006 - 14:43:17 CST