Query results from one table against a list created by another
Essentially, here's what I'm trying to do:
I have a query that tells me: ticket.tn, ticket.create_time, ticket.change_time
Basically, the date a ticket was opened and the date is was closed. I can easily get the difference between the two times a number of ways, but I need to be able to cross-reference these closed tickets against business days only.
I've added a table called holidays that has: dayofweek (as a number 1-7), date, isholiday (either 1 or 0)
Referencing a SINGLE ticket, I can get it to count how many days it was open based on the holidays table, however I cannot for the LIFE of me figure out how to get it to show ALL tickets that were closed in the range I describe AND the count of business days they were open.
Any ideas? You'll find all the pertinent info below.....
Scott
Single ticket:
mysql> select tn, create_time, change_time from ticket
-> where tn = 2011022110000659;
+------------------+---------------------+---------------------+
| tn | create_time | change_time |
+------------------+---------------------+---------------------+
| 2011022110000659 | 2011-02-21 18:10:06 | 2011-02-25 09:24:14 |
+------------------+---------------------+---------------------+
Single ticket against holidays table:
mysql> select ticket.tn, count(*) from ticket, holidays
-> where tn = 2011022110000659
-> and holidays.date between date(ticket.create_time) and date(ticket.change
_time)
-> and day not in (1,7)
-> and isholiday = 0;
+------------------+----------+
| tn | count(*) |
+------------------+----------+
| 2011022110000659 | 5 |
+------------------+----------+
When I try to find all tickets within a date range and their respective working days:
mysql> select ticket.tn, count(*) from ticket, holidays
-> where date(ticket.change_time) between '2011-2-1' and '2011-2-8'
-> and ticket.ticket_state_id = 2
-> and holidays.date between date(ticket.create_time) and date(ticket.change
_time)
-> and day not in (1,7)
-> and isholiday = 0;
+------------------+----------+
| tn | count(*) |
+------------------+----------+
| 2010122910000494 | 2016 |
+------------------+----------+
1 row in set (1.80 sec)
Here's the actual number of tickets that is SHOULD find:
mysql> select count(*) from ticket
-> where date(change_time) between '2011-2-1' and '2011-2-8'
-> and ticket_state_id = 2;
+----------+
| count(*) |
+----------+
| 977 |
+----------+
1 row in set (0.03 sec)
Edited 1 time(s). Last edit at 03/01/2011 03:28PM by Scott Moore.