SQL Server - Generate a column value which satisfy same condition in a day

Question

I have a transaction table (Tran_Table) that contains Account Number, Transaction Date, Transaction Amount. I would like to generate a column that has "Yes" or "No" info in case if a transaction repeated in same day with same amount for same customer. Table and generated value example are shown below.

AccountNumber   Tran_Date   Amount  Have_SameTran
85694           01/01/2020  1000    Yes
85694           01/01/2020  300     no
85694           01/02/2020  300     no
24121           01/01/2020  500     yes
24121           01/02/2020  750     no
85694           01/03/2020  1000    no
24121           01/01/2020  500     yes

how can i do this?

Answer 1

You can use window functions and a conditional expression:

select 
    t.*,
    case 
        when count(*) over(partition by tran_date, account_number, amount) > 1 
        then 'yes' 
        else 'no' 
    end as has_same_tran
from mytable t