Maintenance Tickets Breaching the Seven-Day SLA
A rental management company promises that every maintenance ticket is fixed within 7 days of being raised.
- Difficulty: Easy
- Topics: Dates, Conditional Logic
- Dialect: MySQL
- Problem: #352
Problem statement
A rental management company promises that every maintenance ticket is fixed within 7 days of being raised. On the review date 2025-03-31, a ticket's age is the number of days from raised_on to resolved_on — or, for a ticket still open (resolved_on NULL), from raised_on to 2025-03-31.
Return every ticket whose age is more than 7 days, with request_id, flat_no, category, days_open (the age) and state ('resolved' or 'open'), ordered by days_open descending, ties by request_id ascending. A ticket fixed in exactly 7 days met the SLA.
Tables
Table: MaintenanceRequest
| Column | Type |
|---|---|
| request_id | int |
| flat_no | varchar |
| category | enum(plumbing, electrical, carpentry, painting, pest_control) |
| raised_on | date |
| resolved_on | date |
Primary key: request_id.
resolved_on is NULL while the ticket is open. Every ticket was raised on or before 2025-03-31.
Examples
Example 1
MaintenanceRequest
| request_id | flat_no | category | raised_on | resolved_on |
|---|---|---|---|---|
| 501 | A-0302 | plumbing | 2025-03-01 | 2025-03-04 |
| 502 | B-1204 | electrical | 2025-03-02 | 2025-03-09 |
| 503 | B-0801 | carpentry | 2025-03-03 | 2025-03-14 |
| 504 | C-0101 | pest_control | 2025-03-20 | NULL |
| 505 | A-1101 | painting | 2025-03-10 | NULL |
| 506 | C-0505 | plumbing | 2025-02-25 | 2025-03-08 |
Output
| request_id | flat_no | category | days_open | state |
|---|---|---|---|---|
| 505 | A-1101 | painting | 21 | open |
| 503 | B-0801 | carpentry | 11 | resolved |
| 504 | C-0101 | pest_control | 11 | open |
| 506 | C-0505 | plumbing | 11 | resolved |
How to solve Maintenance Tickets Breaching the Seven-Day SLA
Every ticket needs an end date to measure against: its resolved_on if it was fixed, otherwise the review date. COALESCE(resolved_on, '2025-03-31') gives that in one expression, and DATEDIFF(end, raised_on) turns it into the age in days. The same expression filters (> 7) and is selected as days_open; a derived table lets you name it once and filter on the alias, as the first alternative does.
The state is a plain CASE on whether resolved_on is NULL. The boundary matters: ticket 502 was raised on the 2nd and fixed on the 9th — exactly 7 days — and met the promise, so it is not listed; ticket 503 took 11 days and is.
For open tickets, "older than 7 days on 31 March" is the same as raised before 24 March, which the second alternative uses. Order by age, oldest breach first, with the ticket id breaking ties. One scan of the table.
Reference solution (MySQL)
SELECT request_id, flat_no, category,
DATEDIFF(COALESCE(resolved_on, '2025-03-31'), raised_on) AS days_open,
CASE WHEN resolved_on IS NULL THEN 'open' ELSE 'resolved' END AS state
FROM MaintenanceRequest
WHERE DATEDIFF(COALESCE(resolved_on, '2025-03-31'), raised_on) > 7
ORDER BY days_open DESC, request_idAnother way
SELECT * FROM (SELECT request_id, flat_no, category, DATEDIFF(IFNULL(resolved_on, '2025-03-31'), raised_on) AS days_open, IF(resolved_on IS NULL, 'open', 'resolved') AS state FROM MaintenanceRequest) t WHERE days_open >= 8 ORDER BY days_open DESC, request_idAnother way
SELECT request_id, flat_no, category, CASE WHEN resolved_on IS NULL THEN DATEDIFF('2025-03-31', raised_on) ELSE DATEDIFF(resolved_on, raised_on) END AS days_open, CASE WHEN resolved_on IS NULL THEN 'open' ELSE 'resolved' END AS state FROM MaintenanceRequest WHERE (resolved_on IS NULL AND raised_on < '2025-03-24') OR DATEDIFF(resolved_on, raised_on) > 7 ORDER BY days_open DESC, request_id← Masked Tenant Phone Numbers for the Notice Board · Broker Commission by Deal Slab →