SELECT
COUNT(*) AS total_tickets,
SUM(CASE
WHEN UPPER(COALESCE(SlaResponseMet,''))='Y'
THEN 1 ELSE 0 END) AS response_met,
SUM(CASE
WHEN UPPER(COALESCE(SlaResponseMet,''))='N'
THEN 1 ELSE 0 END) AS response_breached,
SUM(CASE
WHEN UPPER(COALESCE(SlaResolutionMet,''))='Y'
THEN 1 ELSE 0 END) AS resolution_met,
SUM(CASE
WHEN UPPER(COALESCE(SlaResolutionMet,''))='N'
THEN 1 ELSE 0 END) AS resolution_breached
FROM data_ticket
WHERE Package = '{package}'
AND CreatedDate >= DATE_SUB(NOW(),
INTERVAL 30 DAY)
SELECT
Guid, TicketNumber, Subject, Priority,
AssignedTo, SlaResolutionDeadline,
TIMESTAMPDIFF(
MINUTE, NOW(), SlaResolutionDeadline
) AS minutes_remaining
FROM data_ticket
WHERE Package = '{package}'
AND Status NOT IN ('CLOSED','CANCELLED','RESOLVED')
AND SlaResolutionDeadline IS NOT NULL
AND SlaResolutionDeadline < NOW()
ORDER BY SlaResolutionDeadline ASC
SELECT
Guid, TicketNumber, Subject, Priority,
AssignedTo, SlaResolutionDeadline,
TIMESTAMPDIFF(
MINUTE, NOW(), SlaResolutionDeadline
) AS minutes_remaining
FROM data_ticket
WHERE Package = '{package}'
AND Status NOT IN ('CLOSED','CANCELLED','RESOLVED')
AND SlaResolutionDeadline IS NOT NULL
AND SlaResolutionDeadline > NOW()
AND SlaResolutionDeadline <= DATE_ADD(
NOW(), INTERVAL 60 MINUTE)
ORDER BY SlaResolutionDeadline ASC
SELECT
DATE(CreatedDate) AS report_date,
COUNT(*) AS total,
SUM(CASE
WHEN UPPER(COALESCE(SlaResponseMet,''))='Y'
THEN 1 ELSE 0 END) AS resp_pass,
SUM(CASE
WHEN UPPER(COALESCE(SlaResponseMet,''))='N'
THEN 1 ELSE 0 END) AS resp_fail,
SUM(CASE
WHEN UPPER(COALESCE(SlaResolutionMet,''))='Y'
THEN 1 ELSE 0 END) AS resol_pass,
SUM(CASE
WHEN UPPER(COALESCE(SlaResolutionMet,''))='N'
THEN 1 ELSE 0 END) AS resol_fail
FROM data_ticket
WHERE Package = '{package}'
AND CreatedDate >= DATE_SUB(NOW(),
INTERVAL 30 DAY)
GROUP BY DATE(CreatedDate)
ORDER BY report_date ASC
SELECT
Priority,
COUNT(*) AS total,
SUM(CASE
WHEN UPPER(COALESCE(SlaResponseMet,''))='Y'
THEN 1 ELSE 0 END) AS resp_met,
SUM(CASE
WHEN UPPER(COALESCE(SlaResponseMet,''))='N'
THEN 1 ELSE 0 END) AS resp_breach,
SUM(CASE
WHEN UPPER(COALESCE(SlaResolutionMet,''))='Y'
THEN 1 ELSE 0 END) AS resol_met,
SUM(CASE
WHEN UPPER(COALESCE(SlaResolutionMet,''))='N'
THEN 1 ELSE 0 END) AS resol_breach
FROM data_ticket
WHERE Package = '{package}'
AND CreatedDate >= DATE_SUB(NOW(),
INTERVAL 30 DAY)
GROUP BY Priority
ORDER BY FIELD(
Priority,
'CRITICAL','HIGH','MEDIUM','LOW','INFO'
)
SELECT
COALESCE(NULLIF(AssignedTo,''),
'(unassigned)') AS assignee,
COUNT(*) AS total,
SUM(CASE
WHEN UPPER(COALESCE(SlaResponseMet,''))='Y'
THEN 1 ELSE 0 END) AS resp_met,
SUM(CASE
WHEN UPPER(COALESCE(SlaResponseMet,''))='N'
THEN 1 ELSE 0 END) AS resp_breach,
SUM(CASE
WHEN UPPER(COALESCE(SlaResolutionMet,''))='Y'
THEN 1 ELSE 0 END) AS resol_met,
SUM(CASE
WHEN UPPER(COALESCE(SlaResolutionMet,''))='N'
THEN 1 ELSE 0 END) AS resol_breach
FROM data_ticket
WHERE Package = '{package}'
AND CreatedDate >= DATE_SUB(NOW(),
INTERVAL 30 DAY)
GROUP BY assignee
ORDER BY total DESC