derived table fails, I'm doing something wrong...

I am trying to join a list of stocks with a list of (optional) prices,
getting the newest price. I am using a derived table to get the latest price,
but when I join, I always get NULL even though there are prices...
SELECT a.AccountID, a.code, pr.price1, pr.pricedate
FROM tblAccounts a
LEFT JOIN (SELECT TOP 1 accountId, price1, pricedate, pricenote FROM
tblPrices WHERE pricedate <= GETDATE() ORDER BY pricedate desc) pr ON
pr.accountId=a.accountid
ORDER BY a.code
If you run the inner query (the derived table) for a given accountId (adding
it to the where) it works fine, returning the correct row from the prices
table. But when used as above, it always returns null for pr.
Any ideas?
MauryMaury,
The problem is that you are asking for the TOP 1 record, thus getting only
one record. Then you are comparing the pr.accountID of that one record with
a.accountID. So, there could be some rows joining, but all the other rows
from tblAccounts would not find a match.
I think you are probably try to do something like this (untested) code:
SELECT a.AccountID, a.code, pr.price1, pr.pricedate
FROM tblAccounts a
LEFT JOIN
(SELECT accountId, price1, pricedate, pricenote
FROM tblPrices tp1
JOIN (SELECT accountId, MAX(pricedate)
FROM tblPrices
WHERE pricedate <= GETDATE( )
GROUP BY accountID) AS tp2
ON tp1.accountID = tp2.accountID
AND tp1.pricedate = tp2.pricedate) AS pr
ON pr.accountId=a.accountid
ORDER BY a.code
If I did not blow this, what it is trying to do is:
tp2 = Give me the accountId and maximum pricedate foroevery account
pr = Join tblPrices to tp2 to return all information that matches the
maximum pricedate for each accountId
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:0F47DDED-8044-4F41-BBDE-C5734329D153@.microsoft.com...
>I am trying to join a list of stocks with a list of (optional) prices,
> getting the newest price. I am using a derived table to get the latest
> price,
> but when I join, I always get NULL even though there are prices...
> SELECT a.AccountID, a.code, pr.price1, pr.pricedate
> FROM tblAccounts a
> LEFT JOIN (SELECT TOP 1 accountId, price1, pricedate, pricenote FROM
> tblPrices WHERE pricedate <= GETDATE() ORDER BY pricedate desc) pr ON
> pr.accountId=a.accountid
> ORDER BY a.code
> If you run the inner query (the derived table) for a given accountId
> (adding
> it to the where) it works fine, returning the correct row from the prices
> table. But when used as above, it always returns null for pr.
> Any ideas?
> Maury|||"Russell Fields" wrote:
> I think you are probably try to do something like this (untested) code:
This is how I did it the first time actually, but the performance was
terrible.
You know, it's about time the SQL vendors added some syntax for time series!
Is it too much to ask for NEWEST and OLDEST?
Maury|||Maury,
I feel your pain, but the right answer is better than a fast answer. (Sort
of :-)) One thing that we did not discuss is indexes and how they may help
such a query. Having said that, however, what I would probably do is create
a temporary table with the innermost tp2 select, something like:
CREATE #Temp
(accountId int,
maxpricedate datetime)
INSERT INTO #temp
SELECT accountId, MAX(pricedate)
FROM tblPrices
WHERE pricedate <= GETDATE( )
GROUP BY accountID
-- Then use the temp table in the last select.
SELECT a.AccountID, a.code, pr.price1, pr.pricedate
FROM tblAccounts a
LEFT JOIN #temp t
ON a.accountId = t.accountId
JOIN tblPrices pr
ON t.accountId = pr.accountId
AND t.pricedate = pr.pricedate
ORDER BY a.code
Put an index on accountId in #temp and so forth.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6A1230A2-19DC-4D1C-A297-DC4A56C1E149@.microsoft.com...
> "Russell Fields" wrote:
>> I think you are probably try to do something like this (untested) code:
> This is how I did it the first time actually, but the performance was
> terrible.
> You know, it's about time the SQL vendors added some syntax for time
> series!
> Is it too much to ask for NEWEST and OLDEST?
> Maury
>|||You know, I think what I should really do is create a non-temp table called
tblCurrentPrices and keep the "latest" version in there. This sort of thing
is spread all over my code, I could probably make my whole app run faster all
over the place...
Wait, I even have a table to put it in already. Ignore me, I'm rambling.|||On Jul 6, 8:26 pm, Maury Markowitz
<MauryMarkow...@.discussions.microsoft.com> wrote:
> I am trying to join a list of stocks with a list of (optional) prices,
> getting the newest price. I am using a derived table to get the latest price,
> but when I join, I always get NULL even though there are prices...
> SELECT a.AccountID, a.code, pr.price1, pr.pricedate
> FROM tblAccounts a
> LEFT JOIN (SELECT TOP 1 accountId, price1, pricedate, pricenote FROM
> tblPrices WHERE pricedate <= GETDATE() ORDER BY pricedate desc) pr ON
> pr.accountId=a.accountid
> ORDER BY a.code
> If you run the inner query (the derived table) for a given accountId (adding
> it to the where) it works fine, returning the correct row from the prices
> table. But when used as above, it always returns null for pr.
> Any ideas?
> Maury
Hi, I made made slight changes to your qiery:
Select a.AccountID, a.code, price1 = (SELECT TOP 1 price1 FROM
tblPrices pr WHERE pr.accountId=a.accountid and
pricedate <= GETDATE() ORDER BY pricedate desc),
pricedate = (SELECT TOP 1 pricedate FROM tblPrices WHERE
pr.accountId=a.accountid and
pricedate <= GETDATE() ORDER BY pricedate desc)
from tblAccounts a