SQL create new field name from query that populates value with "0" or "1"

Question

I have a field named status that has many records with only 3 possible values: open, open_reminder_sent, and received_complete. I would like to write SQL code that does the following:

• if the record in the status field has a value of "open", create a new field called "open" and populate the field with a numeric value of "1" if the record is "open" and "0" if the record is either open_reminder_sent, or received_complete.

• If the record in the status field has a value of open_reminder_sent, create a new field called "open_reminder_sent" and populate the field with a numeric value of "1" if the record is "open_reminder_sent" and "0" if the record is either "open", or "received_complete".

I would like to do the same thing for any records that have a value of "received_complete".

Here is the code that I am working with now: Could someone please advise on SQL code that will do what I'm describing above?

LOCATE(' ', `status`) = "OPEN",
        SUBSTRING(`status`, 1, LOCATE(' ', `status`) - 1),
        `status`
    ) AS OPEN,

 LOCATE(' ', `status`) = "OPEN_REMINDER_SENT",
        SUBSTRING(`status`, 1, LOCATE(' ', `status`) - 1),
        `status`
    ) AS OPEN_REMINDER_SENT,

 LOCATE(' ', `status`) = "RECEIVED_COMPLETE",
        SUBSTRING(`status`, 1, LOCATE(' ', `status`) - 1),
        `status`
    ) AS RECEIVED_COMPLETE

Answer 1

I think what you are looking for is a CASE statement:

CASE WHEN status = 'open' THEN 1 ELSE 0 END AS 'open'
CASE WHEN status = 'open_reminder_sent' THEN 1 ELSE 0 END AS 'open_reminder_sent'