Label Each Insurance Claim by How It Settled — SQL Problem
The billing desk files a cashless claim with the patient's insurer for every insured discharge.
- Difficulty: Easy
- Topics: Conditional Logic
- Dialect: MySQL
- Problem: #203
Problem statement
The billing desk files a cashless claim with the patient's insurer for every insured discharge. Once the insurer processes it, the approved amount is recorded.
Return claim_id, claimed_amount, approved_amount and settlement, where settlement is: Pending when approved_amount is NULL; Rejected when it is 0; Full when it is at least the claimed amount; Partial otherwise. Return the rows in any order.
Tables
Table: InsuranceClaim
| Column | Type |
|---|---|
| claim_id | int |
| patient_id | int |
| insurer | varchar |
| claimed_amount | int |
| approved_amount | int |
Primary key: claim_id.
Amounts in rupees. approved_amount is NULL until the insurer has processed the claim.
Examples
Example 1
InsuranceClaim
| claim_id | patient_id | insurer | claimed_amount | approved_amount |
|---|---|---|---|---|
| 801 | 11 | Star Health | 85000 | 85000 |
| 802 | 12 | HDFC Ergo | 142000 | 118500 |
| 803 | 13 | Niva Bupa | 36000 | 0 |
| 804 | 14 | Care Health | 61000 | NULL |
| 805 | 15 | ICICI Lombard | 54000 | 54000 |
| 806 | 16 | Star Health | 230000 | 4000 |
Output
| claim_id | claimed_amount | approved_amount | settlement |
|---|---|---|---|
| 801 | 85000 | 85000 | Full |
| 802 | 142000 | 118500 | Partial |
| 803 | 36000 | 0 | Rejected |
| 804 | 61000 | NULL | Pending |
| 805 | 54000 | 54000 | Full |
| 806 | 230000 | 4000 | Partial |
How to solve Label Each Insurance Claim by How It Settled
Each claim gets exactly one label from its own row, so this is one CASE expression in the select list. The branches are tried in order and the first true one wins, which makes their order part of the logic.
The NULL test goes first. If it came last, a pending claim would reach the ELSE branch — every comparison against NULL is unknown, never true — and be labelled Partial. Next comes approved_amount = 0 for a rejection, before the < comparison that would otherwise also catch it. Then >= claimed_amount for a full settlement; the statement says at least, so an approval that exceeds the claim (insurers sometimes add room-rent differences back) is Full too. Whatever is left is Partial.
Nested IF calls express the same decision tree; another variant leaves the NULL case to fall out of a CASE with no ELSE and turns that NULL into 'Pending' with COALESCE. One pass over the table.
Reference solution (MySQL)
SELECT claim_id, claimed_amount, approved_amount,
CASE
WHEN approved_amount IS NULL THEN 'Pending'
WHEN approved_amount = 0 THEN 'Rejected'
WHEN approved_amount >= claimed_amount THEN 'Full'
ELSE 'Partial'
END AS settlement
FROM InsuranceClaimAnother way
SELECT claim_id, claimed_amount, approved_amount, IF(approved_amount IS NULL, 'Pending', IF(approved_amount = 0, 'Rejected', IF(approved_amount < claimed_amount, 'Partial', 'Full'))) AS settlement FROM InsuranceClaimAnother way
SELECT claim_id, claimed_amount, approved_amount, COALESCE(CASE WHEN approved_amount = 0 THEN 'Rejected' WHEN approved_amount >= claimed_amount THEN 'Full' WHEN approved_amount > 0 THEN 'Partial' END, 'Pending') AS settlement FROM InsuranceClaim← Patient Mobile Numbers That Cannot Get SMS Reminders · Doctor Names for the Prescription Letterhead →