Hi. I have som problems grouping a mssql view.
I have a few thousand accounts, and I have about 4 different types of portfolios. These portfolios are identified with "kontotypeID"
these portfolios consists of hundreds of instruments each with a marketvalue ("markedsverdi") for the account.
I need to find the median value per instrument per portfolio.
My code so far only gives med the median value either by kontotypeID or by instrument (insname), not both combined.
I would like to add something like;
, but this gives me an error saying that this function can only handle one expression.
Here is my code so far.
I am not sure if I am looking at this the best way.
My goal is to find the correct allocation per instrument per portfolio, and if I use average values I will have problems with the customers who are in a buy or sell situation and the portfolios wouldnt be representative.
I would really appreciate any help...
I have a few thousand accounts, and I have about 4 different types of portfolios. These portfolios are identified with "kontotypeID"
these portfolios consists of hundreds of instruments each with a marketvalue ("markedsverdi") for the account.
I need to find the median value per instrument per portfolio.
My code so far only gives med the median value either by kontotypeID or by instrument (insname), not both combined.
I would like to add something like;
Code:
(ORDER BY kontotypeID, insname, markedsverdi)
Here is my code so far.
Code:
SELECT InsName, kontotypeID, Median = MAX(Median)
FROM
(
SELECT insname, kontotypeID,Median = PERCENTILE_CONT(0.5) WITHIN GROUP
(ORDER BY markedsverdi) OVER (PARTITION BY kontotypeid, insname)
FROM view_AFBO_portfolio
)
AS x
GROUP BY InsName, kontotypeID;
I am not sure if I am looking at this the best way.
My goal is to find the correct allocation per instrument per portfolio, and if I use average values I will have problems with the customers who are in a buy or sell situation and the portfolios wouldnt be representative.
I would really appreciate any help...