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 - 2007-02-06 : 08:54:45
|
| writes "I was wondering if you could tell me what is wrong with this below code. I have SQL Server 2005 and Windows 2003 Server.SELECT P.FirstName, P.LastName, P.PortfolioID, P.PortfolioDescription, SC.Description, SC.SecurityID, SC.SecurityTypeID, SUM(T.Quantity) AS TQuantity, CASE SC.SecurityTypeID WHEN 11 THEN CAST(SUM(T .Quantity)) WHEN 2 THEN CAST(SUM(T .Quantity * (SELECT Price FROM dbo.SecurityPrices AS SP WITH (Nolock) WHERE T .SymbolID = SP.SecurityID AND PriceDate = '1/24/2007'))) / 100 ELSE CAST(SUM(T .Quantity * (SELECT Price FROM dbo.SecurityPrices AS SP WITH (Nolock) WHERE T .SymbolID = SP.SecurityID AND PriceDate = '1/24/2007'))) END AS Amount, P.AccountNumber, P.AccountTypeDescription, SC.CodeDescription, SC.CodeDescription2, SC.SymbolFROM dbo.Transactions AS T WITH (Nolock) INNER JOIN dbo.Portfolios AS P WITH (Nolock) ON P.PortfolioID = T.PortfolioID INNER JOIN dbo.SFGSecuritiesAndCodes AS SC WITH (Nolock) ON T.SymbolID = SC.SecurityID INNER JOIN dbo.Groups AS G WITH (Nolock) ON P.PortfolioID = G.PortfolioIDWHERE (T.StatusTypeID = 100) AND (G.OwningPortfolioID = 270) AND (T.TradeDate <= '1/24/2007')GROUP BY T.SymbolID, P.FirstName, P.LastName, P.PortfolioID, SC.Description, SC.SecurityID, SC.SecurityTypeID, P.PortfolioDescription, P.AccountNumber, P.AccountTypeDescription, SC.CodeDescription, SC.CodeDescription2, SC.SymbolThank you." |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-02-06 : 09:03:41
|
the use of CAST is wrongit should be CAST(SUM(T.Quantity) as decimal(10,2)) for example
KH |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
|
|
How to use Cast in SUm operation?