Post reply

Trying to Extract customer Transacted in two unique merchants

  • January 6, 2021 at 6:26 pm

    #3828218

    Hi,

    Am trying to extract list of card holders who had done transactions in two different merchants on the same day.

    Both the merchant details are available in the same table.

    Please find enclosed code am using.

    Select Distinct(a.cardnumber),Count(*), Sum(AMT) from transactions a

    where Merchant like('%Microsoft%')

    and a.cardnumber in (Select b. card number from transactions B)

    Where Merchant like('%Google%')

    I got list of card number however am not getting transaction count and cumulative transaction amount of Google(Merchant 2).

    Please help me with the code.

  • January 6, 2021 at 10:01 pm

    #3828290

     

    SELECT a.cardnumber, Count(*), Sum(AMT) 
    from transactions a
    where Merchant like '%Google%' or Merchant like '%Microsoft%'
    group by a.cardnumber
    having max(case when Merchant like '%Google%' then 1 else 0 end) = 1 and
        max(case when Merchant like '%Microsoft%' then 1 else 0 end) = 1
    order by cardnumber

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply