0

I have a series of tables:

  • TECH PERSONNEL (pplSoft, fname, lname, pittID, expertise, office phone) where fname is first name, and lname is last name.

  • USERS (pplSoft, fname, lname, pittID, office phone)

  • CATEGORIES (category id, category, description) where this table lists all possible categories of submitted tickets.

  • INVENTORY(machine name, IP, network port, MACADDR, location id)

  • LOCATIONS(location id, location, building, notes)

  • TICKETS (ticket number, owner pplSoft, date submitted, date closed, days worked on, category id, machine name, location, description)

  • ASSIGNMENT (ticket number, tech pplSoft, date assigned, status) where status held is an enumeration, could be: assigned, in progress, delegated, closed successful, or closed unsuccessful.

My task is to list the Device Name all names of the machines that had the maximum number of problems in the two months of December 2011 and January 2012.

I have to turn this into SQL.

Can I do something like this?

select machine_name 
from tickets 
where date_submitted >= '01-DEC-2012' and 'date_submitted <= '31-JAN-2012'  

But I need to count the tickets or use max?

How do I make progress from here?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
CDev33
  • 1
  • 1
  • 2
  • Can you give an example of input and output to clarify what you want to do? – Albin Sunnanbo Feb 12 '12 at 21:10
  • i want to find and list all of the names of the devices (machines) that had the maximum number of problems within the months of dec and january – CDev33 Feb 12 '12 at 21:12
  • Since none of the columns in any of the tables is 'device name', the question as stated is unanswerable. Elegant variation is best left to English classes; in computing, rigidly calling an object by a single name helps everyone - including you. Presumably, the issue is _Which machine names had the most problems in December 2011 and January 2012 based on the count of the number of tickets opened for the machine_? Is that about right? – Jonathan Leffler Feb 12 '12 at 21:17

4 Answers4

3

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.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
2

You need to use group by.

select machine_name, count(*) as numMachines
from tickets
where date_submitted >= '01-DEC-2011' and 'date_submitted <= '31-JAN-2012'
group by machine_name
order by numMachines desc
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Albin Sunnanbo
  • 46,430
  • 8
  • 69
  • 108
  • Thank you so much... but will that take the maximum number of problems? (where i guess you count the number of tickets submitted per machine right? and list each machine that has the most?) – CDev33 Feb 12 '12 at 21:27
1
select machine_name, count(machine_name) as totalTicketCount
from tickets 
where date_submitted >= '01-DEC-2012' and 'date_submitted <= '31-JAN-2012' 
Group By machine_name
Order by totalTicketCount DESC 
Maarten Kesselaers
  • 1,141
  • 2
  • 8
  • 19
1

Your query will return you one row for each problem. Your first step is to group the result by machine so you get one row for each machine. You can then add a count column that shows you how many problems there were for that machine.

To find the maximum number of problems you need to put your query into a subselect so that you can extract the maximum. You can then use this as a subselect in a having clause to return the machines that have that maximum count.

SELECT machine_name, COUNT(machine_name) AS ticket_count
  FROM tickets
  WHERE date_submitted >= '01-DEC-2012' AND date_submitted <= '31-JAN-2012'
  GROUP BY machine_name
  HAVING ticket_count = (
    SELECT MAX(ticket_count) FROM (
      SELECT COUNT(machine_name) AS ticket_count
        FROM tickets
        WHERE date_submitted >= '01-DEC-2012' AND date_submitted <= '31-JAN-2012'
        GROUP BY machine_name
    )
  )
Neil
  • 54,642
  • 8
  • 60
  • 72