| Finding date processing a tad difficult [message #1781] |
Wed, 29 May 2002 11:12  |
Gus
Messages: 18
Registered: December 2000
|
Junior Member |
|
|
Hi
heres the problem:
simple table
date_in date_out vehicle_reg
----------- ------------ -----------------
01/01/2002 12:31 01/01/2002 13:31 ND1111
01/01/2002 16:31 01/01/2002 18:31 ND1111
01/01/2002 22:31 01/01/2002 23:31 ND1112
02/01/2002 07:31 02/01/2002 08:31 ND1112
I need a qry that returns the vehicle registrations
of vehicles that have come in and left and then come
in again under a certain time period. If you look
at the row with vehicle_reg ND1111, it came in and
left, and then came in again 3 hours later. Thats
the results I need. Any help would be MUCH appreciated!
chow
|
|
|
|
| Re: Finding date processing a tad difficult [message #1783 is a reply to message #1781] |
Wed, 29 May 2002 11:47  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Gus, I would suggest something like this as a starting point. This evaluates every row looking for entries with both a date_in and date_out, and then uses a correlated subquery to see if there is another row out there with the same vehicle_reg, with a greater date_in (so we don't look at the row we're currently evaluating), and where the difference between this out and the next in is less than 2 days (arbitrary number for this example). I used a DISTINCT clause in case the same reg ever had more than two rows and it came back in under the threshold more than once.
select distinct vehicle_reg
from reg r1
where date_in is not null
and date_out is not null
and exists (select null
from reg r2
where r2.vehicle_reg = r1.vehicle_reg
and r2.date_in > r1.date_in
and (r2.date_in - r1.date_out) < 2); |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
