Holmes Holmes - 1 year ago 44
SQL Question

SQL Creating New Column Based off Data

So, I am Crystal Report person and am really curious about how I would do this in SQL. I have been searching through stackoverflow and online but haven't found any clear answers that may be applicable (I may just be really slow...)

This seems to be a really simple problem though.

Basically, I am trying to create an attribute called "Vital Status" which is binary in that the person is alive or dead.

To do this, I want to go into our "Activity Status" attribute which contains:

Activity Status: Active main hospital, Active regional hospital, Active consult, Expired

And create a new attribute called "Vital Status" under Crystal formula fields that basically says:

If: Activity Status = "Active main hospital" or "Active regional hospital" or "Active consult"
Then: Vital Status = "Alive"
If: Activity Status = "Expired"
Then: Vital Status = "Dead"

I honestly have no idea how I would do this in SQL say if I wanted to include this in a query like:

SELECT Patient_Name, Disease_status, Date_of_birth, Vital Status
FROM Patient_Info_table
WHERE Disease_status = "diabetes"

Answer Source

This is really simple question and the answer is the same for most DB.

SELECT Patient_Name, Disease_status, Date_of_birth, Vital Status,
case when [Activity Status] in ('Active main hospital', 'Active regional hospital', 'Active consult' Then 'Alive'
else 'Dead' end [Vital Status]
  FROM Patient_Info_table
  WHERE Disease_status =  "diabetes"