Hi there,
I've been trying around for some time now and i just don't see a solution to
my problem:
I have a table called "feiertage" ("Holidays"):;
mysql> select * from feiertage;explain feiertage;
+------------+
| datum |
+------------+
| 2001-12-24 |
| 2001-12-25 |
| 2001-12-26 |
| 2002-01-01 |
+------------+
4 rows in set (0.81 sec)
+-------+------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+------------+-------+
| datum | date | | PRI | 0000-00-00 | |
+-------+------+------+-----+------------+-------+
1 row in set (0.81 sec)
now i want to know the last date, that is not listed in that table and that
is not on a weekend and is earlier or the same than a given date:
assume 2001-12-29 is the given date, then i tried around with a loop in perl
increasing the INTERVAL.
select DATE_SUB('2001-12-29', INTERVAL 0 DAY) as tag from feiertage where
datum = DATE_SUB('2001-12-29', INTERVAL 0 DAY) or
WEEKDAY(DATE_SUB('2001-12-29', INTERVAL 0 DAY))>4;
tells me that this date is on a weekend or a holiday. that doesn't rellay
help me, what i need is a query that gives me "2001-12-28" (which is the
friday). i played around for 2 hours now but have no idea how to make it work with
just one query. I could think of ways to do it with perl and loops and stuff
like that, increasing the INTERVAL until i get zero rows which would mean i
stepped backwards enough days, but then i would need another query to
actually get the date in the right form "2001-12-28" which i need. But i have still
hope there is a way without the overhead of loops and stuff...
has anybody some suggestion?
thanks a lot for any hints
Richard
--
GMX - Die Kommunikationsplattform im Internet.
http://www.gmx.net
---------------------------------------------------------------------
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
