Medicine Batches Expiring Within 90 Days — SQL Dates Problem
The hospital pharmacy returns near-expiry stock to distributors for credit, but only while it has not expired yet. Take 2025-06-30 as today.
- Difficulty: Easy
- Topics: Dates, Basics
- Dialect: MySQL
- Problem: #201
Problem statement
The hospital pharmacy returns near-expiry stock to distributors for credit, but only while it has not expired yet. Take 2025-06-30 as today.
Return every batch that still has stock (quantity greater than 0) and whose expiry_date is on or after today and at most 90 days after it, with columns batch_id, medicine, quantity and days_left (days from today to the expiry date). Order by days_left, then batch_id.
Tables
Table: PharmacyBatch
| Column | Type |
|---|---|
| batch_id | int |
| medicine | varchar |
| quantity | int |
| expiry_date | date |
Primary key: batch_id.
One row per batch received; quantity is the number of units still on the shelf.
Examples
Example 1
PharmacyBatch
| batch_id | medicine | quantity | expiry_date |
|---|---|---|---|
| 1 | Amoxicillin 500mg | 120 | 2025-07-15 |
| 2 | Paracetamol 500mg | 0 | 2025-07-01 |
| 3 | Insulin Glargine | 18 | 2025-06-30 |
| 4 | ORS Sachet | 300 | 2025-09-28 |
| 5 | Ceftriaxone 1g | 40 | 2025-09-29 |
| 6 | Atorvastatin 10mg | 75 | 2025-06-12 |
| 7 | Salbutamol Inhaler | 22 | 2025-07-15 |
Output
| batch_id | medicine | quantity | days_left |
|---|---|---|---|
| 3 | Insulin Glargine | 18 | 0 |
| 1 | Amoxicillin 500mg | 120 | 15 |
| 7 | Salbutamol Inhaler | 22 | 15 |
| 4 | ORS Sachet | 300 | 90 |
How to solve Medicine Batches Expiring Within 90 Days
Every condition is about a single row, so this is a filter plus a computed column. DATEDIFF(expiry_date, '2025-06-30') is the number of days left: 0 for a batch expiring today, negative for one already expired. The batch qualifies when that number is between 0 and 90 inclusive and the batch still holds stock.
The same window can be written on the dates themselves — expiry_date BETWEEN '2025-06-30' AND DATE_ADD('2025-06-30', INTERVAL 90 DAY) — which lets an index on expiry_date find the batches without computing a difference for every row. Both ends matter: a batch expiring exactly on day 90 is still returnable, day 91 is not, and yesterday's expiry is a write-off, not a return.
Batches with quantity 0 are empty slots in the register and nothing can be returned from them. Order by the computed column, with batch_id breaking ties between batches expiring on the same day.
Reference solution (MySQL)
SELECT batch_id, medicine, quantity, DATEDIFF(expiry_date, '2025-06-30') AS days_left
FROM PharmacyBatch
WHERE quantity > 0
AND expiry_date BETWEEN '2025-06-30' AND DATE_ADD('2025-06-30', INTERVAL 90 DAY)
ORDER BY days_left, batch_idAnother way
SELECT batch_id, medicine, quantity, DATEDIFF(expiry_date, '2025-06-30') AS days_left FROM PharmacyBatch WHERE quantity > 0 AND DATEDIFF(expiry_date, '2025-06-30') BETWEEN 0 AND 90 ORDER BY 4, 1Another way
SELECT * FROM (SELECT batch_id, medicine, quantity, DATEDIFF(expiry_date, '2025-06-30') AS days_left FROM PharmacyBatch WHERE quantity <> 0) t WHERE days_left >= 0 AND days_left <= 90 ORDER BY days_left, batch_id← Completed OPD Visits per Department in March 2025 · Patient Mobile Numbers That Cannot Get SMS Reminders →