CODEWITHSUNDEEP
sqlsql-server
user460114
user460114

Reputation: 1848

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

Upvotes: 0

Views: 113

Answers (5)

Govind
Govind

Reputation: 439

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270201

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.

I would expect a query like this:

select merchant_id, 
       (case when max(tt.transaction_dt) = max(case when tt.trans_live = 1 then tt.transaction_dt)
             then 'Y' else 'N'
        end) as is_live
from transaction_t tt
group by merchant_id;

I'm a bit baffled by what your query has to do with your question.

Upvotes: 0

allmhuran
allmhuran

Reputation: 4464

I would guess that the reason for the downvote is that you are doing something very "weird" here. But your question is fairly clear.

I understand the issue you are trying to solve: you want to get the trans_live value for the row with the most recent transaction date. But you can't see how to do that using max, because something like trans_live, max(transaction_dt) group by trans_live doesn't do what you want, and neither does max(transaction_dt), max(trans_live).

So you have come up with a rather "clever" solution: convert both to character values, concatenate them, sort them, and then split out the least siginificant "figure" from the concatenated value.

But the thing is, there are ways to do this sort of thing that are more natural in the language. Your way is a clever workaround, but it's cryptic.It's also a bit dangerous unless your trasaction_dt column is a date (and not a datetime), otherwise you are truncating the time part, in which case two transactions on the same date with different times will have the same truncated value, and then the one with trans_live = 1 will always be sorted higher than the one with trans_live = 0

A much simpler solution would be something like this:

create table transaction_t(merchant_id int, transaction_dt datetime, trans_live bit)
go

;with merchants as 
(
   select   distinct 
            merchant_id 
   from     transaction_t
)
select      m.merchant_id,
            is_live = t.trans_live
from        merchants m
cross apply (
               select   top 1 trans_live
               from     transaction_t
               where    merchant_id = m.merchant_id
               order by transaction_dt desc
            ) t

-- OR

select   distinct
         merchant_id, 
         is_live = first_value(trans_live) over
                   (
                      partition by merchant_id
                      order by transaction_dt desc
                   )
from     transaction_t

-- OR

select   merchant_id,
         is_live
from     (
            select   merchant_id,
                     is_live = trans_live,
                     rn = row_number() over 
                     (
                        partition by merchant_id 
                        order by transaction_dt desc
                     )
            from    transaction_t
         ) t 
where    t.rn = 1

Edit: And if you are only looking for the result for a single merchant...

declare @merchant_id int = 1;

select      top 1 
            is_live = trans_live
from        transaction_t
where       merchant_id = @merchant_id
order by    transaction_dt desc

Upvotes: 1

sacse
sacse

Reputation: 3744

You can simply use iif like below:

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

db<>fillde demo.

Upvotes: 1

Jim Macaulay
Jim Macaulay

Reputation: 5155

Please use below query, you have to use case statement to get your result. You can use required columns in the select statement. Hope this is your expectation, if not let me know

select 
convert(bit, Substring(Max(convert(CHAR(8), tt.transaction_dt, 112) + convert(CHAR(1), 
tt.trans_live)), 9, 1)) AS is_live,
case when trans_live = 1 then 'Yes' 
   when trans_live = 0 then 'No' end as  trans_live
from transaction_t tt

Upvotes: 0

Related Questions