Skip to content
Advertisement

Replacing case with IIF in SQL Server

SELECT 
    patientname, patientinsurance, patienthistory,
    (CASE 
        WHEN patientinsurance IS NOT NULL THEN 'insured'
        WHEN CDPrintBatchId IS NULL THEN 'uninsured'    
     END) AS insurancestatus
FROM 
    [dbo]..database
WHERE
    admissiontime BETWEEN '2020-09-03' AND '2020-09-04'

So for my above query, I want to use IIF instead of CASE, because I want to show only uninsured patients, would something be correct? Because if I only use one case in my case scenario, the null will should up in my insurancestatus column instead of uninsured.

SELECT 
    IIF(patient("Null") = 0, 'YES', 'NO');

Basically I just want the result for null showing up in result, excluding not null

Advertisement

Answer

This query is equivalent to the one posted except it returns ‘uninsured’ in all FALSE cases. insurancestatus will not be NULL

SELECT patientname, patientinsurance, patienthistory,
        iif(patientinsurance is not null, 'insured', 'uninsured') insurancestatus 
FROM [dbo]..database
where admissiontime between '2020-09-03' and '2020-09-04';

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement