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?
Advertisement
Answer
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