Hi all, just subscribed. Normally I work these out for myself, but time
and frustration are winning.
I have two tables:
SCHEDULE which contains a column called "date" and in that are DATETIME
entries from 2001-09-14 to who cares.
NONAVAIALBLE which contains users and dates they aren't available in a
column called 'nonavaildate'
I needed to create 'schedule' because I can't find an easy way to generate
a list of dates 200 days long without going to external scripts, which is
fine on my unix systems, but I have windows systems too. (sigh) It also
means I've got an indexed reference point that I can use for other things
anyway.
What I need to do is list dates in ranges, say for example between
2001-11-01 and 2001-11-10 that have no comparable entries in nonavailable.
I've tried all kinds of things and given up to ask the wider audience out
there! Any ideas welcome, but it must be done within SQL, preferably as
generic as possible as I have that mixed platform issue.
My last attempt was:
SELECT date
FROM schedule, nonavailable
WHERE registrationid=30 #don't worry about that
AND date >= "2001-11-01" and date <= "2001-11-10"
AND nonavaildate >= "2001-11-01" and nonavaildate <= "2001-11-10"
AND date <> nonavailadate
GROUP BY date
ORDER BY date
It unfortunately dumps a list of dates from 1st to 10th :)
If I: SELECT count(date), date
I get a result of dates but the count varies according to which dates are
not in table2 (nonavailable).
Any suggestions, hints, ideas. The simpler the better.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
