Yes/No Format for query column

I have the following query, simplified for this question:

SELECT convert(bit, Substring(Max(convert(CHAR(8), tt.transaction_dt, 112) + convert(CHAR(1), tt.trans_live)), 9, 1)) AS is_live
FROM transaction_t tt

The query is designed to determine if a particular merchant has a “live” status depending on the “trans_live” value of the merchant’s LATEST transaction record.

This returns a “1” or “0”. However, I need a “yes/no” formatted column value.

NOTE: The section of the query as follows:

Max(convert(CHAR(8), tt.transaction_dt, 112) + convert(CHAR(1), trans_live))

returns a result in the following format:

202006271

Following is the relevant table structure:

transaction_t

id     transaction_dt     trans_live     merchant_id
-----------------------------------------------------
1      2020-04-02         0              4
2      2020-04-02         1              4
3      2020-04-03         1              4
4      2020-04-04         0              4
4      2020-06-27         1              4

Answer

Please use case statement like below,

SELECT CASE (convert(bit, Substring(Max(convert(CHAR(8), tt.transaction_dt, 112) + convert(CHAR(1), tt.trans_live)), 9, 1))) 
   WHEN '0' THEN 'no' 
   WHEN '1' THEN 'yes' 
END AS is_live 
FROM transaction_t tt

Syntax may be wrong but this would help

Advertisement

Answer