Cannot use an aggregate or a subquery in an expression

1

Good day.

I have a query that I am having difficult time with:

Select
ah.MemberNumber,
ah.AccountNumber,
BIN = Case When (Select Max(dc.BIN) From DebitCard dc Where dc.MemberNumber = ah.MemberNumber AND dc.CreditCardAccount = ah.AccountNumber) In (543940,534025) Then 420477
When (Select Max(dc.BIN) From DebitCard dc Where dc.MemberNumber = ah.MemberNumber AND dc.CreditCardAccount = ah.AccountNumber) Is Null AND (Select Max(dc.CreditCardAccount) From DebitCard dc Where dc.MemberNumber = ah.MemberNumber AND dc.BIN = 581551 AND dc.TransactionDate > ah.EntryDate) = 0 Then 581551
When (Select Max(dc.BIN) From DebitCard dc Where dc.MemberNumber = ah.MemberNumber AND dc.CreditCardAccount = ah.AccountNumber) Is Null OR (Select Max(dc.CreditCardAccount) From DebitCard dc Where dc.MemberNumber = ah.MemberNumber AND dc.BIN = 420477) <> 0 Then 420477
Else (Select Max(dc.BIN) From DebitCard dc Where dc.MemberNumber = ah.MemberNumber AND dc.CreditCardAccount = ah.AccountNumber)
End,
Transaction_Summary = Case When ah.TransactionCodeKey = 236 AND ah.PrincipalAmount < 0 AND ah.TokenString Not Like '%pay.fb.com%' AND ah.TokenString Not Like '%VISA%Direct%' AND ah.TokenString Not Like '%Apple%Cash%' Then 'Purchase - Debit Card Signature'
When ah.FocusTellerID = 6005 AND ah.PrincipalAmount < 0 AND ah.TokenString Like '%9%875%300%18%POS%' AND ah.TokenString Not Like '%pay.fb.com%' AND ah.TokenString Not Like '%VISA%Direct%' AND ah.TokenString Not Like '%Apple%Cash%' Then 'Purchase - Debit Card PIN/PINLess'
When ah.FocusTellerID = 6005 AND ah.PrincipalAmount > 0 AND ah.TokenString Like '%9%875%300%18%POS%' AND ah.TokenString Not Like '%pay.fb.com%' AND ah.TokenString Not Like '%VISA%Direct%' AND ah.TokenString Not Like '%Apple%Cash%' Then 'Return - PIN/PINLess'
When ah.TransactionType = 'VCR' AND ah.FocusTellerID = 6099 AND ah.PrincipalAmount > 0 AND ah.TokenString Not Like '%pay.fb.com%' AND ah.TokenString Not Like '%VISA%Direct%' AND ah.TokenString Not Like '%Apple%Cash%' Then 'Return - Debit Card Signature'
When ah.TransactionCodeKey = 236 AND ah.PrincipalAmount < 0 AND (ah.TokenString Like '%pay.fb.com%' Or ah.TokenString Like '%VISA%Direct%' OR ah.TokenString Like '%Apple%Cash%') Then 'Money Transfer - Debit'
When ah.TransactionType = 'VCR' AND ah. FocusTellerID = 6099 AND ah.PrincipalAmount > 0 AND (ah.TokenString Like '%pay.fb.com%' Or ah.TokenString Like '%VISA%Direct%' OR ah.TokenString Like '%Apple%Cash%') Then 'Money Transfer - Credit'
When ah.TokenString Like '%9%875%300%18%ATM%' AND ah.PrincipalAmount < 0 Then 'ATM Withdrawal'
When ah.TokenString Like '%9%875%300%18%ATM%' AND ah.PrincipalAmount > 0 Then 'ATM Deposit'
Else 'None'
End,
Total_Count = SUM(Case When ah.PrincipalAmount <> 0 Then 1 Else 0 End),
Total_Amount = SUM(Case When ah.PrincipalAmount <> 0 Then ah.PrincipalAmount Else 0 End)

From
AccountHistory ah

Where
ah.EntryDate Between '02/10/2021' AND '02/17/2021'
AND ah.AccountNumber Not Between 105 and 135

AND Case When ah.TransactionCodeKey = 236 AND ah.PrincipalAmount < 0 AND ah.TokenString Not Like '%pay.fb.com%' AND ah.TokenString Not Like '%VISA%Direct%' AND ah.TokenString Not Like '%Apple%Cash%' Then 'Purchase - Debit Card Signature'
When ah.FocusTellerID = 6005 AND ah.PrincipalAmount < 0 AND ah.TokenString Like '%9%875%300%18%POS%' AND ah.TokenString Not Like '%pay.fb.com%' AND ah.TokenString Not Like '%VISA%Direct%' AND ah.TokenString Not Like '%Apple%Cash%' Then 'Purchase - Debit Card PIN/PINLess'
When ah.FocusTellerID = 6005 AND ah.PrincipalAmount > 0 AND ah.TokenString Like '%9%875%300%18%POS%' AND ah.TokenString Not Like '%pay.fb.com%' AND ah.TokenString Not Like '%VISA%Direct%' AND ah.TokenString Not Like '%Apple%Cash%' Then 'Return - PIN/PINLess'
When ah.TransactionType = 'VCR' AND ah.FocusTellerID = 6099 AND ah.PrincipalAmount > 0 AND ah.TokenString Not Like '%pay.fb.com%' AND ah.TokenString Not Like '%VISA%Direct%' AND ah.TokenString Not Like '%Apple%Cash%' Then 'Return - Debit Card Signature'
When ah.TransactionCodeKey = 236 AND ah.PrincipalAmount < 0 AND (ah.TokenString Like '%pay.fb.com%' Or ah.TokenString Like '%VISA%Direct%' OR ah.TokenString Like '%Apple%Cash%') Then 'Money Transfer - Debit'
When ah.TransactionType = 'VCR' AND ah. FocusTellerID = 6099 AND ah.PrincipalAmount > 0 AND (ah.TokenString Like '%pay.fb.com%' Or ah.TokenString Like '%VISA%Direct%' OR ah.TokenString Like '%Apple%Cash%') Then 'Money Transfer - Credit'
When ah.TokenString Like '%9%875%300%18%ATM%' AND ah.PrincipalAmount < 0 Then 'ATM Withdrawal'
When ah.TokenString Like '%9%875%300%18%ATM%' AND ah.PrincipalAmount > 0 Then 'ATM Deposit'
Else 'None'
End <> 'None'

Group By
ah.MemberNumber,
ah.AccountNumber,
Case When (Select Max(dc.BIN) From DebitCard dc Where dc.MemberNumber = ah.MemberNumber AND dc.CreditCardAccount = ah.AccountNumber) In (543940,534025) Then 420477
When (Select Max(dc.BIN) From DebitCard dc Where dc.MemberNumber = ah.MemberNumber AND dc.CreditCardAccount = ah.AccountNumber) Is Null AND (Select Max(dc.CreditCardAccount) From DebitCard dc Where dc.MemberNumber = ah.MemberNumber AND dc.BIN = 581551 AND dc.TransactionDate > ah.EntryDate) = 0 Then 581551
When (Select Max(dc.BIN) From DebitCard dc Where dc.MemberNumber = ah.MemberNumber AND dc.CreditCardAccount = ah.AccountNumber) Is Null OR (Select Max(dc.CreditCardAccount) From DebitCard dc Where dc.MemberNumber = ah.MemberNumber AND dc.BIN = 420477) <> 0 Then 420477
Else (Select Max(dc.BIN) From DebitCard dc Where dc.MemberNumber = ah.MemberNumber AND dc.CreditCardAccount = ah.AccountNumber)
End,
Case When ah.TransactionCodeKey = 236 AND ah.PrincipalAmount < 0 AND ah.TokenString Not Like '%pay.fb.com%' AND ah.TokenString Not Like '%VISA%Direct%' AND ah.TokenString Not Like '%Apple%Cash%' Then 'Purchase - Debit Card Signature'
When ah.FocusTellerID = 6005 AND ah.PrincipalAmount < 0 AND ah.TokenString Like '%9%875%300%18%POS%' AND ah.TokenString Not Like '%pay.fb.com%' AND ah.TokenString Not Like '%VISA%Direct%' AND ah.TokenString Not Like '%Apple%Cash%' Then 'Purchase - Debit Card PIN/PINLess'
When ah.FocusTellerID = 6005 AND ah.PrincipalAmount > 0 AND ah.TokenString Like '%9%875%300%18%POS%' AND ah.TokenString Not Like '%pay.fb.com%' AND ah.TokenString Not Like '%VISA%Direct%' AND ah.TokenString Not Like '%Apple%Cash%' Then 'Return - PIN/PINLess'
When ah.TransactionType = 'VCR' AND ah.FocusTellerID = 6099 AND ah.PrincipalAmount > 0 AND ah.TokenString Not Like '%pay.fb.com%' AND ah.TokenString Not Like '%VISA%Direct%' AND ah.TokenString Not Like '%Apple%Cash%' Then 'Return - Debit Card Signature'
When ah.TransactionCodeKey = 236 AND ah.PrincipalAmount < 0 AND (ah.TokenString Like '%pay.fb.com%' Or ah.TokenString Like '%VISA%Direct%' OR ah.TokenString Like '%Apple%Cash%') Then 'Money Transfer - Debit'
When ah.TransactionType = 'VCR' AND ah. FocusTellerID = 6099 AND ah.PrincipalAmount > 0 AND (ah.TokenString Like '%pay.fb.com%' Or ah.TokenString Like '%VISA%Direct%' OR ah.TokenString Like '%Apple%Cash%') Then 'Money Transfer - Credit'
When ah.TokenString Like '%9%875%300%18%ATM%' AND ah.PrincipalAmount < 0 Then 'ATM Withdrawal'
When ah.TokenString Like '%9%875%300%18%ATM%' AND ah.PrincipalAmount > 0 Then 'ATM Deposit'
Else 'None'
End

Order By
ah.MemberNumber

Running the query this way results in the:
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause

If I take out the bold section of the Group By, it results in:
Column 'AccountHistory.EntryDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Finally, if I add the AccountHistory.EndDate to the Group By section, it doesn't group properly.

What can I do to correct this? Thank you.

2

I suggest you start by removing the duplicate logic.
Something like:

WITH DCMemberCard
AS
(
	SELECT MemberNumber, CreditCardAccount
		,MAX([BIN]) AS MaxBin
	FROM DebitCard
	GROUP BY MemberNumber, CreditCardAccount
)
,DBMember
AS
(
	SELECT MemberNumber
		,MAX
		(
			CASE
				WHEN TransactionDate > EntryDate AND [BIN] = 581551
				THEN [BIN]
			END
		) AS MaxBin581551
		,MAX
		(
			CASE
				WHEN [BIN] = 420477
				THEN [BIN]
			END
		) AS MaxBin420477
	FROM DebitCard
	GROUP BY MemberNumber
)
SELECT ah.MemberNumber, ah.AccountNumber
	,A.[BIN], A.Transaction_Summary
	,Total_Count = SUM(CASE WHEN ah.PrincipalAmount <> 0 THEN 1 ELSE 0 END)
	,Total_Amount = SUM(CASE WHEN ah.PrincipalAmount <> 0 THEN ah.PrincipalAmount ELSE 0 END)
FROM AccountHistory ah
	LEFT JOIN DCMemberCard MC
		ON ah.MemberNumber = MC.MemberNumber
			AND ah.AccountNumber = MC.CreditCardAccount
	LEFT JOIN DBMember M
		ON ah.MemberNumber = M.MemberNumber	
	OUTER APPLY
	(
		VALUES
		(
			CASE 
				WHEN MC.MaxBin IN (543940, 534025)
				THEN 420477
				WHEN MC.MaxBin IS NULL
					AND M.MaxBin581551 = 0
				THEN 581551
				WHEN MC.MaxBin IS NULL
					OR M.MaxBin420477 <> 0
				THEN 420477
				ELSE MC.MaxBin
			END			
			,CASE 
				WHEN ah.TransactionCodeKey = 236
					AND ah.PrincipalAmount < 0
					AND ah.TokenString NOT LIKE '%pay.fb.com%'
					AND ah.TokenString NOT LIKE '%VISA%Direct%'
					AND ah.TokenString NOT LIKE '%Apple%Cash%'
					THEN 'Purchase - Debit Card Signature'
				WHEN ah.FocusTellerID = 6005
					AND ah.PrincipalAmount < 0
					AND ah.TokenString LIKE '%9%875%300%18%POS%'
					AND ah.TokenString NOT LIKE '%pay.fb.com%'
					AND ah.TokenString NOT LIKE '%VISA%Direct%'
					AND ah.TokenString NOT LIKE '%Apple%Cash%'
					THEN 'Purchase - Debit Card PIN/PINLess'
				WHEN ah.FocusTellerID = 6005
					AND ah.PrincipalAmount > 0
					AND ah.TokenString LIKE '%9%875%300%18%POS%'
					AND ah.TokenString NOT LIKE '%pay.fb.com%'
					AND ah.TokenString NOT LIKE '%VISA%Direct%'
					AND ah.TokenString NOT LIKE '%Apple%Cash%'
					THEN 'Return - PIN/PINLess'
				WHEN ah.TransactionType = 'VCR'
					AND ah.FocusTellerID = 6099
					AND ah.PrincipalAmount > 0
					AND ah.TokenString NOT LIKE '%pay.fb.com%'
					AND ah.TokenString NOT LIKE '%VISA%Direct%'
					AND ah.TokenString NOT LIKE '%Apple%Cash%'
					THEN 'Return - Debit Card Signature'
				WHEN ah.TransactionCodeKey = 236
					AND ah.PrincipalAmount < 0
					AND (
						ah.TokenString LIKE '%pay.fb.com%'
						OR ah.TokenString LIKE '%VISA%Direct%'
						OR ah.TokenString LIKE '%Apple%Cash%'
						)
					THEN 'Money Transfer - Debit'
				WHEN ah.TransactionType = 'VCR'
					AND ah.FocusTellerID = 6099
					AND ah.PrincipalAmount > 0
					AND (
						ah.TokenString LIKE '%pay.fb.com%'
						OR ah.TokenString LIKE '%VISA%Direct%'
						OR ah.TokenString LIKE '%Apple%Cash%'
						)
					THEN 'Money Transfer - Credit'
				WHEN ah.TokenString LIKE '%9%875%300%18%ATM%'
					AND ah.PrincipalAmount < 0
					THEN 'ATM Withdrawal'
				WHEN ah.TokenString LIKE '%9%875%300%18%ATM%'
					AND ah.PrincipalAmount > 0
					THEN 'ATM Deposit'
				ELSE 'None'
			END	
		)
	) A ([BIN], Transaction_Summary)
WHERE ah.EntryDate BETWEEN '20210210' AND '20210217'
	AND ah.AccountNumber NOT BETWEEN 105 AND 135
	AND A.Transaction_Summary <> 'None'
GROUP BY ah.MemberNumber, ah.AccountNumber
	,A.[BIN], A.Transaction_Summary
ORDER BY ah.MemberNumber;
3

hi

first you will need to find out WHATS going on ..

to do that
Based on My Experience

My One Idea is ..
Very Nice formatting of the code
Please google search .. lots of online SQL Formatters available

My Second Idea ..
Build the SQL Query .. piece by piece .. i mean adding very small pieces at one time and running the query .. once you see something not working then you can see immediately whats the problem

Please let me know if you need any more help
I can simulate Test Data also and find the issue

4
SQL Script Formatted .. 
SELECT ah.membernumber,
       ah.accountnumber,
       BIN = CASE
               WHEN (SELECT Max(dc.bin)
                     FROM   debitcard dc
                     WHERE  dc.membernumber = ah.membernumber
                            AND dc.creditcardaccount = ah.accountnumber) IN ( 543940, 534025 ) THEN 420477
               WHEN (SELECT Max(dc.bin)
                     FROM   debitcard dc
                     WHERE  dc.membernumber = ah.membernumber
                            AND dc.creditcardaccount = ah.accountnumber) IS NULL
                    AND (SELECT Max(dc.creditcardaccount)
                         FROM   debitcard dc
                         WHERE  dc.membernumber = ah.membernumber
                                AND dc.bin = 581551
                                AND dc.transactiondate > ah.entrydate) = 0 THEN 581551
               WHEN (SELECT Max(dc.bin)
                     FROM   debitcard dc
                     WHERE  dc.membernumber = ah.membernumber
                            AND dc.creditcardaccount = ah.accountnumber) IS NULL
                     OR (SELECT Max(dc.creditcardaccount)
                         FROM   debitcard dc
                         WHERE  dc.membernumber = ah.membernumber
                                AND dc.bin = 420477) <> 0 THEN 420477
               ELSE (SELECT Max(dc.bin)
                     FROM   debitcard dc
                     WHERE  dc.membernumber = ah.membernumber
                            AND dc.creditcardaccount = ah.accountnumber)
             END,
       Transaction_Summary = CASE
                               WHEN ah.transactioncodekey = 236
                                    AND ah.principalamount < 0
                                    AND ah.tokenstring NOT LIKE '%pay.fb.com%'
                                    AND ah.tokenstring NOT LIKE '%VISA%Direct%'
                                    AND ah.tokenstring NOT LIKE '%Apple%Cash%' THEN 'Purchase - Debit Card Signature'
                               WHEN ah.focustellerid = 6005
                                    AND ah.principalamount < 0
                                    AND ah.tokenstring LIKE '%9%875%300%18%POS%'
                                    AND ah.tokenstring NOT LIKE '%pay.fb.com%'
                                    AND ah.tokenstring NOT LIKE '%VISA%Direct%'
                                    AND ah.tokenstring NOT LIKE '%Apple%Cash%' THEN 'Purchase - Debit Card PIN/PINLess'
                               WHEN ah.focustellerid = 6005
                                    AND ah.principalamount > 0
                                    AND ah.tokenstring LIKE '%9%875%300%18%POS%'
                                    AND ah.tokenstring NOT LIKE '%pay.fb.com%'
                                    AND ah.tokenstring NOT LIKE '%VISA%Direct%'
                                    AND ah.tokenstring NOT LIKE '%Apple%Cash%' THEN 'Return - PIN/PINLess'
                               WHEN ah.transactiontype = 'VCR'
                                    AND ah.focustellerid = 6099
                                    AND ah.principalamount > 0
                                    AND ah.tokenstring NOT LIKE '%pay.fb.com%'
                                    AND ah.tokenstring NOT LIKE '%VISA%Direct%'
                                    AND ah.tokenstring NOT LIKE '%Apple%Cash%' THEN 'Return - Debit Card Signature'
                               WHEN ah.transactioncodekey = 236
                                    AND ah.principalamount < 0
                                    AND ( ah.tokenstring LIKE '%pay.fb.com%'
                                           OR ah.tokenstring LIKE '%VISA%Direct%'
                                           OR ah.tokenstring LIKE '%Apple%Cash%' ) THEN 'Money Transfer - Debit'
                               WHEN ah.transactiontype = 'VCR'
                                    AND ah. focustellerid = 6099
                                    AND ah.principalamount > 0
                                    AND ( ah.tokenstring LIKE '%pay.fb.com%'
                                           OR ah.tokenstring LIKE '%VISA%Direct%'
                                           OR ah.tokenstring LIKE '%Apple%Cash%' ) THEN 'Money Transfer - Credit'
                               WHEN ah.tokenstring LIKE '%9%875%300%18%ATM%'
                                    AND ah.principalamount < 0 THEN 'ATM Withdrawal'
                               WHEN ah.tokenstring LIKE '%9%875%300%18%ATM%'
                                    AND ah.principalamount > 0 THEN 'ATM Deposit'
                               ELSE 'None'
                             END,
       Total_Count = Sum(CASE
                           WHEN ah.principalamount <> 0 THEN 1
                           ELSE 0
                         END),
       Total_Amount = Sum(CASE
                            WHEN ah.principalamount <> 0 THEN ah.principalamount
                            ELSE 0
                          END)
FROM   accounthistory ah
WHERE  ah.entrydate BETWEEN '02/10/2021' AND '02/17/2021'
       AND ah.accountnumber NOT BETWEEN 105 AND 135
       AND CASE
             WHEN ah.transactioncodekey = 236
                  AND ah.principalamount < 0
                  AND ah.tokenstring NOT LIKE '%pay.fb.com%'
                  AND ah.tokenstring NOT LIKE '%VISA%Direct%'
                  AND ah.tokenstring NOT LIKE '%Apple%Cash%' THEN 'Purchase - Debit Card Signature'
             WHEN ah.focustellerid = 6005
                  AND ah.principalamount < 0
                  AND ah.tokenstring LIKE '%9%875%300%18%POS%'
                  AND ah.tokenstring NOT LIKE '%pay.fb.com%'
                  AND ah.tokenstring NOT LIKE '%VISA%Direct%'
                  AND ah.tokenstring NOT LIKE '%Apple%Cash%' THEN 'Purchase - Debit Card PIN/PINLess'
             WHEN ah.focustellerid = 6005
                  AND ah.principalamount > 0
                  AND ah.tokenstring LIKE '%9%875%300%18%POS%'
                  AND ah.tokenstring NOT LIKE '%pay.fb.com%'
                  AND ah.tokenstring NOT LIKE '%VISA%Direct%'
                  AND ah.tokenstring NOT LIKE '%Apple%Cash%' THEN 'Return - PIN/PINLess'
             WHEN ah.transactiontype = 'VCR'
                  AND ah.focustellerid = 6099
                  AND ah.principalamount > 0
                  AND ah.tokenstring NOT LIKE '%pay.fb.com%'
                  AND ah.tokenstring NOT LIKE '%VISA%Direct%'
                  AND ah.tokenstring NOT LIKE '%Apple%Cash%' THEN 'Return - Debit Card Signature'
             WHEN ah.transactioncodekey = 236
                  AND ah.principalamount < 0
                  AND ( ah.tokenstring LIKE '%pay.fb.com%'
                         OR ah.tokenstring LIKE '%VISA%Direct%'
                         OR ah.tokenstring LIKE '%Apple%Cash%' ) THEN 'Money Transfer - Debit'
             WHEN ah.transactiontype = 'VCR'
                  AND ah. focustellerid = 6099
                  AND ah.principalamount > 0
                  AND ( ah.tokenstring LIKE '%pay.fb.com%'
                         OR ah.tokenstring LIKE '%VISA%Direct%'
                         OR ah.tokenstring LIKE '%Apple%Cash%' ) THEN 'Money Transfer - Credit'
             WHEN ah.tokenstring LIKE '%9%875%300%18%ATM%'
                  AND ah.principalamount < 0 THEN 'ATM Withdrawal'
             WHEN ah.tokenstring LIKE '%9%875%300%18%ATM%'
                  AND ah.principalamount > 0 THEN 'ATM Deposit'
             ELSE 'None'
           END <> 'None'
GROUP  BY ah.membernumber,
          ah.accountnumber,
          CASE
            WHEN (SELECT Max(dc.bin)
                  FROM   debitcard dc
                  WHERE  dc.membernumber = ah.membernumber
                         AND dc.creditcardaccount = ah.accountnumber) IN ( 543940, 534025 ) THEN 420477
            WHEN (SELECT Max(dc.bin)
                  FROM   debitcard dc
                  WHERE  dc.membernumber = ah.membernumber
                         AND dc.creditcardaccount = ah.accountnumber) IS NULL
                 AND (SELECT Max(dc.creditcardaccount)
                      FROM   debitcard dc
                      WHERE  dc.membernumber = ah.membernumber
                             AND dc.bin = 581551
                             AND dc.transactiondate > ah.entrydate) = 0 THEN 581551
            WHEN (SELECT Max(dc.bin)
                  FROM   debitcard dc
                  WHERE  dc.membernumber = ah.membernumber
                         AND dc.creditcardaccount = ah.accountnumber) IS NULL
                  OR (SELECT Max(dc.creditcardaccount)
                      FROM   debitcard dc
                      WHERE  dc.membernumber = ah.membernumber
                             AND dc.bin = 420477) <> 0 THEN 420477
            ELSE (SELECT Max(dc.bin)
                  FROM   debitcard dc
                  WHERE  dc.membernumber = ah.membernumber
                         AND dc.creditcardaccount = ah.accountnumber)
          END,
          CASE
            WHEN ah.transactioncodekey = 236
                 AND ah.principalamount < 0
                 AND ah.tokenstring NOT LIKE '%pay.fb.com%'
                 AND ah.tokenstring NOT LIKE '%VISA%Direct%'
                 AND ah.tokenstring NOT LIKE '%Apple%Cash%' THEN 'Purchase - Debit Card Signature'
            WHEN ah.focustellerid = 6005
                 AND ah.principalamount < 0
                 AND ah.tokenstring LIKE '%9%875%300%18%POS%'
                 AND ah.tokenstring NOT LIKE '%pay.fb.com%'
                 AND ah.tokenstring NOT LIKE '%VISA%Direct%'
                 AND ah.tokenstring NOT LIKE '%Apple%Cash%' THEN 'Purchase - Debit Card PIN/PINLess'
            WHEN ah.focustellerid = 6005
                 AND ah.principalamount > 0
                 AND ah.tokenstring LIKE '%9%875%300%18%POS%'
                 AND ah.tokenstring NOT LIKE '%pay.fb.com%'
                 AND ah.tokenstring NOT LIKE '%VISA%Direct%'
                 AND ah.tokenstring NOT LIKE '%Apple%Cash%' THEN 'Return - PIN/PINLess'
            WHEN ah.transactiontype = 'VCR'
                 AND ah.focustellerid = 6099
                 AND ah.principalamount > 0
                 AND ah.tokenstring NOT LIKE '%pay.fb.com%'
                 AND ah.tokenstring NOT LIKE '%VISA%Direct%'
                 AND ah.tokenstring NOT LIKE '%Apple%Cash%' THEN 'Return - Debit Card Signature'
            WHEN ah.transactioncodekey = 236
                 AND ah.principalamount < 0
                 AND ( ah.tokenstring LIKE '%pay.fb.com%'
                        OR ah.tokenstring LIKE '%VISA%Direct%'
                        OR ah.tokenstring LIKE '%Apple%Cash%' ) THEN 'Money Transfer - Debit'
            WHEN ah.transactiontype = 'VCR'
                 AND ah. focustellerid = 6099
                 AND ah.principalamount > 0
                 AND ( ah.tokenstring LIKE '%pay.fb.com%'
                        OR ah.tokenstring LIKE '%VISA%Direct%'
                        OR ah.tokenstring LIKE '%Apple%Cash%' ) THEN 'Money Transfer - Credit'
            WHEN ah.tokenstring LIKE '%9%875%300%18%ATM%'
                 AND ah.principalamount < 0 THEN 'ATM Withdrawal'
            WHEN ah.tokenstring LIKE '%9%875%300%18%ATM%'
                 AND ah.principalamount > 0 THEN 'ATM Deposit'
            ELSE 'None'
          END
ORDER  BY ah.membernumber