Comparing Value Ranges Between 2 Tables

oracle, oracle10g, sql

Solution

In my opinion, the easiest way is if you have a min and max purchase amounts:

select rebate_percentage, min_purchase,
       (lead(min_purchase, 1) over (order by min_purchase) - 1) as max_purchase
from rebates

Then you can do a simple between join, where the join condition looks like:

on totalorders between rebates.min_purchase and rebates.max_purchase

You can handle the final case (with NULLs) with a modified join condition:

on totalorders >= rebates.min_purchase and
    (totalorders <= rebates.max_purchase or rebates.max_purchase is null)

Or, alternatively, by changing the original logic to have a coalesce() on the lead function with some very large value.

Problem

I have an oracle 10g database that has 2 tables: a REBATES table, and an ORDERS table. The REBATES table looks sort of like this: ``` | rebate_percentage | min_purchase | ------------------------------------ | 1.0 | 5000 | | 1.5 | 7000 | | 2.0 | 11000 | | 5.0 | 20000 | ``` I'm trying to determine the rebate percentage to apply, based on total orders. I know how to find the sum of all orders for a particular customer, for a particular time range, but how do I also grab the rebate percentage, all in one query? For example, if the order total is 16,000 then how can I construct a query that takes this value, compares it against the REBATES table, and returns 2.0?

Original source