Join condition with date ranges

[batchman1974]

I have two tables I need to join to an invoice table.

There are 4 primary keys on the invoice table:
account_num upc sales_id date
10 001005 356 04/01/2009

The other two tables, discount and promotion, describe specific incentives that are in place. The structure of these tables is as follows:

account_num upc sales_id begin_date end_date
10 001005 356 01/01/2009 05/31/2009

I'm trying to compare these feilds based on the date of the sale:

invoice.price, discount.price - promotion.price

My problem is I am unsure of how to join the invoice table given I have a date range rather than a list of dates that a particular promotion/discount would be in place.

Thanks in advance!

 

[SantaMufasa]

Batchman,

How about:

...WHERE tab1.dt between tab2.begin_date and tab2.end_date...

Without more information about your join, that's the best suggestion we can make.

BTW, "date" (in your tab1) would not be a legal column name since "date" is an Oracle keyword.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”