Applicable price on the right date

[Elielson via AccessMonster.com]

Hello

I wonder if anyone can help please.

Prices of products have an effective (valid from) date.

I.e. a 1kg of Rice cost £2 from 20/09/07; £1.80 from the 25/09/07, etc.

I have two tables:

a) tbl_invoices with the fields
InvNumber, Date; Product;
Eg. Data corresponding:
1 / 10/10/07 / a
1 / 10/10/07 / b
2 / 12/10/07 / a
3 / 17/10/07 / a

b) tbl_prices
Product; Valid_from; Price

E.g data:
a / 09/10/07 / 09
a / 11/10/07 / 11
b / 01/10/07 / 2
a / 01/10/07 / 5
a / 15/10/07 / 13


What is the query to pull the right price for those invoices please. Thanks.

 

[Michel Walsh]

SELECT a.invNumber, b.price

FROM (invoices AS a INNER JOIN prices AS b
ON a.product = b.product AND a.date > b.date)
INNER JOIN prices AS c
ON a.product = c.product AND a.date > c.date

GROUP BY a.invNumber, a.product, a.date, b.date

HAVING b.date=MAX(c.date)




(technically, I am inclined to think the GROUP BY clause can be : GROUP BY
a.invNumber, b.date
but it would relay on the fact that each given couple { product, date } as
a distinct invNumber, and vice-versa. Repeating product and date in addition
to invNumber in the group by clause is the redundant,... but it may help to
understand what is going on).


Assuming there is only one product (for simplicity). Aliases b and c refer
to all prices with a data tag less than the date in the alias a. Clearly
only the latest date, max(c.date) has to be considered, and from all the
possible matching pairs { a.date, b.date } we keep only those having
b.date= c.date.



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Should be:

SELECT a.invNumber, LAST(b.price)
FROM ...

SELECT a.invNumber, b.price

FROM (invoices AS a INNER JOIN prices AS b
ON a.product = b.product AND a.date > b.date)
INNER JOIN prices AS c
ON a.product = c.product AND a.date > c.date

GROUP BY a.invNumber, a.product, a.date, b.date

HAVING b.date=MAX(c.date)




(technically, I am inclined to think the GROUP BY clause can be : GROUP
BY a.invNumber, b.date
but it would relay on the fact that each given couple { product, date }
as a distinct invNumber, and vice-versa. Repeating product and date in
addition to invNumber in the group by clause is the redundant,... but it
may help to understand what is going on).


Assuming there is only one product (for simplicity). Aliases b and c refer
to all prices with a data tag less than the date in the alias a. Clearly
only the latest date, max(c.date) has to be considered, and from all the
possible matching pairs { a.date, b.date } we keep only those having
b.date= c.date.



Hoping it may help,
Vanderghast, Access MVP

 

[elielson via AccessMonster.com]

Thank you. I am a begginner so I am looking at your comments. Many thanks
indeed!


elielson

Should be:

SELECT a.invNumber, LAST(b.price)
FROM ...

SELECT a.invNumber, b.price

[quoted text clipped - 53 lines]

 

[elielson via AccessMonster.com]

Hello thank you for your replies.

I run the queries and it's missing some entries. If the product appears not
in ascending order by date in the table invoice

Date Product
15/10/07 b
11/10/07 b

The query 'misses' product 'b' on the 11/10/07.

thank you.

Thank you. I am a begginner so I am looking at your comments. Many thanks
indeed!

elielson

Should be:

[quoted text clipped - 6 lines]

 

[Michel Walsh]

I cannot reproduce the problem here.

Here are the tables I used ( date format is yyyy.mm.dd ):

prices product date price
1 2005.01.01 0
1 2002.01.01 10
1 2007.01.01 20



note that the price for 2002 is between 2005 and 2007. I also include an invoice in 2006 after an invoice in 2007, in my table of invoices, just in case:

Invoices invNumber product date
1 1 2004.06.06
2 1 2005.06.06
3 1 2007.06.06
4 1 2006.06.06


and the query

SELECT a.invNumber, LAST( b.price)
FROM (invoices AS a INNER JOIN prices AS b
ON a.product = b.product AND a.date > b.date)
INNER JOIN prices AS c
ON a.product = c.product AND a.date > c.date
GROUP BY a.invNumber, a.product, a.date, b.date
HAVING b.date=MAX(c.date)


returns what is expected:


Query1 invNumber Expr1001
1 $10.00
2 $0.00
3 $20.00
4 $0.00



I would suspect you have a problem of date format. Are you consistently using mm/dd/yyyy ? or sometimes dd/mm/yyyy too? or a value for the month, greater than 12 (which would indicate, to OLEAUT32 to TRY another format than the one YOU were expecting).
In ANY system, the following would probably return the same date:

? Format( #30/6/2007#, "long date"), Format(#6/30/2007#, "long date")
Saturday, June 30, 2007 Saturday, June 30, 2007

Note that EVEN IF I used, in one case 30/6 and in the other case 6/30, I got the SAME result ! That is because OLEAUT32 TRIES to make sense, when, sometimes, it should NOT.



Vanderghast, Access MVP