oining on "between x and y"

SQL Apprentice Question
I have these tables:


Sales (SaleId, ProductId, QTY)
Bandwidth (ProductId, RangeFrom, RangeTo, Price)


A RangeTo field is included in the Bandwidth table cause an "open end"
is possible; 1-10, 11-20, 21>


Now i would like to get a table like:


SalesView(SaleId, ProductId, Qty, Price)


Where price has to be NULL if it cannot be matched in the bandwith
table; i always need all the records from the Sales table...


I tried something like this


SELECT s.SaleId, s.ProductId, s.QTY, s.QTY*b.Price AS Price
FROM tblSales s
INNER JOIN tblBandwidth b
ON b.ProductId = s.ProductID
AND
(
s.QTY BETWEEN b.RangeFrom AND b.RangeTo
OR
(s.QTY >= b.RangeFrom AND b.RangeTo IS NULL)
)


With inner join i don't get all sales records (there out of range),
with outer join i get them doubled or more (and i don't want to use a
DISTINCT)


Any great ideas?



Celko Answers

>> A RangeTo field [sic] is included in the Bandwidth table cause an "open end" is possible; 1-10, 11-20, 21 <<


That usually means that the price for that range holds for all
quantities over the from range value. In your example, any ordr over
21 units gets that same unit price. But then you say:


>> price has to be NULL if it cannot be matched in the bandwith table; i always need all the records [sic] from the Sales table... <<


You can use DRI to force all sales to be of actual products you stock,
and for every bandwidth to reference a real product. You should not
have any missing data if you design the schema properly. Here is a
guess at what you want:

CREATE TABLE Sales
(sale_id INTEGER NOT NULL PRIMARY KEY,
product_id INTEGER NOT NULL
REFERENCES Products(product_id),
sold_qty INTEGER NOT NULL
CHECK(sold_qty > 0));


CREATE TABLE Bandwidth -- weird name, why not Discounts?
(product_id INTEGER NOT NULL
REFERENCES Products(product_id),
from_range INTEGER DEFAULT 1 NOT NULL
CHECK (from_range > 0),
to_range INTEGER,
CHECK(from_range <= to_range),
unit_price DECIMAL (10,4) NOT NULL,
PRIMARY KEY (product_id, from_range) );


If you have a single price for an item, then the DEFAULT values will
give you a (1, NULL) row in the Bandwidth table. Hide the work in a
VIEW that will always be current:


CREATE VIEW SalesRevenue(sale_id, product_id, sale_qty, sale_total)
AS
SELECT S.sale_id, S.product_id, S.sale_qty,
S.sale_qty*B.unit_price
FROM Sales AS S, Bandwidth AS B
WHERE B.product_id = S.product_id
AND S.sale_qty BETWEEN
B.from_range AND COALESCE (B.to_range, S.sale_qty);


The COALESCE() will handle any quantity not explicitly in the Bandwidth
table.


SQL Apprentice Question
guess i didn't explain myself in such a good way. I might also used
some strange descriptions like bandwidth, probably cause english is not
my native language. But, let's not blame it on that; in the situation i
have now, referential integrity by the database is not possible, for as
far as i know, mainly because of:


* the actual quantity for 1 line item (i will use this term instead of
sale) is determined by a group of line items (if 1 is updated the whole
lot can switch into another range)
* the bandwidth/ranges/discounts may have a closed range: "1-10, 11-20,
21-30" (dont ask me why, commercial reasons probably)
* the line items are gathered over a period of time; at time of closing
the period the definete prices/tariff should be set; the system should
indicate where bandwidth/ranges do not fit the quantities
* different 'pricelists' can be applied to a set of 'line items'/sales


this is the reason why i stated "Where price has to be NULL if it
cannot be matched in the bandwith
table; i always need all the records from the Sales table... ", but
maybe i should have used the word tariff, because i don't mean the
qty*tariff result here, just the tariff.

Celko Answers
>> * the actual quantity for 1 line item (i will use this term instead of sale) is determined by a group of line items (if 1 is updated the whole lot can switch into another range)<<


I would use a VIEW with a GROUP BY to build these groups.


>> * the bandwidth/ranges/discounts may have a closed range: "1-10, 11-20, 21-30" (dont ask me why, commercial reasons probably) <<


That is still strange to me, but okay ...


>> * the line items are gathered over a period of time; at time of closing the period the definite prices/tariff should be set; the system should indicate where bandwidth/ranges do not fit the quantities <<


And having a VIEW would let you keep the correct totals


>> * different 'pricelists' can be applied to a set of 'line items'/sales <<


I would put all the price lists into one table, with the ranges and
price list name ("A list" customers, "B list" customers, etc.)

Here is a slight re-write; see if it helps


CREATE TABLE Sales
(sale_id INTEGER NOT NULL PRIMARY KEY,
product_id INTEGER NOT NULL
REFERENCES Products(product_id),
sold_qty INTEGER NOT NULL
CHECK(sold_qty > 0));


CREATE TABLE Bandwidth -- weird name, why not Discounts?
(product_id INTEGER NOT NULL
REFERENCES Products(product_id),
from_range INTEGER DEFAULT 1 NOT NULL
CHECK (from_range > 0),
to_range INTEGER NOT NULL, -- fix this constraint
CHECK(from_range <= to_range),
unit_price DECIMAL (10,4) NOT NULL,
PRIMARY KEY (product_id, from_range) );


Use an OUTER JOIN to presrve the sales data.


CREATE VIEW SalesRevenue(sale_id, product_id, sale_qty, sale_total)
AS
SELECT S.sale_id, S.product_id, S.sale_qty,
S.sale_qty*B.unit_price
FROM Sales AS S
LEFT OUTER JOIN
Bandwidth AS B
ON B.product_id = S.product_id
AND S.sale_qty BETWEEN
B.from_range AND B.to_range;