Finding exceptions

[ngan]

I have a table called tblTrips that holds all the data about trips our
clients takes. The table would have TripID, ClientID, PUDateTime, PUAddress,
etc...

I have another table called tblSuspensions that hods data about suspension
history of clients. The table would have SuspensionID, ClientID,
SuspStartDate, SuspEndDate, etc...

I need to do a query that shows any trips the clients have during their
suspension. They should not be taking any trips during their suspensions.
We do not want to pay for those trips either.

For instance, I can have 80 clients who were suspended at some point during
the month of January. I want to find all the trips that were booked for
those clients during their suspensions. Here are some example:

Client 12345 was suspended from 1/4/06 to 1/14/06. Client 56789 was
suspended from 1/20/06 to 1/30/06. Client 98765 was suspended from 1/10/06
to 2/10/06.

I want to find all the trips that client 12345 had between his suspension
range (1/4-1/14). I also want to find all the trips that client 56789 had
between his suspension (1/20-1/30). and so on.

I think this requires more than one query, I just don't know where to start.

Any help will be appreciated! Thanks!

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What column in tblTrips holds the date the travel took place? Is it
PUDateTime? If so, you'd have a query that may look like this:

SELECT T.ClientID, Count(TripID) As UnauthorizedTrips
FROM tblTrips As T INNER JOIN tblSuspensions As S
ON T.ClientID = S.ClientID
WHERE T.PUDateTime BETWEEN S.SuspStartDate and S.SuspEndDate

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+J96IechKqOuFEgEQKs1gCg0xcWZxrEwUG/1Z2WcABAYsUhAi4An3aB
yqEGQ+gmac/38hjxJ9R+mQzr
=gtBN
-----END PGP SIGNATURE-----

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What column in tblTrips holds the date the travel took place? Is it
PUDateTime? If so, you'd have a query that may look like this:

SELECT T.ClientID, Count(TripID) As UnauthorizedTrips
FROM tblTrips As T INNER JOIN tblSuspensions As S
ON T.ClientID = S.ClientID
WHERE T.PUDateTime BETWEEN S.SuspStartDate and S.SuspEndDate

As usual I forgot the GROUP BY clause:

GROUP BY T.ClientID