SQLTeam.com | Weblogs | Forums

Iclude item from a subquery in Select statement


#1

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!


#2

Will this not do it?

    SELECT Portfolio,
           ClientCode, 
           LEFT(ClientCode, NULLIF(charindex('-',Clientcode)-1,-1)) AS Client,
           Status, 
           Type, 
           ClientName,
           SUM(PortfolioValue) TotalValue 
           
    FROM vPortfolio 
    
    WHERE Status = 'open' 
    AND Type = 'Cash'
    group by Portfolio, ClientCode, STATus, type, ClientName
    HAVING SUM(PortfolioValue) > 500000

#3

Thank you for your response! I really should have added that the Portfolio is the unique key in the table so the Group By in your example above does not end up grouping anything and the Total Value is really only the individual value (no summing actually takes place).

I do need to see each Portfolio listed separately but the grouping should be for the left part of the client number and to sum the total market value of the client.

Any other thoughts? Thanks again!!!