Modifying an SQL query to exclude particular dates

Question

Hey everyone,

I have a pretty long SQL query that I use to, for all of the users in a particular "region", for a given month, calculate the number of days that they have been on "duty". This is broken down into weekends and weekdays. Here is the php/SQL statement:

 $result = mysql_query("

 SELECT fname, lname, MONTH( eventDate ) AS MONTH , 
    SUM( IF( WEEKDAY( eventDate ) <=2, 1, 0 ) 
       OR IF( WEEKDAY( eventDate ) = 6, 1, 0 )) AS WeekdayCount, 
    SUM( IF( WEEKDAY( eventDate ) = 4, 1, 0 ) 
       OR IF( WEEKDAY( eventDate ) = 5, 1, 0 ) 
       OR IF( WEEKDAY( eventDate ) = 3, 1, 0 )) AS WeekendCount, 
    SUM( IF( secondary =0, 1, 0 ) ) AS `Primary`, 
    SUM( IF( secondary =1, 1, 0 ) ) AS `Secondary` 
 FROM eventcal AS e 
 LEFT JOIN users AS u ON e.primary = u.username 
 WHERE e.region = '$region' AND MONTH( eventDate ) = '$month' 
 GROUP BY fname, lname, MONTH( eventDate ) 
 ORDER BY lname

");

Unfortunately, for different regions, different days of the week are considered as weekends and weekdays - as you can see above. So Sunday may not be considered a weekend. But that is not important here.

My problem is, I want to say: "Do not include in the count any days from Oct 10, 2009 - Oct 13, 2009.

Does anyone know how I can modify this SQL to say that? Or is there a little PHP script I can write?

Thanks!

Answer 1

You can add in the where clause (just before the 'GROUP BY')

AND eventDate NOT BETWEEN '2009-10-10' AND '2009-10-13'

NOTE: the between statement can function differently depending on the database - it can be inclusive or exclusive, ie it may include or exclude results that fall exactly on the start or end of the range, so alternatively:

AND (eventDate<'2009-10-10' OR eventDate>'2009-10-13')

Also note, including a date in a plain text sql query is handled differently on different databases, so read your database's documentation. the above will work for postgres.