Medicine Batches Expiring Within 90 Days

Easy
201.

Medicine Batches Expiring Within 90 Days

EasyDatesBasics

MySQL

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

ColumnType
batch_idint
medicinevarchar
quantityint
expiry_datedate

Primary key: batch_id.

One row per batch received; quantity is the number of units still on the shelf.

Example

The rows must come back in the order shown.

PharmacyBatch

batch_idmedicinequantityexpiry_date
1Amoxicillin 500mg1202025-07-15
2Paracetamol 500mg02025-07-01
3Insulin Glargine182025-06-30
4ORS Sachet3002025-09-28
5Ceftriaxone 1g402025-09-29
6Atorvastatin 10mg752025-06-12
7Salbutamol Inhaler222025-07-15

Output

batch_idmedicinequantitydays_left
3Insulin Glargine180
1Amoxicillin 500mg12015
7Salbutamol Inhaler2215
4ORS Sachet30090
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.

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

ColumnType
batch_idint
medicinevarchar
quantityint
expiry_datedate

Primary key: batch_id.

One row per batch received; quantity is the number of units still on the shelf.

Examples

Example 1

PharmacyBatch

batch_idmedicinequantityexpiry_date
1Amoxicillin 500mg1202025-07-15
2Paracetamol 500mg02025-07-01
3Insulin Glargine182025-06-30
4ORS Sachet3002025-09-28
5Ceftriaxone 1g402025-09-29
6Atorvastatin 10mg752025-06-12
7Salbutamol Inhaler222025-07-15

Output

batch_idmedicinequantitydays_left
3Insulin Glargine180
1Amoxicillin 500mg12015
7Salbutamol Inhaler2215
4ORS Sachet30090

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_id

Another 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, 1

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