I'm trying to get the syntax correct on my query but I'm having issues.
Currently my query will show the Household Total Value (HHValue) and then the NationalValue on one row, then the second row shows the HHValue again and the DirectValue. It's splitting the results between two rows and I want it all on one row.
However, when I try to change my CASE statement, I am getting errors. I think I'm just confusing myself but I need one row for each household with HHValue, NationalValue and DirectValue.
This is what I have in my query so far:
SELECT Household.Name , FinancialPlanner.LastName , PlanFirmSpecCode.SpecialCode , SUM(Asset.Value) AS HHValue , SUM(CASE WHEN financialinst.name = 'National Financial Services' THEN Value ELSE 0 END) AS NationalValue , SUM(CASE WHEN financialinst.name <> 'National Financial Services' THEN Value ELSE 0 END) AS DirectValue FROM PlanFirmSpecCode JOIN FinPlanSpecCode ON PlanFirmSpecCode.PlanFirmSpecCodeID = inPlanSpecCode.PlanFirmSpecCodeID RIGHT JOIN Household JOIN Account ON Household.HouseholdID = Account.HouseholdID JOIN Asset ON Account.AccountID = Asset.AccountID JOIN FinancialPlanner ON Household.FinancialPlannerID = FinancialPlanner.FinancialPlannerID LEFT JOIN FinancialInst ON Account.FinancialInstID = FinancialInst.FinancialInstID ON FinPlanSpecCode.FinancialPlannerID = FinancialPlanner.FinancialPlannerID WHERE Account.Closed <> 'Y' AND Asset.Value > '0' and Account.Managed <> 'N' GROUP BY Household.Name , FinancialPlanner.LastName , PlanFirmSpecCode.SpecialCode , CASE WHEN FinancialInst.Name = 'National Financial Services' THEN 'NFS' ELSE 'Direct Business' END Order by household.Name