SELECT     Household.Name, FinancialPlanner.LastName, PlanFirmSpecCode.SpecialCode,
           SUM(Asset.Value) AS HHValue,
           SUM(CASE WHEN financialinst.name = 'National Financial Services' THEN Asset.Value ELSE 0 END) AS NationalValue,
           SUM(CASE WHEN financialinst.name <> 'National Financial Services'THEN Asset.Value END) AS DirectValue
FROM       PlanFirmSpecCode INNER JOIN
           FinPlanSpecCode ON PlanFirmSpecCode.PlanFirmSpecCodeID = FinPlanSpecCode.PlanFirmSpecCodeID RIGHT OUTER JOIN
           Household INNER JOIN
           Account ON Household.HouseholdID = Account.HouseholdID INNER JOIN
           Asset ON Account.AccountID = Asset.AccountID INNER JOIN
           FinancialPlanner ON Household.FinancialPlannerID = FinancialPlanner.FinancialPlannerID LEFT OUTER 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
Order by   household.Name