I have two tables and can't wrap my mind around how to join them. The first has Shipments. Each with a dollar value. The second table has a fee percentage table that applies to the aggregate of the shipments.
In the enclosed example there are 10 shipments totaling $44.45. There are 6 tiers that apply to that aggregate. The total surcharge should be $5.26.
Min Max Tier Fee
0.00 5.00 0.01 0.05
5.01 10.00 0.02 0.2
10.01 20.00 0.04 0.8
20.01 30.00 0.05 1.5
30.01 40.00 0.06 2.4
40.01 50.00 0.07 0.3115
5.2615
The raw data is below. Is there a decent way to do this with SQL only?
CREATE TABLE Shipment (
id INTEGER,
tracking NVARCHAR(800),
value DECIMAL(18,2),
dateadded DATETIME
);
INSERT INTO Shipment (id, tracking, value, dateadded) VALUES (1, '123456', 8.90, '2017-02-07 14:48:10.486232');
INSERT INTO Shipment (id, tracking, value, dateadded) VALUES (2, '156789023890', 4.95, '2017-02-07 14:49:49.784686');
INSERT INTO Shipment (id, tracking, value, dateadded) VALUES (3, '356789045890', 2.95, '2017-02-07 14:49:49.896843');
INSERT INTO Shipment (id, tracking, value, dateadded) VALUES (4, '556789067890', 2.95, '2017-02-07 14:49:49.964853');
INSERT INTO Shipment (id, tracking, value, dateadded) VALUES (5, '456567890490', 1.95, '2017-02-07 14:49:50.018834');
INSERT INTO Shipment (id, tracking, value, dateadded) VALUES (6, '345656789890', 4.95, '2017-02-07 14:49:50.095079');
INSERT INTO Shipment (id, tracking, value, dateadded) VALUES (7, '456567890890', 3.95, '2017-02-07 14:49:50.149059');
INSERT INTO Shipment (id, tracking, value, dateadded) VALUES (8, '567567890890', 5.95, '2017-02-07 14:49:50.224858');
INSERT INTO Shipment (id, tracking, value, dateadded) VALUES (9, '456789567900', 4.95, '2017-02-07 14:49:50.278679');
INSERT INTO Shipment (id, tracking, value, dateadded) VALUES (10, '565678907890', 2.95, '2017-02-07 14:49:50.328999');
CREATE TABLE AccountTier (
id INT,
tierpercentage DECIMAL(18,2),
minvalue DECIMAL(18,2),
maxvalue DECIMAL(18,2),
dateadded DATETIME
);
INSERT INTO AccountTier (id, tierpercentage, minvalue, maxvalue, dateadded) VALUES (1, 0.01, 0.00, 5.00, '2017-02-07 14:52:52.599540');
INSERT INTO AccountTier (id, tierpercentage, minvalue, maxvalue, dateadded) VALUES (2, 0.02, 5.01, 10.00, '2017-02-07 14:52:52.599540');
INSERT INTO AccountTier (id, tierpercentage, minvalue, maxvalue, dateadded) VALUES (3, 0.04, 10.01, 20.00, '2017-02-07 14:52:52.599540');
INSERT INTO AccountTier (id, tierpercentage, minvalue, maxvalue, dateadded) VALUES (4, 0.05, 20.01, 30.00, '2017-02-07 14:52:52.599540');
INSERT INTO AccountTier (id, tierpercentage, minvalue, maxvalue, dateadded) VALUES (5, 0.06, 30.01, 40.00, '2017-02-07 14:52:52.599540');
INSERT INTO AccountTier (id, tierpercentage, minvalue, maxvalue, dateadded) VALUES (6, 0.07, 40.01, 50.00, '2017-02-07 14:52:52.599540');