MySQL Forums
Forum List  »  Newbie

Query results from one table against a list created by another
Posted by: Scott Moore
Date: March 01, 2011 03:22PM

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.

Options: ReplyQuote


Subject
Written By
Posted
Query results from one table against a list created by another
March 01, 2011 03:22PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.