SQL Case Statement or Sub-Query-CodePudding

In a query we have in an application, we have a situation where we are bringing records back that are open, closed, and archived with a date associated with them. This is a table associated and joined with a main table. The table could have 1 to 3 records associated with the same ID of the main table depending if the record has been opened, closed, and/or archived. The three stages essentially of open, closed, and archived.

What we're looking to do is this: When EStatusID = 1 (Which means open) we need the DateClosed to read as blank (because it's not closed or archived yet)

     SELECT
     E.EID,
     EStatus.EStatusID,
     FORMAT (EStatus.DateCreated, 'MM/dd/yyyy') as DateClosed,

I won't bore you with the rest of the query because it's long and not useful to the question. So we need some kind of Case statement or sub query or something in the Select to accomplish this task.

CodePudding user response：

You can use a case expression:

CASE WHEN EStatus.EStatusID <> 1 THEN FORMAT (EStatus.DateCreated, 'MM/dd/yyyy') END
    AS DateClosed,