Fee Invoices Paid Late or Still Overdue — SQL Dates Problem
The accounts office closes its books for the quarter on 2024-08-31 and charges a late fee per day on tuition invoices.
- Difficulty: Easy
- Topics: Dates, Conditional Logic
- Dialect: MySQL
- Problem: #125
Problem statement
The accounts office closes its books for the quarter on 2024-08-31 and charges a late fee per day on tuition invoices. An invoice is late when it was paid after its due date, or when it is still unpaid on 2024-08-31 and its due date is before that day.
Return every late invoice with the columns invoice_id, roll_no and days_late — the days between the due date and the payment date, or 2024-08-31 for an unpaid invoice. An invoice paid on its due date is not late. Order the rows by days_late highest first, then by invoice_id.
Tables
Table: FeeInvoice
| Column | Type |
|---|---|
| invoice_id | int |
| roll_no | int |
| amount | int |
| due_date | date |
| paid_on | date |
Primary key: invoice_id.
amount is in rupees; paid_on is NULL while the invoice is unpaid and is never after 2024-08-31.
Examples
Example 1
FeeInvoice
| invoice_id | roll_no | amount | due_date | paid_on |
|---|---|---|---|---|
| 501 | 2101 | 85000 | 2024-07-15 | 2024-07-10 |
| 502 | 2102 | 85000 | 2024-07-15 | 2024-07-15 |
| 503 | 2103 | 85000 | 2024-07-15 | 2024-07-29 |
| 504 | 2104 | 42000 | 2024-08-10 | NULL |
| 505 | 2105 | 42000 | 2024-08-31 | NULL |
| 506 | 2101 | 12500 | 2024-08-01 | 2024-08-22 |
| 507 | 2106 | 12500 | 2024-09-15 | NULL |
Output
| invoice_id | roll_no | days_late |
|---|---|---|
| 504 | 2104 | 21 |
| 506 | 2101 | 21 |
| 503 | 2103 | 14 |
How to solve Fee Invoices Paid Late or Still Overdue
Two kinds of invoice are late — paid after the due date, and unpaid past the due date — but both measure the same thing: the gap between the due date and the day the invoice was settled, where an unpaid invoice counts as settled on the closing day. COALESCE(paid_on, '2024-08-31') turns the two cases into one date, and DATEDIFF(settled, due_date) is the days late.
A positive difference means late; zero is paid on the due date (or unpaid but due on the closing day) and negative is paid early, neither of which belongs in the answer. So the filter is the same expression > 0, and the list is ordered by it, highest first, with invoice_id breaking ties.
Spelling the two cases out with CASE and an OR in WHERE gives the same rows. Either way it is a single scan of the invoices.
Reference solution (MySQL)
SELECT invoice_id, roll_no,
DATEDIFF(COALESCE(paid_on, '2024-08-31'), due_date) AS days_late
FROM FeeInvoice
WHERE DATEDIFF(COALESCE(paid_on, '2024-08-31'), due_date) > 0
ORDER BY days_late DESC, invoice_idAnother way
SELECT invoice_id, roll_no, CASE WHEN paid_on IS NULL THEN DATEDIFF('2024-08-31', due_date) ELSE DATEDIFF(paid_on, due_date) END AS days_late FROM FeeInvoice WHERE (paid_on IS NULL AND due_date < '2024-08-31') OR paid_on > due_date ORDER BY days_late DESC, invoice_id ASCAnother way
SELECT invoice_id, roll_no, DATEDIFF(IFNULL(paid_on, '2024-08-31'), due_date) AS days_late FROM FeeInvoice WHERE IFNULL(paid_on, '2024-08-31') > due_date ORDER BY 3 DESC, 1← Courses Nobody Registered For in the Odd Semester · Average Quiz Score for Quizzes With Three or More Attempts →