Find the recent available data based on the dates (1 Viewer)

[Arvidsson]

Hello,

I already asked this question in my last thread, but unfortunately it was overlooked because the topic question was solved.
Anyway, sorry for double posting.

I have a price table:

tblPrice
PosNr  PriceDate          Company  Price
1       01.01.2014       Firma A       5                                      
2       02.01.2014       Firma A       7                                   
3       03.01.2014       Firma A       9            
4       04.01.2014       Firma A       8            
5       06.01.2014       Firma A       6            
6       02.01.2014       Firma XY     11                                      
7       03.01.2014       Firma XY      9                                  
8       04.01.2014       Firma XY      7           
9       05.01.2014       Firma XY      8          
10      06.01.2014       Firma XY      10

And I have a table with the dates, for which I need a price.

tblDates
PosNr  PriceDate          Company    
1       01.01.2014       Firma A                                             
2       02.01.2014       Firma A                                          
3       03.01.2014       Firma A                   
4       04.01.2014       Firma A
5       05.01.2014       Firma A (no price available)           
6       06.01.2014       Firma A                   
7       02.01.2014       Firma XY                                           
8       03.01.2014       Firma XY                                        
9       04.01.2014       Firma XY                 
10      05.01.2014       Firma XY                
11      06.01.2014       Firma XY

And now I want to combine this tables, and for the dates which have no price, the last price should be taken.

tblResult    
PosNr  PriceDate          Company  Price
1       01.01.2014       Firma A       5                                      
2       02.01.2014       Firma A       7                                   
3       03.01.2014       Firma A       9            
4       04.01.2014       Firma A       8 
5       05.01.2014       Firma A       8 (actualy no priceavailable, so take last price)
6       06.01.2014       Firma A       6            
7       02.01.2014       Firma XY     11                                      
8       03.01.2014       Firma XY      9                                  
9       04.01.2014       Firma XY      7           
10      05.01.2014       Firma XY      8          
11      06.01.2014       Firma XY      10

Do you habe any idea how I can get this?

I have this code, but it need hours.

SELECT tblDates.PosNr, tblDates.Company, tblDates.PriceDate, (SELECT TOP 1
          B.Price
      FROM
          tblPrices As B
      WHERE
          B.Company = tblDates.Company
          AND
          B.PriceDate <= tblDates.PriceDate
      ORDER BY
          B.PriceDates  DESC                   ) AS Price
FROM tblPrices RIGHT JOIN tblDates ON (tblPrices.PriceDate = tblDates.PriceDates) AND (tblPrices.Company = tblDates.Company);

I would be very glad if someone has an idea.

Thank you very much in advance.

Magnus

 

[CJ_London]

try this

SELECT tblDates.*, tblPrice.Price 
FROM tblDates INNER JOIN tblPrice ON tblDates.Company = tblPrice.Company
WHERE tblPrice.PriceDate=
    (SELECT Max(PriceDate) FROM tblPrice as Tmp WHERE PriceDate<=tblDates.PriceDate)

 

[Arvidsson]

Thank you for your support.

Unfortunately I get this result:

PosNr	PriceDate	Company	        Price
1	01.01.2014	Firma A	        5
2	02.01.2014	Firma A	        7
3	03.01.2014	Firma A	        9
4	04.01.2014	Firma A	        8
6	06.01.2014	Firma A	        6
7	02.01.2014	Firma XY	11
8	03.01.2014	Firma XY	9
9	04.01.2014	Firma XY	7
10	05.01.2014	Firma XY	8
11	06.01.2014	Firma XY	10

This Line is missing

05.01.2014       Firma A       8

Do you think this code is fast enough for 220,000 data points? (I set indices on company and price date)

 

[CJ_London]

This Line is missing

It shouldn't be, can you post your interpretation of the SQL - it sounds like you may have missed off the alias in the subquery or have also joined on pricedate

Do you think this code is fast enough for 220,000 data points?

I believe it's the fastest option but I would ask why you actually want a query with that number of data points - in normal usage wouldn't you be filtering down to a smaller range of companies and/or dates?

 

[Arvidsson]

Hmm...i build test tables with the content from my first post.

tblDates
PosNr As a number
PriceDate As a date
Company As a short text

tblPrice
PriceDate As a date
Company As a short text
Price As a number

And i used 1:1 your code

SELECT tblDates.*, tblPrice.Price
FROM tblDates INNER JOIN tblPrice ON tblDates.Company = tblPrice.Company
WHERE tblPrice.PriceDate=
    (SELECT Max(PriceDate) FROM tblPrice as Tmp WHERE PriceDate<=tblDates.PriceDate);

In the daily usage you are right, but if I want to backtest something I have to carry out this query for the whole database. Its ok if the query takes e.g. 10 minutes, but 7 hours (as my code) is a bit too long

 

[CJ_London]

Sorry, missed a bit in the subquery (in red)

SELECT tblDates.*, tblPrice.Price
FROM tblDates INNER JOIN tblPrice ON tblDates.Company = tblPrice.Company
WHERE tblPrice.PriceDate=
    (SELECT Max(PriceDate) FROM tblPrice as Tmp WHERE PriceDate<=tblDates.PriceDate [COLOR=red]AND Company=tblDates.Company[/COLOR])

Don't know about 10 minutes but some time ago I was asked to improve db performance for a query which was running overnight (circa 1.5m records) due to the time it took - changed the (insert) query to something similar to this and the time was reduced to about 30 mins - still time to go get a coffee!

You'll need to run it to see.

If tables are linked to a BE on the network, this will slow things down but if it is an occasional 'one off' for backtesting, you could always temporarily copy the BE to your local drive

 

[Arvidsson]

Thank you very much CJ_London!

I get the correct result.
Now I will test it on the "big" database.

 

[Arvidsson]

It is running now approximately 30 minutes.

In the bottom on the left side appears a info:

This recordset cant be updated.

(German: Dieses Recordset kann nicht aktualisiert werden)

I think this is not normal, or?

 

[CJ_London]

Not for a select query since nothing is being updated.

Is your data source is SQL server or similar by any chance? It shouldn't matter for read only but just to be sure, have you identified an appropriate index for each table?

 

[Arvidsson]

The data source is another Access DB (BackEnd). The two tables are linked to the FrontEnd Access DB.

This are the two tables:

tblDates
PosNr As a number (Index, without duplicates)
PriceDate As a date (Index, with duplicates)
Company As a short text (Index, with duplicates)

tblPrice
Company As a short text (Index, with duplicates)
PriceDate As a date (Index, with duplicates)
Price As a number
...
(this table has 4 more fields (numbers, without index), but this should not be a problem)

 

[CJ_London]

Sorry, I think I interpreted your post incorrectly. I though you were saying the query had stopped with an error

This select query is not updateable, so the message is correct. I'm just not used to seeing the message until you try to make a change. It might be to do with the version of Access (I use 2010).

Assuming your navigation bar is visible at the bottom, if it is just showing a single number, then the query is still running - and you should see a progress bar to the bottom right. If the query has finished the navigation bar will be showing something like '1 of 220000'

 

[Arvidsson]

Yes, the query is still running.

The navigation bar is in this query not visible (in other queries i see the "single number" and the first results).

On the bottom right side I see only "Query is running" (German: Abfrage wird ausgeführt), but no progress bar.

I am using Access 2013 64bit.

 

[vbaInet]

CJ, I wonder if there's a problem with the table. Why was there no price in the first place?

I don't want to interfere, just throwing some ideas. You can try it with IN() with your subquery in the IN. Or flip your date field (using DESC), use TOP 1 and do a join.

 

[CJ_London]

Regret I don't use 2013 as yet but that sounds like it is just information. I'm surprised there is not a progress bar.

No idea why you see the navigation bar in other queries but not this one.

A few suggestions.

Try converting the text names to numeric (indexed, duplicates OK) and join on those instead. A numeric index is typically quicker than a text one

As an experiment, try filtering the tblDates table for just one company and 'grow' from there

Is the date just a date? or is it Date/Time? If the latter then strip out the time element from both tables (unless this will give you duplicate dates)

if the backend is on the LAN trying moving a copy to your local drive and link to the copy instead

 

[Arvidsson]

Ok, I am trying to convert the text (company) into a number.

Concerning the other questions:
It is just a date and the backend DB is on my deskop computer.

Could the proposal of vbaInet (flip your date field (using DESC), use TOP 1 and do a join) speed up the query? But I dont know how I should implement this in the sql code.

 

[CJ_London]

don't want to interfere

Please do!, more ideas the better

Why was there no price in the first place?

The OP can answer that one but I would guess the price table is only updated when there is a change in price

The IN is a thought, I've always assumed that = is a faster comparison than IN but no harm in trying - MS often use IN when referencing subqueries.

Not sure about TOP 1 - you are adding a sort action into the subquery which will have an effect on performance, but then I guess Max is having to do some additional work anyway

So a couple of permutations of the query criteria

WHERE tblPrice.PriceDate IN
    (SELECT Max(PriceDate) FROM tblPrice as Tmp WHERE PriceDate<=tblDates.PriceDate AND Company=tblDates.Company)

WHERE tblPrice.PriceDate IN
    (SELECT TOP 1 PriceDate FROM tblPrice as Tmp WHERE PriceDate<=tblDates.PriceDate AND Company=tblDates.Company ORDER BY PriceDate DESC)

 

[vbaInet]

I just didn't want to confuse matters here since you're on it

It should be generally faster but sometime's it's not a straight rule that "=" would be faster than "IN". It depends on how much data you're pulling from the LEFT side and the RIGHT side. In this case we're pulling 1 record from the right side so IN or = should perform relatively the same. The query optimiser knows best anyway

You can also try the 2nd permutation with the =.

 

[Arvidsson]

I have changed "company" (short text) to a "company_id" (number).
Additional I switched to a query which creates a new table. Now I see the the progress bar.

The query runs now for 30 minutes and the progress bar is only filled for maybe 5%.

That is very unfortunate.

 

[vbaInet]

You've not reported on the outcomes of the other suggestions posed.

 

[Arvidsson]

Sry, the three options (code1, code2, code2 with "=" ) give the same (correct) result.

Currently code2 with the company_ID is running.
But even after >40 min I get no result. And the progress bar is not really growing.