I have a table that contains Account Number, Transaction Date, Transaction Branch and amount.
I would like to generate a column that contains the information of:
if that specific customer, made a transaction from different branches on the same day.
An example of result is shown below:
AccountNumber Transaction_branch tran_Date Amount Different_Branch_Tran
11452 331 20/07/2020 500 no
11452 331 21/07/2020 500 no
5432 14 22/07/2020 500 no
5432 14 22/07/2020 500 no
11452 14 24/07/2020 500 yes
11452 420 24/07/2020 500 yes
11452 14 26/07/2020 500 no
I have a code that shows me if a customer made a same amount of transaction on the same day. however I couldn't figure it out how to modify or change this code to get the result I want.
select
a.*,
case when count(*) over(partition by trandate, accountnumber, amount) > 1 then 'Yes' else 'No' end SameAmountSameDay
from Tran_table a
where trandate> '20190701'
.....
case when
min(Transaction_branch) over(partition by AccountNumber, tran_Date, Amount)
=
max(Transaction_branch) over(partition by AccountNumber, tran_Date, Amount)
then 'No' else 'Yes'
end as SameAmountSameDayDifferentBranch
.........