As with any complex SQL query, the secret is to break it up into parts, preferably independently testable parts.
The first problem is to establish the count of the number of tickets each machine had during the period in question. What is the criterion here? Probably, if a machine had a problem that started in November 2011 and extended into December 2011, that should be counted; likewise, if a problem was started in January 2012 but completed in February 2012, that should be counted. So, we need:
SELECT machine_name, COUNT(*) AS num_tickets
FROM tickets
WHERE date_completed >= '01-Dec-2011' AND date_submitted <= '31-Jan-2012'
GROUP BY machine_name;
If you decide it is only the dates when the tickets were submitted that count, adjust the criterion to reference date_submitted
twice; likewise, if it is only the dates when the tickets were completed that count, then reference date_completed
twice. Note that if a machine had a ticket that was started in November and not resolved until February, the query above will count it; if you use either of the alternatives, that machine had no problem during the period in question.
That tells us how many tickets were open for the machine during the time period. Now we need to find which number of tickets is the maximum number:
SELECT MAX(num_tickets) AS max_tickets
FROM (SELECT machine_name, COUNT(*) AS num_tickets
FROM tickets
WHERE date_completed >= '01-Dec-2011' AND date_submitted <= '31-Jan-2012'
GROUP BY machine_name
);
Now we need to select the machine name(s) that had this number of tickets:
SELECT machine_name
FROM (SELECT MAX(num_tickets) AS max_tickets
FROM (SELECT machine_name, COUNT(*) AS num_tickets
FROM tickets
WHERE date_completed >= '01-Dec-2011' AND date_submitted <= '31-Jan-2012'
GROUP BY machine_name
)
) AS n
JOIN (SELECT machine_name, COUNT(*) AS num_tickets
FROM tickets
WHERE date_completed >= '01-Dec-2011' AND date_submitted <= '31-Jan-2012'
GROUP BY machine_name
) AS m
ON n.max_tickets = m.num_tickets;
Assuming Oracle supports the WITH clause, this can be simplified (considerably):
WITH t AS
(SELECT machine_name, COUNT(*) AS num_tickets
FROM tickets
WHERE date_completed >= '01-Dec-2011' AND date_submitted <= '31-Jan-2012'
GROUP BY machine_name
)
SELECT t.machine_name
FROM t
JOIN (SELECT MAX(num_tickets) AS max_tickets FROM t) AS m
ON t.num_tickets = m.max_tickets;
Caveat: I've used 'AS alias' on the sub-queries, as supported by the SQL Standard. I believe Oracle does not allow 'AS alias' and requires just 'alias' after table names; I'm not sure whether that also applies to names for sub-queries. If the 'AS m' and 'AS n' notations cause trouble, try dropping the AS. You might find a similar issue with the column renamings 'AS num_tickets' etc, but I believe Oracle does allow AS in that context.
Presumably, this is just one of a series of questions since the answer doesn't seem to require any of the tables except the Tickets table. Presumably, other questions require the use of other tables.