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:
>
> 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 al
l
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 pri
ce,
> 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 (addi
ng
> 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 asql

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

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?
Maury
Maury,
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:
>
> 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

Derived Column return WRONG datatype.

Hi, friend,
l've 2 variables A & B from source database which i used to calculate C with the following formula, i.e.

C = 100 * A/B

In case of B = 0 (DIV BY ZERO), C should be equal to -9999.

l've set the datatype for A & B are INT, C is FLOAT at targetting database.

So l used to derived column to calculate C as,
B == 0 ? -9999 : 100 * A/B

After l run the package, l realize that all my C is INTEGER rather than FLOAT.....it seems that SQL server has evaluate the wrong datatype for me....anyway to overcome this?

This should not be a problem - you can map an integer in the data flow to a float at the destination without any issues.

Donald Farmer

|||Fact is, l get a Integer at the end :(, even the data and meta data is float........
|||

Try casting A and B as floats before using them in your calculation.

-Jamie

Deploying Jet OleDb Provider

If I'm posting to the wrong forum, please correct me.

I'd like to use an Access database for an application I'm developing for retail sale, and I need to do these things:

Determine during setup if the target machine has the Jet OleDb Provider installed. If so, determine whether the version is adequate. If necessary, install the correct provider.

I don't seem to be able to find any documentation or downloads covering this scenario, however. This seems odd, since I would have imagined the scenario to be rather common.

Has anyone had any successful experience with these issues?

Thanks

Hi,

if you are having a installer package, you can query the registry for the MDAC key which is described and worked for me in many cases here:

http://support.microsoft.com/?kbid=301202

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

That looks good, thanks.

Would you happen to know where I can get a redistributable Jet provider? I'm looking here:

http://msdn.microsoft.com/data/mdac/downloads/

But all of those are user-initiated setups. You're correct—I'm using an installer.

|||Hi there,

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257604

HTH, Jens Suessmeyer.

PS: The one included in VS is only to check the existence of MDAC.

http://www.sqlserver2005.de
|||

Ah, you're the greatest!

|||:-D Nice to hear that. :-D

-Jens.|||

Say, tell me... did you look those articles up specifically for this thread, or did you already have them on hand.

The reason I ask is because of the cumbersome task of sifting through the KB. I know I probably shouldn't, but lately I've pretty much given up on trying to find anything useful there among the myriad of articles that don't apply to the issue I'm looking for.

You must be pretty good at it.

|||Thats something in the middle, for the most cases I ran into the same problems / issues like the posters. Knowing the *right* keywords from my experience its easy to find the right articles on the internet.

-jens.|||

Ah, I see. Makes sense.

Tell me—since clearly you've already fought this battle—do you have your programs install MDAC in order to support Access, or do you instead opt for the OleDb provider?

The reason I ask is, according to my research on the subject, MDAC stopped including it after version 2.5. In fact, the second KB article you referred me to (thanks again) discusses MDAC only, and not OleDb.

That said, have you even been able to find a silent install for the standalone OleDb provider?

Thanks.

Deploy Solution brings up a login box

Login box shows a server ashttp://localhost/reports (could be wrong, I don't kow but that's what I put in the property for that)

Then it wants a user name and password. I've tried a few logons but it doesn't accept them.

What is the problem here?

Disregard. reports needs to be reportserver.