SELECT MAX statement

[Brent]

The database has four tables (-> = fields):

Account -> AccountID, ClientID, (others)
Security -> SecurityID, AccountID, (others)
SecurityTransactions -> SecurityID, Shares, (others)
SecurityPrices -> SecurityID, PriceDate, Price, (others)

Account and Security are connected by the AccountID key. Security,
SecurityTransactions, and SecurityPrices are connected by the SecurityID
key.

I'm looking to write a SQL statement that will supply the total value of a
client's holdings on a particular day -- as simply as possible.

I've achieved the result using a series of views, but it's slow. I was
hoping someone would help me out with a SQL statement something like this:

SELECT SUM(SecurityTransactions.Shares *
(SELECT MAX(PriceDate) FROM SecurityPrices x WHERE x.PriceDate <=
'4/2/2004' AND x.SecurityID = SecurityPrices.SecurityID) AS
LastSecurityPrice) AS TotalValue
FROM SecurityTransactions INNER JOIN
Account ON
SecurityTransactions.AccountID = Account.AccountID INNER JOIN
SecurityPrices ON
SecurityTransactions.SecurityID = SecurityPrices.SecurityID
WHERE (Accounts.ClientID = 1)

This statement fails at "AS".

Thanks for any help!

--Brent

[John Bell]

Hi

As you don't post real DDL we don't know what the indexes PKs etc are. You
may want to see what the Index Tuning Wizard suggests or view the query
plan.

John

"Brent" <bbigler @ ahem-'whoopee' . com> wrote in message
news:M0*****************@newssvr25.news.prodigy.co m...

The database has four tables (-> = fields):

Account -> AccountID, ClientID, (others)
Security -> SecurityID, AccountID, (others)
SecurityTransactions -> SecurityID, Shares, (others)
SecurityPrices -> SecurityID, PriceDate, Price, (others)

Account and Security are connected by the AccountID key. Security,
SecurityTransactions, and SecurityPrices are connected by the SecurityID
key.

I'm looking to write a SQL statement that will supply the total value of a
client's holdings on a particular day -- as simply as possible.

I've achieved the result using a series of views, but it's slow. I was
hoping someone would help me out with a SQL statement something like this:

SELECT SUM(SecurityTransactions.Shares *
(SELECT MAX(PriceDate) FROM SecurityPrices x WHERE x.PriceDate <=
'4/2/2004' AND x.SecurityID = SecurityPrices.SecurityID) AS
LastSecurityPrice) AS TotalValue
FROM SecurityTransactions INNER JOIN
Account ON
SecurityTransactions.AccountID = Account.AccountID INNER JOIN
SecurityPrices ON
SecurityTransactions.SecurityID = SecurityPrices.SecurityID
WHERE (Accounts.ClientID = 1)

This statement fails at "AS".

Thanks for any help!

--Brent

[Hugo Kornelis]

On Fri, 02 Apr 2004 22:43:56 GMT, "Brent" <bbigler @ ahem-'whoopee' .
com> wrote:

The database has four tables (-> = fields):

Account -> AccountID, ClientID, (others)
Security -> SecurityID, AccountID, (others)
SecurityTransactions -> SecurityID, Shares, (others)
SecurityPrices -> SecurityID, PriceDate, Price, (others)

Account and Security are connected by the AccountID key. Security,
SecurityTransactions, and SecurityPrices are connected by the SecurityID
key.

I'm looking to write a SQL statement that will supply the total value of a
client's holdings on a particular day -- as simply as possible.

I've achieved the result using a series of views, but it's slow. I was
hoping someone would help me out with a SQL statement something like this:

SELECT SUM(SecurityTransactions.Shares *
(SELECT MAX(PriceDate) FROM SecurityPrices x WHERE x.PriceDate <=
'4/2/2004' AND x.SecurityID = SecurityPrices.SecurityID) AS
LastSecurityPrice) AS TotalValue
FROM SecurityTransactions INNER JOIN
Account ON
SecurityTransactions.AccountID = Account.AccountID INNER JOIN
SecurityPrices ON
SecurityTransactions.SecurityID = SecurityPrices.SecurityID
WHERE (Accounts.ClientID = 1)

This statement fails at "AS".

Thanks for any help!

--Brent

As John says, post the table structure in the form as DDL and some
sample data as inserts, so that we can copy&paste it into Query
Analyser and run some tests.

However, if I don't misread your query, you should at the very least
remove "AS LastSecurityPrice". Then, your query will be syntactically
correct. If it does what you want it to is on another card.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)