Below is a query I am working on. I am trying to make 3 count cases. The first one just counts all transactions. The second one counts only debit transactions that aren't approved.
For the third count case (one with *) I am trying to find out how many times a customer tries the same card on the same day. Is there any way to count this? A card is id_code_1 and days are by settlement_batch_number. For example if an id_code_1 shows up 3 times on a specific settlement_batch_number then it would show 3 as the count plus all the other id_code_1 that show up more time than once. IS there anyway to show the id_code_1 and how many times? If not just a total. Thanks for any help.
Use Winpayment GO
SELECT S.card_acceptor_identification STORE, COUNT (M.message_type) TOTAL_#_TRANS, COUNT(CASE WHEN id_code_1 = 'DB' and response_code <> '00' THEN 1 END) DEBIT_DECLINED, *COUNT(CASE WHEN THEN 1 END) FROM Store S Left Join financial_message M On M.card_acceptor_identification = S.card_acceptor_identification And (settlement_batch_number >= '880')