Hello!
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!