Label Each Insurance Claim by How It Settled

Easy
203.

Label Each Insurance Claim by How It Settled

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

ColumnType
claim_idint
patient_idint
insurervarchar
claimed_amountint
approved_amountint

Primary key: claim_id.

Amounts in rupees. approved_amount is NULL until the insurer has processed the claim.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

InsuranceClaim

claim_idpatient_idinsurerclaimed_amountapproved_amount
80111Star Health8500085000
80212HDFC Ergo142000118500
80313Niva Bupa360000
80414Care Health61000NULL
80515ICICI Lombard5400054000
80616Star Health2300004000

Output

claim_idclaimed_amountapproved_amountsettlement
8018500085000Full
802142000118500Partial
803360000Rejected
80461000NULLPending
8055400054000Full
8062300004000Partial
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.

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

ColumnType
claim_idint
patient_idint
insurervarchar
claimed_amountint
approved_amountint

Primary key: claim_id.

Amounts in rupees. approved_amount is NULL until the insurer has processed the claim.

Examples

Example 1

InsuranceClaim

claim_idpatient_idinsurerclaimed_amountapproved_amount
80111Star Health8500085000
80212HDFC Ergo142000118500
80313Niva Bupa360000
80414Care Health61000NULL
80515ICICI Lombard5400054000
80616Star Health2300004000

Output

claim_idclaimed_amountapproved_amountsettlement
8018500085000Full
802142000118500Partial
803360000Rejected
80461000NULLPending
8055400054000Full
8062300004000Partial

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 InsuranceClaim

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

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