I'm trying to write a PHP script that gives a report on how many support tickets were 'live' between a certain period of time. To explain this as simply as possible, the live period starts at (say) 1st June 2010 and ends at 30th June 2010. If a support ticket was closed before the 1st June then this must be excluded from the report, and if a support ticket was opened after 30th June then this must also be excluded.
SELECT ticket_id
FROM ticket_table
WHERE client_id = '$client_id'
AND ticket_opened < '2010-06-30'
AND ticket_closed > '2010-06-01'
OR ticket_closed IS NULL
The problem I have is that the test needs to check whether the ticket_closed value is either NULL or greater than 1st June, but the way my query is structured means that it all gets evaluated wrong. In the DB, the ticket_closed value is set to the DATE type and has a default value of NULL when a new ticket is added. I realise I could make the test easier if instead of NULL I manually edited the ticket_closed field to 0000-00-00 but there are an awful lot of records in there already
SELECT ticket_id
FROM ticket_table
WHERE client_id = '$client_id'
AND ticket_opened < '2010-06-30'
AND ticket_closed > '2010-06-01'
OR client_id = '$client_id'
AND ticket_opened < '2010-06-30'
AND ticket_closed IS NULL
I expected this approach to be pretty slow (given that I'm doing the same test twice) but it didn't seem to make much difference.