tommym121
asked on
SQL - ROLLUP Data
I would like to generate the following result,
For PlanCode = P1
RiskLabel Amount Risk
--------------- ------------- -------
Low 10 1
Medum 0 2
High 0 3
For PlanCode = P2
RiskLabel Amount Risk
--------------- ------------- -------
Low 0 1
Medum 20 2
High 70 3
I am not sure how to do that based the following data I have
Can anyone help me on this?
IF OBJECT_ID('tempdb..#invest ment') IS NOT NULL
DROP TABLE #investment;
IF OBJECT_ID('tempdb..#Fund') IS NOT NULL
DROP TABLE #Fund;
IF OBJECT_ID('tempdb..#Risk') IS NOT NULL
DROP TABLE #Risk;
CREATE TABLE #investment
(
InvestmentCode varchar(50) NOT NULL,
PlanCode varchar(50) NOT NULL,
FundCode varchar(50) NOT NULL,
Amount int NOT NULL
)
go
INSERT INTO #investment
VALUES
('123', 'P1', 'F1', 10),
('456', 'P2', 'F2', 20),
('789', 'P2', 'F3', 30),
('321', 'P2', 'F3', 40)
GO
CREATE TABLE #Fund
(
FundCode varchar(50) NOT NULL,
Risk int NOT NULL
)
go
INSERT INTO #Fund
VALUES
('F1', 1),
('F2', 2),
('F3', 3)
GO
CREATE TABLE #Risk
(
RiskLabel varchar(50) NOT NULL,
Risk int NOT NULL
)
go
INSERT INTO #Risk
VALUES
('Low', 1),
('Medium', 2),
('High', 3)
GO
Select a.InvestmentCode, a.PlanCode, a.FundCode, b.Risk, SUM(a.Amount) As Total from #investment a
inner join #Fund b on a.FundCode = b.FundCode
Group By b.Risk, a.InvestmentCode, a.PlanCode, a.FundCode with ROLLUP
For PlanCode = P1
RiskLabel Amount Risk
--------------- ------------- -------
Low 10 1
Medum 0 2
High 0 3
For PlanCode = P2
RiskLabel Amount Risk
--------------- ------------- -------
Low 0 1
Medum 20 2
High 70 3
I am not sure how to do that based the following data I have
Can anyone help me on this?
IF OBJECT_ID('tempdb..#invest
DROP TABLE #investment;
IF OBJECT_ID('tempdb..#Fund')
DROP TABLE #Fund;
IF OBJECT_ID('tempdb..#Risk')
DROP TABLE #Risk;
CREATE TABLE #investment
(
InvestmentCode varchar(50) NOT NULL,
PlanCode varchar(50) NOT NULL,
FundCode varchar(50) NOT NULL,
Amount int NOT NULL
)
go
INSERT INTO #investment
VALUES
('123', 'P1', 'F1', 10),
('456', 'P2', 'F2', 20),
('789', 'P2', 'F3', 30),
('321', 'P2', 'F3', 40)
GO
CREATE TABLE #Fund
(
FundCode varchar(50) NOT NULL,
Risk int NOT NULL
)
go
INSERT INTO #Fund
VALUES
('F1', 1),
('F2', 2),
('F3', 3)
GO
CREATE TABLE #Risk
(
RiskLabel varchar(50) NOT NULL,
Risk int NOT NULL
)
go
INSERT INTO #Risk
VALUES
('Low', 1),
('Medium', 2),
('High', 3)
GO
Select a.InvestmentCode, a.PlanCode, a.FundCode, b.Risk, SUM(a.Amount) As Total from #investment a
inner join #Fund b on a.FundCode = b.FundCode
Group By b.Risk, a.InvestmentCode, a.PlanCode, a.FundCode with ROLLUP
What is the relationship between 'risk' and the amount?
ASKER
I basically trying to get the risk analysis of my investment within each investment plan (say P1 and P2_.
I like to know what is my total amount of investment for each plan in each risk category.
For Plan P2, my investment is mainly in two seperate category (2 and 3). The category 2 have only 1 investment, it totals to 20, Category 3 have two investment, it totals up to 70.
Let me know if that make sense.
I like to create a stored procedure given the Plan code, I will provide the risk analysis of the investment within the plan
I like to know what is my total amount of investment for each plan in each risk category.
For Plan P2, my investment is mainly in two seperate category (2 and 3). The category 2 have only 1 investment, it totals to 20, Category 3 have two investment, it totals up to 70.
Let me know if that make sense.
I like to create a stored procedure given the Plan code, I will provide the risk analysis of the investment within the plan
But what makes something low, medium risk or high risk?
ASKER
The Low, Medium, and High Risk is defined in the #Risk table
A Plan have one or more investment
Each investment is one of the fund in #fund
Each fund is associated with certain risk defined in the Risk field '1, 2 or 3'
Label of the Risk is defined in the #Risk table
Hope that makes sense.
A Plan have one or more investment
Each investment is one of the fund in #fund
Each fund is associated with certain risk defined in the Risk field '1, 2 or 3'
Label of the Risk is defined in the #Risk table
Hope that makes sense.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks.