Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-04-29 : 22:54:48
|
Brent writes "It's quite possible that this query is a simple one, but not for me. I'm trying to produce a single query that will do most of my work for the ASP page, and I'm having trouble conceptualizing AND getting subtotal data out of it. I have a series of nested queries, one of which should provide a total value of accounts grouped by "containers" (containers hold accounts of a similar type -- IRAs, for example -- at different institutions). Here's the query so far:
SELECT DISTINCTROW Container.ContainerID, Container.Owner, Container.RetType, Securities.*, Accounts.NameID, Accounts.AccountID, (SELECT SUM(Amount) FROM Posting WHERE SecurityID=Securities.SecurityID AND Transaction LIKE 'Invest*' AND AccountID=Accounts.AccountID AND TradeDate<=#04/29/2001#) AS TotalInvested, (SELECT SUM(Amount) FROM Posting WHERE AccountID=Accounts.AccountID AND SecurityID=Securities.SecurityID AND Transaction='Withdrawal' AND TradeDate<=#04/29/2001#) AS TotalWithdrawn, (SELECT Sum(Shares) FROM Posting WHERE AccountID=Accounts.AccountID AND SecurityID=Securities.SecurityID AND TradeDate<=#04/29/2001#) AS TotalShares, Accounts.[Account Number], (SELECT TOP 1 SecurityPrices.PriceDate FROM SecurityPrices LEFT JOIN Securities ON SecurityPrices.SecurityID = Securities.SecurityID WHERE SecurityPrices.SecurityID=Posting.SecurityID AND SecurityPrices.PriceDate<=#04/29/2001# ORDER BY SecurityPrices.PriceDate DESC) AS PDate, (SELECT TOP 1 SecurityPrices.Price FROM SecurityPrices LEFT JOIN Securities ON SecurityPrices.SecurityID = Securities.SecurityID WHERE SecurityPrices.SecurityID=Posting.SecurityID AND SecurityPrices.PriceDate<=#04/29/2001# ORDER BY SecurityPrices.PriceDate DESC) AS SecPrice, [SecPrice]*[TotalShares] AS [Value], Securities.SecurityID,
(SELECT Sum(qryTotalValue.TotalShares * qryTotalValue.SecPrice) AS subTotalValue FROM (SELECT DISTINCTROW Accounts.NameID, Accounts.AccountID, (SELECT Sum(Shares) FROM Posting WHERE AccountID=Accounts.AccountID AND SecurityID=Securities.SecurityID AND TradeDate<=#04/29/2001#) AS TotalShares, (SELECT TOP 1 SecurityPrices.Price FROM SecurityPrices LEFT JOIN Securities ON SecurityPrices.SecurityID = Securities.SecurityID WHERE SecurityPrices.SecurityID=Posting.SecurityID AND SecurityPrices.PriceDate<=#04/29/2001# ORDER BY SecurityPrices.PriceDate DESC) AS SecPrice, Securities.SecurityID FROM (Accounts INNER JOIN [Container] ON Accounts.ContainerID = Container.ContainerID) INNER JOIN ((Posting INNER JOIN Securities ON Posting.SecurityID = Securities.SecurityID) INNER JOIN SecurityPrices ON Securities.SecurityID = SecurityPrices.SecurityID) ON Accounts.AccountID = Posting.AccountID WHERE (((Accounts.NameID)=1))) AS qryTotalValue) AS TotalValue,
--------------------->Problem begins here
(SELECT Sum(qryTotalValue.TotalShares*qryTotalValue.SecPrice) AS subContainerValue
FROM (SELECT DISTINCTROW Accounts.NameID, Accounts.AccountID, Accounts.ContainerID, (SELECT Sum(Shares) FROM Posting WHERE AccountID=Accounts.AccountID AND SecurityID=Securities.SecurityID AND TradeDate<=#04/29/2001#) AS TotalShares, (SELECT TOP 1 SecurityPrices.Price FROM SecurityPrices LEFT JOIN Securities ON SecurityPrices.SecurityID = Securities.SecurityID WHERE SecurityPrices.SecurityID=Posting.SecurityID AND SecurityPrices.PriceDate<=#04/29/2001# ORDER BY SecurityPrices.PriceDate DESC) AS SecPrice, Securities.SecurityID FROM (Accounts INNER JOIN [Container] ON Accounts.ContainerID = Container.ContainerID) INNER JOIN ((Posting INNER JOIN Securities ON Posting.SecurityID = Securities.SecurityID) INNER JOIN SecurityPrices ON Securities.SecurityID = SecurityPrices.SecurityID) ON Accounts.AccountID = Posting.AccountID WHERE Accounts.NameID=1) AS qryTotalValue INNER JOIN [Container] ON qryTotalValue.ContainerID = Container.ContainerID
WHERE Container.ContainerID=Accounts.ContainerID) AS ContainerValue
-------------------------------->Problem ends here
FROM ((" |
|
|
|
|
|
|
|
Multiple SubSelects, Subtotals, Totals in One Query