How to get the list of products and prices meeting different criteria in a table

Question

I have a pricing table as follows,

Pricing Table

id  productId  ContractId   ageGroup  ageFrom    ageTo  sellingPrice  specialPrice
1       1          1           1          0        2         0            0
2       1          1           1          3       13        20            0
3       1          1           2         18       55        80            0
4       1          1           3         56      119        60            0
5       1          1           1          0        2         0            0
6       1          2           2         18       55        85            0
7       2          2           3         55      119        90            0
8       2          2           2         18       55        90            0

I need to find the list of Contract Ids and Ids for given age Group (1-adult or 2-child or 3-senior). For the children the age range (from - to) need to be considered as well.

The following query (1 adult, 2 children with the ages 2 & 4 and 1 senior) seems to be working but returns only the ids matching the age group 1.

SELECT contractId,id 
FROM tbl_contract_price cp1 
WHERE contractId IN 
(SELECT contractId FROM tbl_contract_price cp2 
WHERE contractId IN 
(SELECT contractId FROM tbl_contract_price cp3 
WHERE cp1.ageGroup = 1 AND (cp2.ageGroup = 2 AND cp2.ageFrom <= 2 AND 2 <= cp2.ageTo OR cp2.ageGroup = 2 AND cp2.ageFrom <= 4 AND 4 <= cp2.ageTo ) AND cp3.ageGroup = 3))

Is there anything I am missing?

Answer 1

Based on some assumptions, I have created the following to help you get started. Please note that you will need to enforce your data integrity (i.e., ensuring that for each product, all possible ages are covered by a price, etc.)

I suggest that you use a temporary quote table so that you can have more flexibility on the number of inputs. You can see the data example below. Or, better yet, handle that logic within your Business Logic Layer.

You will need to apply any tie-breaker logic if two contracts yield the same price, etc.

CREATE TABLE Pricing (
  ID int not null,
  productId int not null,
  ContractId int not null,
  ageGroup int not null,
  ageFrom int not null,
  ageTo int not null,
  sellingPrice int not null,
  PRIMARY KEY (ID)
);

INSERT INTO Pricing (ID, productId, ContractId, ageGroup, ageFrom, ageTo, sellingPrice) Values (1, 1, 1, 1, 0, 2, 0);
INSERT INTO Pricing (id, productId, ContractId, ageGroup, ageFrom, ageTo, sellingPrice) Values (2, 1, 1, 1, 3, 13, 20);
INSERT INTO Pricing (id, productId, ContractId, ageGroup, ageFrom, ageTo, sellingPrice) Values (3, 1, 1, 2, 18, 55, 80);
INSERT INTO Pricing (id, productId, ContractId, ageGroup, ageFrom, ageTo, sellingPrice) Values (4, 1, 1, 3, 56, 119, 60);
INSERT INTO Pricing (id, productId, ContractId, ageGroup, ageFrom, ageTo, sellingPrice) Values (5, 1, 2, 1, 3, 13, 0);
INSERT INTO Pricing (id, productId, ContractId, ageGroup, ageFrom, ageTo, sellingPrice) Values (6, 1, 2, 2, 18, 55, 85);
INSERT INTO Pricing (id, productId, ContractId, ageGroup, ageFrom, ageTo, sellingPrice) Values (7, 2, 2, 3, 55, 119, 90);
INSERT INTO Pricing (id, productId, ContractId, ageGroup, ageFrom, ageTo, sellingPrice) Values (8, 2, 2, 2, 18, 55, 90);

CREATE TABLE ValidDates (
  ID int not null,
  priceId int not null,
  fromDate date not null,
  toDate date not null,
  PRIMARY KEY (ID)
 );

 INSERT INTO ValidDates (id, priceId, fromDate, toDate) VALUES  (1, 1, '2018-06-01', '2018-06-30');
 INSERT INTO ValidDates (id, priceId, fromDate, toDate) VALUES  (2, 2, '2018-06-01', '2018-06-30');
 INSERT INTO ValidDates (id, priceId, fromDate, toDate) VALUES  (3, 2, '2018-07-01', '2018-07-31');
 INSERT INTO ValidDates (id, priceId, fromDate, toDate) VALUES  (4, 3, '2018-06-01', '2018-06-30');
 INSERT INTO ValidDates (id, priceId, fromDate, toDate) VALUES  (5, 3, '2018-07-01', '2018-07-31');
 INSERT INTO ValidDates (id, priceId, fromDate, toDate) VALUES  (6, 4, '2018-06-01', '2018-06-30');
 INSERT INTO ValidDates (id, priceId, fromDate, toDate) VALUES  (7, 5, '2018-06-01', '2018-06-30');
 INSERT INTO ValidDates (id, priceId, fromDate, toDate) VALUES  (8, 5, '2018-07-01', '2018-07-31');
 INSERT INTO ValidDates (id, priceId, fromDate, toDate) VALUES  (9, 6, '2018-06-01', '2018-06-30');
 INSERT INTO ValidDates (id, priceId, fromDate, toDate) VALUES  (10, 6, '2018-07-01', '2018-07-31');

 CREATE TABLE Products (
   ID int not null,
   PRIMARY KEY (ID)
 );

CREATE TABLE Quotes (
  ID int not null,
  age int
);

INSERT INTO Quotes (Id, age) VALUES (1, 70);
INSERT INTO Quotes (Id, age) VALUES (1, 25);
INSERT INTO Quotes (Id, age) VALUES (1, 1);
INSERT INTO Quotes (Id, age) VALUES (1, 4); 

Then, you can use the following query to calculate your total price based on the product id, selected date, and your quote id (which has all the ages for the particular quote)

---

Scenario: tour date = Jun 22, 2018; product = 1, quote = 1 with age = 1, 4, 25, 70

SELECT @tourdate := '2018-06-22', @productid := 1, @quoteid := 1;

First query to show how the relevant information is retrieved

SELECT productid, contractId, ageGroup, ageFrom, ageTo,
SUM(CASE WHEN age BETWEEN ageFrom AND ageTo THEN 1 ELSE 0 END) AS PAXCount, sellingPrice
FROM ValidDates
LEFT JOIN Pricing
  ON priceId = Pricing.ID
    LEFT JOIN Products
      ON productId = Products.ID
        LEFT JOIN Quotes
          ON Quotes.ID = @quoteid
WHERE (@tourdate BETWEEN fromDate AND toDate) AND productid = @productid
GROUP BY productid, contractid, ageGroup, ageFrom, ageTo, sellingPrice;

---

second query is built upon the first query, aggregating the total so that you have the total cost for ranking

SELECT contractId, SUM(sellingPrice * PAXCount) FROM (
SELECT productid, contractId, ageGroup,
SUM(CASE WHEN age BETWEEN ageFrom AND ageTo THEN 1 ELSE 0 END) AS PAXCount, sellingPrice
FROM ValidDates
LEFT JOIN Pricing
  ON priceId = Pricing.ID
    LEFT JOIN Products
      ON productId = Products.ID
        LEFT JOIN Quotes
          ON Quotes.ID = @quoteid
WHERE (@tourdate BETWEEN fromDate AND toDate) AND productid = @productid
GROUP BY productid, contractid, ageGroup, sellingPrice) P
GROUP BY contractid
ORDER BY SUM(sellingPrice * PAXCount)
#LIMIT 1;

1. You can uncomment the #Limit 1 to get only the cheapest package, but you need to be aware of the limitation
2. You will need to ensure that your data integrity is enforced, i.e., for each product and date range, all possible age needs to be covered by

3. Note that because the child aged 0 and the senior aged 70 were not covered by contract id 2, the $85 total is misleading. You can add logic to check if a contract can fulfil all ages (if input count is 4, check if the contract does indeed include four people, etc.)

4. You might need to clean up the quotes tables as required. It is not the most efficient approach for sure (but it should work according to your requirements).

For example, change the query to something like this:

SELECT @PAXCount := COUNT(*) FROM Quotes WHERE id = @quoteid;

Or you can probably pass that in from your application fairly easily. Then, check to make sure that the count matches.

SELECT contractId, SUM(sellingPrice * PAXCount) AS TotalPrice, SUM(PAXCount) AS TotalPAXCOUNT
FROM (
  SELECT productid, contractId, ageGroup,
  SUM(CASE WHEN age BETWEEN ageFrom AND ageTo THEN 1 ELSE 0 END) AS PAXCount, sellingPrice
  FROM ValidDates
  LEFT JOIN Pricing
    ON priceId = Pricing.ID
      LEFT JOIN Products
        ON productId = Products.ID
          LEFT JOIN Quotes
            ON Quotes.ID = @quoteid
  WHERE (@tourdate BETWEEN fromDate AND toDate) AND productid = @productid 
  GROUP BY productid, contractid, ageGroup, sellingPrice) P
GROUP BY contractid
HAVING @PAXCount = SUM(PAXCount)
ORDER BY SUM(sellingPrice * PAXCount)
#LIMIT 1;

This way, only contract id covering all passengers will be shown.

---

Try it in the DB Fiddler