Re: Outer join based on range?

Damien Jones wrote:
>
> Can you do an outer join based on a range (><) rather than an equality?
>
> I need to display a list of ordered products and prices (if available) for
> a date range.
> Products cost different amounts during different time periods.
>
> My price reference table is keyed on product and date range.
> example: product-1; from-3/1/97; to-3/30/97; price-$100
> product-1; from-4/1/97; to-4/31/97; price-$125
> product-2; from-3/1/97; to-3/31/97; price-$450
> ... (thousands of records here)
>
> My order table lists products and dates ordered.
> example: product 1; date-3/15/97
> product 2; date-5/15/97
> ... (note: there will be substantially more than 2 records here)
>
> My query needs to return all products in the order, and prices for the
> products with matching records in the price reference table:
> example: product 1; date-3/15/97; price-$100
> product 2; date-5/15/97; price-(null)
> ...
>
> The only other ideas I can think of involve finding all the products WITH
> prices, using this as a NOT IN query to find products WITHOUT prices, then
> UNIONing these results with another instance of the first query (all
> products WITH prices)
> OR
> Looping through a list of all products and doing a separate query for each
> product.
>
> Since this is a query that will populate a dynamic Web Server page,
> performance is an important issue. The tables accessed are already on slow,
> foreign databases (via DBLINKS), and I believe the last two approaches may
> be speed-prohibitive.
>
> If you have any ideas, they would be greatly appreciated.

I would implement this via a distinct product/date column. So in your product reference table you would have:

Product Start Date End Date Price Date Prod. Code

---------  ----------   --------   -----    -----------------
product-1    3/1/97     3/30/97    $100     19970301product-1
product-1    4/1/97     4/31/97    $125     19970401product-1
product-2    3/1/97     3/31/97    $450     19970301product-2

(also I would use a numeric ID for my products rather than names, but it works both ways)

Then as a product is ordered, you can get the Date Prod. Code from your reference table and insert it there:

Product Date Prod. Code Order Date

-------    -----------------   ----------
product-1  19970301product-1   3/12/97
product-2  -NULL-              5/15/97

This will allow you to form a simple join relationship with your pricing reference table. Note, I would not recommend allowing orders to be entered for which no pricing can be identified as in your example. One way to ensure this would be to ensure that all dates for every product would result in a price. You could do this by making the end date on the latest pricing something like 12/31/9999 and the start date on the oldest pricing 01/01/0. Do this for every product and the above will work fine and fast if you index on the right columns. When a product has a new pricing established, update the end date of the previous latest pricing to be the second before the start date of the new pricing.

• TRW -- ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| | Tim Witort || He is not silent. He is not | | trw_at_medicalert.org || whispering. We are not quiet | | || we are not listening. | | Pin: TZ, only one :^( || - Out of the Grey | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||