?
Solved

SQL - ROLLUP Data

Posted on 2012-09-16
6
Medium Priority
?
284 Views
Last Modified: 2012-09-16
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
0
Comment
Question by:tommym121
  • 3
  • 3
6 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 38404182
What is the relationship between 'risk' and the amount?
0
 

Author Comment

by:tommym121
ID: 38404220
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
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38404226
But what makes something low, medium risk or high risk?
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:tommym121
ID: 38404289
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.
0
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 38404332
try:
;with fund_data as (
Select a.PlanCode, b.Risk,  SUM(a.Amount) As Total 
from #investment a
inner join #Fund b on a.FundCode = b.FundCode
where a.PlanCode = 'P2'
Group By b.Risk, a.PlanCode
)
select RiskLabel, isnull(Total,0) as Amount, r.Risk
from #risk r
left join fund_data f on r.risk = f.risk

Open in new window

0
 

Author Closing Comment

by:tommym121
ID: 38404368
Thanks.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question