Search code examples
sql-servercountpartitioncase-when

Same day, same customer but different branch transactions


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' 

Solution

  • .....
    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
    .........