helpdesk

customer
techs
operator
ticket
tickethist
product

1. Which products (names, model numbers) have the most 
`problems' (ie: tickets). (extra: make a list of top 10).

select top 10 p.name,p.modelno,count(t.ticketid) as ticketcount
from ticket t,product p
where t.productid = p.productid
group by p.name,p.modelno
order by count(t.ticketid) desc,p.name,p.modelno

(changed by access)
SELECT TOP 10 p.name, p.modelno, count(t.ticketid) AS ticketcount
FROM ticket AS t, product AS p
WHERE t.productid=p.productid
GROUP BY p.name, p.modelno
ORDER BY count(t.ticketid) DESC , p.name, p.modelno;

2. Who are the most productive techs?

SELECT TOP 10 te.name, count(t.ticketid) AS ticketcount
FROM ticket AS t, tech te
WHERE t.techid=te.techid and t.closedate is not null
GROUP BY te.name 
ORDER BY count(t.ticketid) DESC , te.name


3. Average turnaround time for a ticket.

select avg(closedate - createdate) 
from ticket 
where closedate is not null

4. Highest category of tickets this months.

select category,count(*) as ticketcnt
from ticket
where DateDiff('D',createdate,NOW()) < 30
group by category
order by count(*) desc,category


5. which cities have most tickets with highest priority.

select c.city,count(t.ticketid) as cnt
from ticket t,customer c
where t.priority='HIGH' and
      t.customerid = c.customerid
group by c.city
order by count(t.ticketid),c.city