-- Collection of code snippets by Arne Vajhøj 
-- (likely posted to comp.os.vms/INFO-VAX, INFO-TPU, MACRO32, eksperten.dk, newz.dk, LinkedIn or other place sometime between 1990 and now) 
CREATE TABLE cart (
   itemid INTEGER NOT NULL,
   quantity INTEGER,
   PRIMARY KEY(itemid)
)
GO

CREATE TABLE items (
   itemid INTEGER NOT NULL,
   itemname VARCHAR(50),
   unitprice DECIMAL(12,2),
   PRIMARY KEY(itemid)
)
GO

CREATE TABLE rebate (
   itemid INTEGER NOT NULL,
   quantity INTEGER NOT NULL,
   unitprice DECIMAL(12,2),
   PRIMARY KEY(itemid,quantity)
)
GO
INSERT INTO cart VALUES(1, 1)
GO
INSERT INTO cart VALUES(2, 10)
GO
INSERT INTO items VALUES(1,'A',200.00)
GO
INSERT INTO items VALUES(2,'B',100.00)
GO
INSERT INTO rebate VALUES(2, 2, 90.00)
GO
INSERT INTO rebate VALUES(2, 4, 80.00)
GO
INSERT INTO rebate VALUES(2, 8, 70.00)
GO
INSERT INTO rebate VALUES(2, 16, 60.00)
GO

SELECT itemname,quantity,unitprice
FROM cart JOIN items ON cart.itemid=items.itemid
GO
SELECT itemname,quantity,unitprice,CASE WHEN quantity = 1 THEN unitprice ELSE afterrebate END AS unitpriceafterrebate
FROM (cart INNER JOIN items ON cart.itemid=items.itemid) LEFT JOIN (SELECT rebate.itemid,MIN(unitprice) AS afterrebate
                                                                    FROM rebate INNER JOIN cart ON rebate.itemid=cart.itemid
                                                                    WHERE rebate.quantity<=cart.quantity
                                                                    GROUP BY rebate.itemid) x ON cart.itemid=x.itemid
GO
SELECT itemname,quantity,unitprice,ISNULL(afterrebate,unitprice) AS unitpriceafterrebate
FROM (cart INNER JOIN items ON cart.itemid=items.itemid) LEFT JOIN (SELECT rebate.itemid,MIN(unitprice) AS afterrebate
                                                                    FROM rebate INNER JOIN cart ON rebate.itemid=cart.itemid
                                                                    WHERE rebate.quantity<=cart.quantity
                                                                    GROUP BY rebate.itemid) x ON cart.itemid=x.itemid
GO

DROP TABLE cart
GO
DROP TABLE items
GO
DROP TABLE rebate
GO
