The following SQL written specifically for MySQL.

====== Tables ======
  * gradhelp_attachment
  * gradhelp_customer
  * gradhelp_groups
  * gradhelp_list
  * gradhelp_options
  * gradhelp_privatemessage
  * gradhelp_responsetime
  * gradhelp_r_attachment
  * gradhelp_sentprivatemessage
  * gradhelp_severity
  * gradhelp_tickets_action
  * gradhelp_tickets_comment
  * gradhelp_useronline
  * gradhelp_users

====== Queries ======


===== Tickets that had comments later than 15 days after it was created =====
This shows old tickets via the comments date section, yet has a lot of duplicate data and is based off of comments, NOT when the ticket was closed. Cannot query off when the ticket was close yet, because it hasnt been being recorded by the program (this is a known bug).

FIXME **I have updated the code so that an easier query can be run. I have not made the query yet**




===== AVERAGE, MIN, MAX days tickets are open =====
==== All tickets ====
<code sql>
SELECT 
   AVG(DATEDIFF(L.ticket_finished_date, L.ticket_added_date)) AS AvgDaysToFinish,
   MIN(DATEDIFF(L.ticket_finished_date, L.ticket_added_date)) AS MinDaysToFinish,
   MAX(DATEDIFF(L.ticket_finished_date, L.ticket_added_date)) AS MaxDaysToFinish
FROM gradhelp_list L
WHERE L.ticket_status = 'Closed';
</code>

==== Average Per employee ====
<code sql>
SELECT U.name, 
   AVG(DATEDIFF(L.ticket_finished_date, L.ticket_added_date)) AS AverageDaysToFinish,
   MIN(DATEDIFF(L.ticket_finished_date, L.ticket_added_date)) AS MinDaysToFinish,
   MAX(DATEDIFF(L.ticket_finished_date, L.ticket_added_date)) AS MaxDaysToFinish
FROM gradhelp_list L, gradhelp_users U
WHERE L.ticket_owner_id = U.id AND
L.ticket_status = 'Closed'
GROUP BY U.name
</code>

==== Per severity ====
<code sql>
SELECT L.ticket_severity,
   AVG(DATEDIFF(L.ticket_finished_date, L.ticket_added_date)) AS AverageDaysToFinish,
   MIN(DATEDIFF(L.ticket_finished_date, L.ticket_added_date)) AS MinDaysToFinish,
   MAX(DATEDIFF(L.ticket_finished_date, L.ticket_added_date)) AS MaxDaysToFinish
FROM gradhelp_list L
WHERE L.ticket_status = 'Closed'
GROUP BY L.ticket_severity
</code>

==== Per work type(group) ====
<code sql>
SELECT G.group_name, 
   AVG(DATEDIFF(L.ticket_finished_date, L.ticket_added_date)) AS AverageDaysToFinish,
   MIN(DATEDIFF(L.ticket_finished_date, L.ticket_added_date)) AS MinDaysToFinish,
   MAX(DATEDIFF(L.ticket_finished_date, L.ticket_added_date)) AS MaxDaysToFinish
FROM gradhelp_list L, gradhelp_groups G
WHERE L.groups = G.id AND
L.ticket_status = 'Closed'
GROUP BY G.group_name;
</code>


===== How long it takes us to close tickets ======
{{system:gradhelp:closetickets.png}}

<code sql>
SELECT DATEDIFF(L.ticket_finished_date, L.ticket_added_date) AS DaysToFinish, count(DATEDIFF(L.ticket_finished_date, L.ticket_added_date)) AS QuantityOfTickets
FROM gradhelp_list L
WHERE L.ticket_status = 'Closed'
GROUP BY DaysToFinish;
</code>