Help writing a report query

OK, I have to write a SQL query for someone, for a report. There's financial values involved. There's three tables, essentially. Call them account names, account types, and transactions. I need to return the value of each transaction with the transactions, the sum of the transactions for the account, and the sum of all account transactions across all account types for the account name. I'm not sure how best to do this, I'm thinking of creating a temporary table at the bottom level ( where the actual data is ) and joining against it to do a SUM on the higher levels. To do that, I seem to need to do group by, which then means I need to specify all my non grouped column names, is that right ?

Can you post a bit more info, particularly a small sample set of data to work with and what you want the results to be? I am having a bit of trouble envisioning what you want.

|||

OK, it works something like this:

table ShareInfo

ShareInfoId int

CurrentPrice int

table SharePurchase

SharePurchaseId int

PortfolioId int

ShareInfoId int

SharesPurchased int

PricePerShare int

table SharePortfolio

PortfolioId int

and some other columns for names, etc. So, on the bottom level, I want to list all share purchases within a portfolio, and how their value has changed in each instance to toay. This data will be collapsible on the report, and the row that shows always will show the share name, and the total value of shares purchased, and amount lost/gained. This data is also collapsible, and on the top level, I show the Portfolio name, and the total value/total amount lost or gained on the portfolio to date.

|||

Hi you can use the ROLLUP operator,

Example,
Create table #Shares
(
PortfolioId int,
ShareId int,
NoOfShares int,
TimePurchased varchar(10),
SharesPerPrice int
)

Insert Into #Shares values(1,1,10,'10:00 AM',30);
Insert Into #Shares values(1,2,100,'10:00 AM',5);
Insert Into #Shares values(1,2,50,'11:00 AM',6);
Insert Into #Shares values(1,3,112,'10:00 AM',5);
Insert Into #Shares values(1,4,112,'10:00 AM',5);

Select
PortfolioId
,ShareId
,TimePurchased
,Sum(NoOfShares)
,Sum(SharesPerPrice)
,Sum(NoOfShares * SharesPerPrice)
from
#Shares
Group By PortfolioId, ShareId, TimePurchased With Rollup

OUTPUT:

PortfolioId ShareId TimePurchased NoOfShares SharesPerPrice Totalvalue
-- -- - -- -- --
1 1 10:00 AM 10 30 300
1 1 NULL 10 30 300
1 2 10:00 AM 100 5 500
1 2 11:00 AM 50 6 300
1 2 NULL 150 11 800
1 3 10:00 AM 112 5 560
1 3 NULL 112 5 560
1 4 10:00 AM 112 5 560
1 4 NULL 112 5 560
1 NULL NULL 384 51 2220
NULL NULL NULL 384 51 2220

If TimePurchased is NOT NULL then it is Down Level Data
If TimePurchased is NULL and ShareId is not null then it is One level Collopsed from the Down Level
If TimePurchased is NULL and ShareId is NULL and PortfolioId is not NULL then it is at Portfolio Level

|||

OK - that looks good, but how would I then populate my report from this ? I expected I'd return three tables, one for each level.

Thanks for helping...

|||

Here you can change the query as follow as,

Select
PortfolioId
,ShareId
,TimePurchased
,SUM(NoOfShares) NoOfShares
,SUM(SharesPerPrice) SharesPerPrice
,Sum(NoOfShares * SharesPerPrice) TotalValue
INTO #RESULT
from
Shares
Group By PortfolioId, ShareId, TimePurchased wITH ROLLUP
Select PortfolioId,ShareId,TimePurchased,NoOfShares,SharesPerPrice,TotalValue from #RESULT where TimePurchased is not null

Select PortfolioId,ShareId,NoOfShares,SharesPerPrice,TotalValue from #RESULT where TimePurchased is Null And ShareId is NOT NULL

Select PortfolioId,NoOfShares,SharesPerPrice,TotalValue from #RESULT where TimePurchased is Null And ShareId is NULL And PortfolioId is Not Null