Date Range and Invoice Date

[bootu]

I have two tables: A & B

Table A contains sales info: product number, product type, invoice date, and invoice price

Table B contains contract pricing information: It is based on the product number, product type. The contract pricing information is based by product type and specified date ranges:

• I.E Product number 1 with product type 1.A pricing info is $10 from 01/01/2015-10/31/2015 and for the same product number and product type price is $15 from 11/01/2015 to 12/31/2016

How can I write a query that will select the specific contract price based on the invoice date? Any insights is appreciated.

[NTC]

Your product table must have a StartDate field and an EndDate field, along with price, product id, etc, etc.

Then you will rely upon a query of matching Product IDs with the Invoice Date as criteria in being >= StartDate AND <= EndDate

[bootu]

I appreciate the quick response. What do you mean by product table? I have a sales data and contract pricing Info table. The way to connect the two tables will be via product number and product type. Am I missing something here? Thanks again.

[NTC]

to me a table that lists all the products is a product table - but there is no problem in it being called a contract table..... the advice is the same...

[bootu]

Thank you! That worked!