Fee Invoices Paid Late or Still Overdue

Easy
125.

Fee Invoices Paid Late or Still Overdue

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

ColumnType
invoice_idint
roll_noint
amountint
due_datedate
paid_ondate

Primary key: invoice_id.

amount is in rupees; paid_on is NULL while the invoice is unpaid and is never after 2024-08-31.

Example

The rows must come back in the order shown.

FeeInvoice

invoice_idroll_noamountdue_datepaid_on
5012101850002024-07-152024-07-10
5022102850002024-07-152024-07-15
5032103850002024-07-152024-07-29
5042104420002024-08-10NULL
5052105420002024-08-31NULL
5062101125002024-08-012024-08-22
5072106125002024-09-15NULL

Output

invoice_idroll_nodays_late
504210421
506210121
503210314
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
invoice_idint
roll_noint
amountint
due_datedate
paid_ondate

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_idroll_noamountdue_datepaid_on
5012101850002024-07-152024-07-10
5022102850002024-07-152024-07-15
5032103850002024-07-152024-07-29
5042104420002024-08-10NULL
5052105420002024-08-31NULL
5062101125002024-08-012024-08-22
5072106125002024-09-15NULL

Output

invoice_idroll_nodays_late
504210421
506210121
503210314

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_id

Another 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 ASC

Another 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 →