Help on complex query

I have a table of items, with multiple records for each item, representing d
ifferent prices for different periods, and a table which defines supplier re
bates for different periods (may be different than item periods)
For example:
ITEMS: itemno, supplier_id, price, valid_from, valid_to:
1, 1, 1000, '01-01-2001'. '12-31-2001'
1, 1, 1100, '01-01-2002'. '12-31-2002'
1, 1, 1200, '01-01-2003'. '12-31-2003'
SUPPLIER_REBATES:
supplier_id, rebate, rebate_valid_to:
1, 10, '10-31-2002' (10% until 10-31-2002)
1, 12, '12-31-2010' (12% until 12-31-2010)
I need a query to list all items, with the corresponding rebate, according t
o valid_from date, e.g.:
RESULT: itemno, price, valid_from, valid_to, rebate
1, 1000, '01-01-2001'. '12-31-2001', 10
1, 1100, '01-01-2002'. '12-31-2002', 10
1, 1200, '01-01-2003'. '12-31-2003', 12
How do I do that?
Thanks in advance for your helpIt helps if you include DDL and sample data INSERTs with your posts so that
we don't have to guess your keys, constraints and datatypes:
CREATE TABLE Items (itemno INTEGER NOT NULL, supplier_id INTEGER NOT NULL,
price INTEGER NOT NULL, valid_from DATETIME NOT NULL, valid_to DATETIME NOT
NULL, CHECK (valid_from <= valid_to) /* PRIMARY KEY ? */)
CREATE TABLE Rebates (supplier_id INTEGER NOT NULL, rebate INTEGER NOT NULL,
rebate_valid_to DATETIME, PRIMARY KEY (supplier_id, rebate_valid_to) /* ?
*/)
INSERT INTO Items VALUES (1, 1, 1000, '20001011', '20011231')
INSERT INTO Items VALUES (1, 1, 1100, '20020101', '20021231')
INSERT INTO Items VALUES (1, 1, 1200, '20030101', '20031231')
INSERT INTO Rebates VALUES (1, 10, '20021031')
INSERT INTO Rebates VALUES (1, 12, '20101231')
Why don't you have a Valid_From date in your Rebates table? You can
calculate it like this:
SELECT R1.supplier_id,
COALESCE(MAX(R2.rebate_valid_to),'17530101') AS rebate_valid_after,
R1.rebate_valid_to, R1.rebate
FROM Rebates AS R1
LEFT JOIN Rebates AS R2
ON R1.supplier_id = R2.supplier_id
AND R1.rebate_valid_to > R2.rebate_valid_to
GROUP BY R1.supplier_id, R1.rebate_valid_to, R1.rebate
You haven't specified how you determine the rebate when more than one rebate
can apply to a particular valid period for an item. Your required result has
only one row for the period
2002-01-01 to 2002-12-31 yet it appears that *both* rebates were valid
during that period. How do you want to arrive at the result you specified?
The following returns *all* the valid rebates for each Item row.
SELECT I.itemno, I.price, I.valid_from, I.valid_to, R.rebate
FROM Items AS I
JOIN
(SELECT R1.supplier_id,
COALESCE(MAX(R2.rebate_valid_to),'17530101') AS rebate_valid_after,
R1.rebate_valid_to, R1.rebate
FROM Rebates AS R1
LEFT JOIN Rebates AS R2
ON R1.supplier_id = R2.supplier_id
AND R1.rebate_valid_to > R2.rebate_valid_to
GROUP BY R1.supplier_id, R1.rebate_valid_to, R1.rebate) AS R
ON I.valid_from < R.rebate_valid_to AND I.valid_to > R.rebate_valid_after
ORDER BY valid_from
David Portas
SQL Server MVP
--