My original data is all in one table that looks something like this:
Port ClientCode Status Type PortfolioValue ClientName CA11 11111-22222 Open Cash 75,000 Smith CA21 11111-22222 Open Cash 25,000 Smith TR1 11111-22222 Open Trust 550,000 Smith CA41 10101-44444 Open Cash 100,000 Jones CA31 10001-33333 Open Cash 50,000 Black RE1 10001-33333 Open Estate 850,000 Black I want to pull a list of all Ports and their individual values where the Port has an Open Status and the clients combined total value is greater than 500,000. Our client codes are formatted XXXXX-YYYYY where the XXXXX identifies the client number and YYYYY represents subaccounts so I regularly use: LEFT(p.ClientCode, NULLIF(charindex('-',p.ClientCode)-1,-1)) AS ClientNum to chop the Clientcode at the dash to get just the XXXXX for grouping. What I ultimately need to see in my results is: Port ClientCode Client Status Type PortfolioValue ClientName TotalValue CA11 11111-22222 11111 Open Cash 75,000 Smith 650,000 CA21 11111-22222 11111 Open Cash 25,000 Smith 650,000 CA31 10001-33333 10001 Open Cash 50,000 Black 900,000 Here is my query so far: SELECT Portfolio, ClientCode, LEFT(ClientCode, NULLIF(charindex('-',Clientcode)-1,-1)) AS Client, Status, Type, PortfolioValue ClientName FROM vPortfolio WHERE LEFT(ClientCode, NULLIF(charindex('-',ClientCode)-1,-1)) IN (SELECT LEFT(ClientCode, NULLIF(charindex('-',ClientCode)-1,-1)), SUM(PortfolioValue) AS TotalValue FROM vPortfolio WHERE Status = 'open' GROUP BY ClientCode HAVING SUM(PortfolioValue) > 500000) AND Status = 'open' AND Type = 'Cash'
I have the query to a point that it correctly identifies all of the accounts I am expecting but I have tried numerous revisions and cannot seem to get it to also pull in the Combined totalvalue as an additional column in the results set. I feel like I need to add another Select subquery into my FROM and join them but I cannot make this work correctly.
Any help you can offer would be greatly appreciated, especially if I am going about this completely the wrong way! Thanks!