COALESCE with a Potential Empty Field - SQL Server 2005

Question

Here's the issue: When a Patient comes into the doctor's office, they can have their blood pressure taken 3 times. The first time is bp1; the second is bp2 and the last time is bp3.

I need to find the last time a patient had their blood pressure checked and what it was. I thought I could use COALESCE(bp3,bp2,bp1). However; the system doesn't always make the unused fields NULL. Sometimes, they're empty.

An example of the data would be:

PatientID bp1 bp2 bp3 12345 140/90 134/30
12346 NULL 128/68 NULL

Then, I thought I'd use COALESCE(NULLIF(bp3,bp2),bp1) but that isn't working because sometimes the database shows NULL for bp1 or NULL for bp1 and bp3, but there's a blood pressure in bp2.

Anyone have any ideas on how to resolve this?

Thanks!

Please show sample data and desired results. Word problems are hard. — Aaron Bertrand, Jun 22 '12 at 18:51

score 1 · Answer 1 · answered Jun 22 '12 at 18:57

How about case? I don't know the datatype, so I'm guessing INT

case when bp3 > 0 then bp3
     when bp2 > 0 then bp2
     else bp1
end as bp

And for string (varchar) datatype

case when len(bp3) > 0 then bp3
     when len(bp2) > 0 then bp2
     else bp1
end as bp

score 1 · Accepted Answer · answered Jun 22 '12 at 18:59

1

You would have to NULLIF each column individually to make the COALESCE statement work.

E.g.,

COASLESCE(NULLIF(bp3, 0), NULLIF(bp2, 0), NULLIF(bp1, 0))

answered Jun 22 '12 at 18:59

Nick Vaccaro

5,428
6
38
60

COALESCE with a Potential Empty Field - SQL Server 2005

2 Answers2