Link to home
Start Free TrialLog in
Avatar of tommym121
tommym121Flag for Canada

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..#investment') 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
Avatar of Lee Wadwell
Lee Wadwell
Flag of Australia image

What is the relationship between 'risk' and the amount?
Avatar of tommym121

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
But what makes something low, medium risk or high risk?
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.
ASKER CERTIFIED SOLUTION
Avatar of Lee Wadwell
Lee Wadwell
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks.