return a list of dates when nothing happens

  • Thread starter Thread starter news.shaw.net
  • Start date Start date
N

news.shaw.net

I have a database of events booked at three halls.

I'm trying to design a query that will return a list of Saturdays when
each hall is NOT booked.

What criteria should I use for the Date?

Thanks for your help....
 
news.shaw.net said:
I have a database of events booked at three halls.

I'm trying to design a query that will return a list of Saturdays when
each hall is NOT booked.

What criteria should I use for the Date?
Click to expand...

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

Usually, you set up a calendar table of all Saturday dates in the range
you are interested in. Then compare the booking table against the
calendar table. Sorta like this:

CREATE TABLE calendar (saturday_date DATETIME NOT NULL);

[Just example dates - not actual Saturday dates]

INSERT INTO calendar VALUES (#1/1/2006#);
INSERT INTO calendar VALUES (#1/8/2006#);
INSERT INTO calendar VALUES (#1/15/2006#);
.... etc. ...

Then the query looks like this:

SELECT c.saturday_date
FROM calendar As c LEFT JOIN booking as b
ON c.saturday_date = b.book_date
WHERE b.book_date IS NULL

The LEFT JOIN combined w/ the WHERE clause produces all dates in the
Calendar table (Saturday dates) that are not in the Booking table.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBRKVkDYechKqOuFEgEQJhHwCeL4sPX10N2UHwt3bVXzgx7QaY1EIAoI2H
3IX2DySoQy8l5XNsCwb4IpQu
=cX7d
-----END PGP SIGNATURE-----
 
Thank you so much.

You just saved me hours of reading.

Lester
news.shaw.net said:
I have a database of events booked at three halls.

I'm trying to design a query that will return a list of Saturdays when
each hall is NOT booked.

What criteria should I use for the Date?
Click to expand...


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

Usually, you set up a calendar table of all Saturday dates in the range
you are interested in. Then compare the booking table against the
calendar table. Sorta like this:

CREATE TABLE calendar (saturday_date DATETIME NOT NULL);

[Just example dates - not actual Saturday dates]

INSERT INTO calendar VALUES (#1/1/2006#);
INSERT INTO calendar VALUES (#1/8/2006#);
INSERT INTO calendar VALUES (#1/15/2006#);
... etc. ...

Then the query looks like this:

SELECT c.saturday_date
FROM calendar As c LEFT JOIN booking as b
ON c.saturday_date = b.book_date
WHERE b.book_date IS NULL

The LEFT JOIN combined w/ the WHERE clause produces all dates in the
Calendar table (Saturday dates) that are not in the Booking table.
Click to expand...
 
Back
Top