IanWaterman
Programmer
Trying to build an investment query where I drill down from a fund or a company and find all underlying funds of investee companies.
In reality this is unlikely to go down more than 3-4 levels but could go further.
A company can invest in another company or fund and a fund can invest in another fund or company, hence the 4 groups of left joined queries.
The query works fine for a level. However, I then need to repeat but using the INVESTEE_FUND_IQID and INVESTEE_COMPANY_IQID as my starting point.
I need to produce a Stored Proc which can accept ACCOUNTID, FUNDID or COMPANYId and then show all levels.
I can pass the result of the table to a Temp table and identify the level/layer. So my plan is to append output for each level to temp table and then use that again in another embedded SP to filter on layer number and use that data as below.
My issue is that I can not find any code that allows me to loop and then also know when to stop trying to find data when hierarchy comes to an end.
Unfortunately I do not have any sample data. I am using Management Studio 17.9 but database is 2012
Thank you
Ian
In reality this is unlikely to go down more than 3-4 levels but could go further.
A company can invest in another company or fund and a fund can invest in another fund or company, hence the 4 groups of left joined queries.
The query works fine for a level. However, I then need to repeat but using the INVESTEE_FUND_IQID and INVESTEE_COMPANY_IQID as my starting point.
I need to produce a Stored Proc which can accept ACCOUNTID, FUNDID or COMPANYId and then show all levels.
I can pass the result of the table to a Temp table and identify the level/layer. So my plan is to append output for each level to temp table and then use that again in another embedded SP to filter on layer number and use that data as below.
My issue is that I can not find any code that allows me to loop and then also know when to stop trying to find data when hierarchy comes to an end.
Unfortunately I do not have any sample data. I am using Management Studio 17.9 but database is 2012
Thank you
Ian
Code:
select --TOP 100
ACCOUNT_NAME,
ACCOUNT_IQID,
MANAGED_ENTITY_IQID,
MANAGED_ENTITY_NAME,
MANAGED_FUND_IQID,
MANAGED_FUND_NAME,
MANAGED_COMPANY_IQID,
MANAGED_COMPANY_NAME ,
STATUS_DECSR,
'1' AS LAYER,
ISNULL(MF.IQID, MCF.IQID) AS INVESTEE_FUND_IQID,
ISNULL(MF.FUND, MCF.FUND) AS INVESTEE_FUND_NAME,
ISNULL(MC.IQID, MIC.IQID) AS INVESTEE_COMPANY_IQID,
ISNULL(MC.NAME, MIC.NAME) AS INVESTEE_COMPANY_NAME
FROM MANAGED_ENTITY AS ME
--MANAGED FUND LINK (FOF LINK)
LEFT JOIN VCSUBSCRVEHICUL AS SV ON ME.MANAGED_FUND_IQID = SV.FUND AND SV.IQDELETED = 0
LEFT JOIN VCSUBSCRIBER AS S ON SV.SUBSCRIBER = S.IQID AND S.IQDELETED = 0
LEFT JOIN VCFUND AS MF ON S.FUND = MF.IQID AND MF.IQDELETED = 0
LEFT JOIN VCSUBSCRVEHICUL AS SCV ON ME.MANAGED_COMPANY_IQID = SCV.ACCOUNT AND SCV.IQDELETED = 0
LEFT JOIN VCSUBSCRIBER AS SC ON SCV.SUBSCRIBER = SC.IQID AND SC.IQDELETED = 0
LEFT JOIN VCFUND AS MCF ON SC.FUND = MCF.IQID AND MCF.IQDELETED = 0
--MANAGED COMPANY LINK (DIRECT)
LEFT JOIN VCINVESTMENT AS I ON MANAGED_COMPANY_IQID = I.FIRM AND I.IQDELETED = 0
LEFT JOIN SFAACCOUNT AS MC ON I.ACCOUNT = MC.IQID AND MC.IQDELETED = 0
--
LEFT JOIN VCINVESTMENT AS IC ON MANAGED_FUND_IQID = IC.FUND AND IC.IQDELETED = 0
LEFT JOIN SFAACCOUNT AS MIC ON I.ACCOUNT = MIC.IQID AND MIC.IQDELETED = 0