1

I have below table which stores my product prices and special prices data for my products. I want to get the least price product from below data.

+------+-------+---------------+-------------------+-----------------+
|  id  | price | special_price | special_from_date | special_to_date |
+------+-------+---------------+-------------------+-----------------+
| 2647 |   299 |           251 | NULL              | NULL            |
| 2648 |   299 |            85 | 2017-12-29        | 2018-02-28      |
| 2649 |   299 |          NULL | 2017-12-29        | 2018-02-28      |
| 2650 |   299 |            55 | 2017-12-29        | 2018-01-01      |
| 2651 |   299 |           179 | 2017-12-29        | NULL            |
+------+-------+---------------+-------------------+-----------------+

The tricky part here is that I want to check below conditions.

  1. Check price column for minimum product price.
  2. Skip special_price if NULL
  3. Skip special_price if special_from_date and special_to_date is NULL or current date does not fall between range
  4. Check special_price column if special_from_date is not NULL and special_from_date is less than current date and special_to_date is either NULL or greater than current date.

The output will be like below.

+------+-------+---------------+-------------------+-----------------+
|  id  | price | special_price | special_from_date | special_to_date |
+------+-------+---------------+-------------------+-----------------+
| 2648 |   299 |            85 | 2017-12-29        | 2018-02-28      |
+------+-------+---------------+-------------------+-----------------+

Here is my table schema.

+------------------------+---------------------+------+-----+---------+-------+
| Field                  | Type                | Null | Key | Default | Extra |
+------------------------+---------------------+------+-----+---------+-------+
| id                     | int(10) unsigned    | NO   | PRI | NULL    |       |
| price                  | decimal(12,4)       | YES  |     | NULL    |       |
| special_price          | decimal(12,4)       | YES  |     | NULL    |       |
| special_from_date      | date                | YES  |     | NULL    |       |
| special_to_date        | date                | YES  |     | NULL    |       |
+------------------------+---------------------+------+-----+---------+-------+
  • Please add table schema. – McNets Feb 9 '18 at 9:57
  • 1
    2018-02-29 is not a valid date – McNets Feb 9 '18 at 10:02
  • 1
    I don't see what's so tricky about this query. You'll need WHERE, GROUP BY (that's not clear from your question. Where's the product id in your table?), and MIN(). After completing a sql tutorial you should be able to write the appropriate query. Please have a try and post the result. It would probably clear up the question as well. – tombom Feb 9 '18 at 10:03
  • @tombom, Thanks for the suggestion, I'll try from my side and will add query to the question. PS: this is just the demo table, my original table contains a lot more data but I have only used the columns which are required. – Jaimin Sutariya Feb 9 '18 at 10:09
1

Get the current price as follows:

Use special_price when:

  1. special_price is not NULL
  2. special_from_date is not NULL
  3. special_from_date is not greater than today
  4. One of the following applies (OR condition)
    • special_to_date is NULL (open)
    • special_to_date is not less than current date

Otherwise use (default) price.

Since a comparison will "fail" if one side is NULL, (2) is redundant with (3) and can be skipped.

To avoid an OR-condition, (4) can be expressed as coalesce(special_to_date, curdate()) >= curdate().

select p.*,
  case
    when  special_price is not null -- 1
      and curdate() >= special_from_date -- 2 and 3
      and curdate() <= coalesce(special_to_date, curdate()) -- 4
    then special_price
    else price
  end as current_price
from prices p
order by current_price asc
limit 1

Demo: http://rextester.com/ETEU48971

Note that it's not quite clear, if you want to include special_from_date and special_to_date into the range. In this case they are included. If you want to exclude them, you can use:

      and curdate() > special_from_date
      and curdate() < coalesce(special_to_date, curdate() + interval 1 day)
  • Thank you very much. that worked for me. I was not aware of coalesce – Jaimin Sutariya Feb 10 '18 at 4:47
2

Keep in mind that if CURDATE() doesn't fit any range of dates you'll get no rows.

select   id, 
         min(price) price, 
         special_price, 
         special_from_date,
         special_to_date
from     prices
where    special_price is not null
and      special_from_date is not null
and      special_to_date is not null
and      (curdate() >= special_from_date
          and
          curdate() <= special_to_date)
group by id
  id |    price | special_price | special_from_date | special_to_date
---: | -------: | ------------: | :---------------- | :--------------
2648 | 299.0000 |       85.0000 | 2017-12-29        | 2018-02-28     

dbfiddle here

  • Thank you for this answer, but it is not working if special_to_date is null – Jaimin Sutariya Feb 9 '18 at 11:01
  • I've removed rows where special_to_date is null or special_from_date is null – McNets Feb 9 '18 at 11:04
  • I mean if special_to_date for 2648 column is NULL, it should fetch the same row and not skip it. Because NULL value for special_to_date means there is no expiry date for special price. – Jaimin Sutariya Feb 9 '18 at 11:10
  • then you'll get two rows, 2648, 2651 – McNets Feb 9 '18 at 11:23
  • You are grouping by the primary key. That doesn't make sence in a single table select statement. – Paul Spiegel Feb 10 '18 at 2:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.