When I added the case statement to Student Checking it doubled my results from 30k to 60k. There must be some aspect of case statements I'm missing: use ANALYTICS_ODS
DECLARE @ProcessDateKey INT;
SELECT @ProcessDateKey = DR.DateKey
FROM Common.DateRelative DR
WHERE DR.IsMostRecentCompleteProcessDate = 'Y';
SELECT
MM.MemberNumber
,COUNT(CASE WHEN PD.LEVEL1 = 'SHARE' OR PD.LEVEL1 = 'LOAN' THEN PD.LEVEL1 END) AS TotalProduct_Count
,COUNT(CASE WHEN PD.Level1 = 'SHARE' THEN PD.LEVEL3 END) AS Share_Count
,SUM(CASE WHEN PD.Level1 = 'SHARE' THEN PP.ProductAverageDailyBalance END) AS Share_Balance
,COUNT(CASE WHEN PD.Level1 = 'LOAN' THEN PD.LEVEL3 END) AS Loan_Count
,SUM(CASE WHEN PD.Level1 = 'LOAN' THEN PP.ProductAverageDailyBalance END) AS Loan_Balance
,COUNT(CASE WHEN pd.ProductName IN ('LIFEPLUS CHECKING','LIVENOW ACCOUNT','CASH BACK CHECKING','High-Yield Checking','Essential Checking','Student Checking') THEN pd.ProductName END) AS ConsumerChecking_Count
,MAX(CASE WHEN pd.ProductName IN ('LIFEPLUS CHECKING','LIVENOW ACCOUNT','CASH BACK CHECKING','High-Yield Checking','Essential Checking','Student Checking') THEN PP.ProductOpenDate END) AS ConsumerChecking_OpenDate
,SUM(CASE WHEN pd.ProductName IN ('LIFEPLUS CHECKING','LIVENOW ACCOUNT','CASH BACK CHECKING','High-Yield Checking','Essential Checking','Student Checking') THEN PP.ProductAverageDailyBalance END) AS ConsumerChecking_Balance
,COUNT(CASE WHEN pd.ProductName = 'LIFEPLUS CHECKING' THEN pd.ProductName END) AS LifePlus_Count
,SUM(CASE WHEN pd.ProductName = 'LIFEPLUS CHECKING' THEN PP.ProductAverageDailyBalance END) AS LifePlus_Balance
,COUNT(CASE WHEN pd.ProductName = 'LIVENOW ACCOUNT' THEN Pd.ProductName END) AS LiveNow_Count
,SUM(CASE WHEN pd.ProductName = 'LIVENOW ACCOUNT' THEN PP.ProductAverageDailyBalance END) AS LiveNow_Balance
,COUNT(CASE WHEN pd.ProductName = 'CASH BACK CHECKING' THEN pd.ProductName END) AS CashBack_Count
,SUM(CASE WHEN pd.ProductName = 'CASH BACK CHECKING' THEN PP.ProductAverageDailyBalance END) AS CashBack_Balance
,COUNT(CASE WHEN pd.ProductName = 'High-Yield Checking' THEN pd.ProductName END) AS HighYield_Count
,SUM(CASE WHEN pd.ProductName = 'High-Yield Checking' THEN PP.ProductAverageDailyBalance END) AS HighYield_Balance
,COUNT(CASE WHEN pd.ProductName = 'Essential Checking' THEN pd.ProductName END) AS Essential_Count
,SUM(CASE WHEN pd.ProductName = 'Essential Checking' THEN PP.ProductAverageDailyBalance END) AS Essential_Balance
,COUNT(CASE WHEN pd.ProductName = 'Student Checking' THEN pd.ProductName END) AS StudentChecking_Count
,SUM(CASE WHEN pd.ProductName = 'Student Checking' THEN PP.ProductAverageDailyBalance END) AS StudentChecking_Balance
,COUNT(CASE WHEN PD.LEVEL4 = 'PRIME SHARE' THEN PD.Level4 END) AS PrimeShare_Count
,SUM(CASE WHEN PD.LEVEL4 = 'PRIME SHARE' THEN PP.ProductAverageDailyBalance END) AS PrimeShare_Balance
,COUNT(CASE WHEN PD.Level3 = 'CERTIFICATE' THEN PD.Level3 END) AS CD_Count
,SUM(CASE WHEN PD.Level3 = 'CERTIFICATE' THEN PP.ProductAverageDailyBalance END) AS CD_Balance
,COUNT(CASE WHEN PD.LEVEL3 = 'MONEY MARKET' THEN PD.Level3 END) AS MoneyMarket_Count
,SUM(CASE WHEN PD.Level3 = 'MONEY MARKET' THEN PP.ProductAverageDailyBalance END) AS MoneyMarket_Balance
,COUNT(CASE WHEN PD.LEVEL3 = 'IRA Share' THEN PD.LEVEL3 END) AS IRAShare_Count
,SUM(CASE WHEN PD.LEVEL3 = 'IRA Share' THEN PP.ProductAverageDailyBalance END) AS IRAShare_Balance
,COUNT(CASE WHEN PD.LEVEL3 = 'CUSTOM SHARE' THEN PD.LEVEL3 END) AS CustomShare_Count
,SUM(CASE WHEN PD.LEVEL3 = 'CUSTOM SHARE' THEN PP.ProductAverageDailyBalance END) AS CustomShare_Balance
,COUNT(CASE WHEN PD.LEVEL3 = 'DIRECT AUTO' THEN PD.LEVEL3 END) AS DirectAuto_Count
,SUM(CASE WHEN PD.LEVEL3 = 'DIRECT AUTO' THEN PP.ProductAverageDailyBalance END) AS DirectAuto_Balance
,COUNT(CASE WHEN PD.LEVEL3 = 'INDIRECT AUTO' THEN PD.LEVEL3 END) AS IndirectAuto_Count
,SUM(CASE WHEN PD.LEVEL3 = 'INDIRECT AUTO' THEN PP.ProductAverageDailyBalance END) AS IndirectAuto_Balance
,COUNT(CASE WHEN PD.LEVEL3 = 'Credit Cards - Consumer' THEN PD.LEVEL3 END) AS CreditCard_Count
,SUM(CASE WHEN PD.LEVEL3 = 'Credit Cards - Consumer' THEN PP.ProductAverageDailyBalance END) AS CreditCard_Balance
,COUNT(CASE WHEN PD.LEVEL3 = 'First Mortgages' THEN PD.LEVEL3 END) AS FirstMortgage_Count
,SUM(CASE WHEN PD.LEVEL3 = 'First Mortgages' THEN PP.ProductAverageDailyBalance END) AS FirstMortgage_Balance
,COUNT(CASE WHEN PD.LEVEL3 = 'HOME EQUITY' THEN PD.LEVEL3 END) AS HomeEquity_Count
,SUM(CASE WHEN PD.LEVEL3 = 'HOME EQUITY' THEN PP.ProductAverageDailyBalance END) AS HomeEquity_Balance
,COUNT(CASE WHEN PD.LEVEL3 = 'OTHER SECURED' THEN PD.LEVEL3 END) AS OtherSecured_Count
,SUM(CASE WHEN PD.LEVEL3 = 'OTHER SECURED' THEN PP.ProductAverageDailyBalance END) AS OtherSecured_Balance
,COUNT(CASE WHEN PD.LEVEL3 = 'Participation Loans' THEN PD.LEVEL3 END) AS ParticipationLoans_Count
,SUM(CASE WHEN PD.LEVEL3 = 'Participation Loans' THEN PP.ProductAverageDailyBalance END) AS ParticipationLoans_Balance
,COUNT(CASE WHEN PD.LEVEL3 = 'Unsecured' THEN PD.LEVEL3 END) AS Unsecured_Count
,SUM(CASE WHEN PD.LEVEL3 = 'Unsecured' THEN PP.ProductAverageDailyBalance END) Unsecured_Balance
,count(case when pd.level3 = 'Fin Tech' then pd.Level3 end) FinTech_Count
,sum(case when pd.level3 = 'Fin Tech' then PP.ProductAverageDailyBalance end) FinTechBalance
,MM.MemberMarket
,MIN (AN.BirthAge) BirthAge
,DR.YearAge AS RelationshipAge
--,PP.ProductOpenDateKey
, CASE
WHEN (PP.ProductOpenDateKey >= '20230201' AND Level4 = 'Consumer Checking') THEN 1
ELSE 0
END AS OpenedCheckingAfter20230201Flag
FROM Product.Product PP
JOIN Account.Account AA ON AA.AccountPK = PP.AccountPK
JOIN Member.Member MM ON MM.MemberPK = AA.MemberPK
JOIN Common.ProductDefinitionCurrent PD ON PD.ProductDefinitionPK = PP.ProductDefinitionPK
JOIN Common.DateRelative DR ON DR.DateKey = PP.ProcessDateKey
LEFT JOIN Account.Name AN ON AA.AccountPK = AN.AccountPK
WHERE 1=1
AND DR.IsMostRecentCompleteProcessDate = 'Y'
AND PP.IsOpenCloseSameDay = 'N'
AND PP.ProductStatus = 'OPEN'
AND MM.MemberStatus = 'ACTIVE'
AND AN.NameType = '0'
AND AN.BirthAge <= '26'
--AND (PP.ProductOpenDateKey >= '20230201' AND Level4 = 'Consumer Checking')
AND PP.ProcessDateKey = @ProcessDateKey
GROUP BY MM.MemberNumber
,MM.MemberMarket
,DR.YearAge
,PP.ProductOpenDateKey
,PD.Level4
ORDER BY
MIN (AN.BirthAge)