Suppose i have the following MySQL prices table:
| ID | item | from_date | to_date | customer | price |
|---|---|---|---|---|---|
| 1 | 1 | 2021-10-10 | 2021-12-14 | NULL | 103.25 |
| 2 | 1 | 2021-12-15 | NULL | NULL | 100.25 |
| 3 | 5 | 2022-01-01 | NULL | NULL | 201.50 |
| 4 | 1 | 2022-01-05 | NULL | 3 | 80.75 |
The idea is that for there will be a price record for each item and if there are special price deals for a customer, also a record for that item with a different price. Looking for the price, the price should be taken from the record that is for the item and that specific customer. if there is none, it should return the default price which is valid for all customers.
So, I am trying to get the price that is valid at today's date (2022-02-02). Having those records selected, if there is a most recent record for my specific customer (3) it takes precedence, return that (record ID 4). If there is none for this customer, return the most recent price (record ID 2)
So, today = 2022-02-02, my item is 1 AND my customer is 3. Using the table above the result should be:
| ID | item | from_date | to_date | customer | price |
|---|---|---|---|---|---|
| 4 | 1 | 2022-01-05 | NULL | 3 | 80.75 |
If the record with ID = 4 is not there the result should be:
| ID | item | from_date | to_date | customer | price |
|---|---|---|---|---|---|
| 2 | 1 | 2021-12-15 | NULL | NULL | 100.25 |
I tried several select queries but still don’t see what i should do and google didn’t help me much.
Any help would be appreciated greatly!
CodePudding user response：
Assuming that there is no record for the same item with the same from_date, then this could do it.
SELECT * FROM YourTable WHERE item = '1' AND ((from_date = '2022-02-02' AND customer = '3') OR (from_date < '2022-02-02' AND (customer <> '3' OR customer IS NULL))) ORDER BY from_date DESC, ID DESC LIMIT 1
The query selects all records for item = 1, with either the requested from_date from the customer you wish, or from other customer (or null) from previous dates. The order by DESC will sort the records by the latest from_date and ID (if there is the same date) and the LIMIT 1 will show only the first record of the query.
If you wish to show the latest price with no customer (if the requested record for the customer is not found), omit the customer <> '3'
SELECT * FROM YourTable WHERE item = '1' AND ((from_date = '2022-02-02' AND customer = '3') OR (from_date < '2022-02-02' AND customer IS NULL)) ORDER BY from_date DESC, ID DESC LIMIT 1
CodePudding user response：
Ok, i think i wasn't in the right mind when i asked this question. See things much clearer now.
I solved it via this simple query:
SELECT ISP.gross_price, ISP.disc_amount, ISP.disc_percentage, I.Name FROM item_salesprices ISP
JOIN Items I ON I.ID = ISP.item
WHERE I.Item=‘ZZ1’
AND (ISP.Customer=(SELECT ID FROM Customers WHERE Customer=‘Cust3’) OR ISP.Customer IS NULL)
AND (ISP.from_date <= NOW() OR ISP.from_date IS NULL) AND (ISP.to_date >= NOW() OR ISP.to_date IS NULL)
ORDER BY ISP.Customer DESC, ISP.from_date DESC LIMIT 1
The result is that if there's a price record for item = 1 and customer = 3, it will output only that row. Thus, record ID = 4, price = 80.75.
If there's not, it will output the default price row for item = 1 (and essentially customer IS null). Thus, record ID = 2, price = 100.25.