2

I am trying to build a single query that will allow me to fetch the first saved_on date from when a price was assigned.

The tricky part is this data needs to only be within the most recent changes for that price. Meaning, if the price were $5 then changed the $6 and then changes back to $5, I need to only query the $5 change date after it was $6 to get the correct price started on date. Since the price was $6 between the $5, I want to ignore any $5 before it was changed to $6.

I am just trying to make a report of when an item's price was last updated to the latest price. Due to some other constraints, I need control over the price because there are some cases where the latest price will be the previous price. To prevent any issues, I want to add the price to the conditional.

What I am trying to do is very similar to this request but slightly different. I tried to use the updated example as this seems to be closest to what I am trying to do but have had no success.

The query needs to work by selecting a custno, item, and price. The price is assumed to be the current price but can be changed to be an earlier price.

I have tried to do something like:

SELECT
    saved_on
FROM customer_items
WHERE custno = '{$custno}'
    AND item = '{$item}'
  AND price = {$price}
  AND saved_on > (
      SELECT
            saved_on
      FROM customer_items
      WHERE custno = '{$custno}'
        AND item = '{$item}'
        AND price <> {$price}
      ORDER BY week_of DESC
      LIMIT 1
)
ORDER BY week_of ASC
LIMIT 1

This works on some but not all the items.

Tests:

I am a bit stuck. Hopefully someone here can push me in the correct direction.

DB Schema and Data:

-- Create Table
CREATE TABLE IF NOT EXISTS `customer_items` (
  `cid` bigint(20) AUTO_INCREMENT NOT NULL,
  `item` varchar(15) NOT NULL,
  `custno` varchar(6) NOT NULL,
  `week_of` date NOT NULL,
  `price` double(12,4) NOT NULL,
  `saved_on` datetime NOT NULL,
  PRIMARY KEY (`cid`),
  UNIQUE KEY `custno_item_weekof` (`custno`,`item`,`week_of`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;


-- Add Data for test 1 (ALLPR - OYDI2505B)
INSERT INTO `customer_items` (`cid`, `item`, `custno`, `week_of`, `price`, `saved_on`) VALUES
    (NULL, 'OYDI2505B', 'ALLPR', '2017-12-11', 4.25, '2017-12-07 16:57:50'), -- Expect this for current price start date 
    (NULL, 'OYDI2505B', 'ALLPR', '2017-10-09', 3.25, '2017-10-05 09:26:48'),
    (NULL, 'OYDI2505B', 'ALLPR', '2017-02-27', 3.00, '2017-02-22 14:45:33'),
    (NULL, 'OYDI2505B', 'ALLPR', '2015-06-08', 3.00, '2015-06-04 15:55:02'), -- Expect this for when I select the 3.00 price for the start date
    (NULL, 'OYDI2505B', 'ALLPR', '2015-06-01', 3.75, '2015-05-28 16:03:02'),
    (NULL, 'OYDI2505B', 'ALLPR', '2015-03-30', 3.25, '2015-03-26 14:26:18'),
    (NULL, 'OYDI2505B', 'ALLPR', '2015-02-09', 3.25, '2015-02-02 16:17:48'),
    (NULL, 'OYDI2505B', 'ALLPR', '2015-01-05', 2.95, '2014-12-30 10:41:33'),
    (NULL, 'OYDI2505B', 'ALLPR', '2014-12-29', 3.00, '2014-12-23 11:30:33'),
    (NULL, 'OYDI2505B', 'ALLPR', '2014-11-10', 2.95, '2014-11-03 15:29:29'),
    (NULL, 'OYDI2505B', 'ALLPR', '2014-10-20', 2.95, '2014-10-16 18:13:58'),
    (NULL, 'OYDI2505B', 'ALLPR', '2014-10-13', 3.00, '2014-10-08 10:42:54'),
    (NULL, 'OYDI2505B', 'ALLPR', '2014-09-22', 2.95, '2014-09-19 17:32:46'),
    (NULL, 'OYDI2505B', 'ALLPR', '2014-09-15', 2.95, '2014-09-11 17:29:53'),
    (NULL, 'OYDI2505B', 'ALLPR', '2014-09-08', 2.95, '2014-09-04 17:58:39'),
    (NULL, 'OYDI2505B', 'ALLPR', '2014-09-01', 2.95, '2014-08-29 18:00:35'),
    (NULL, 'OYDI2505B', 'ALLPR', '2014-07-07', 3.00, '2014-08-13 21:09:11');


-- Add Data for test 2 (JPFS - LT3WCS )
INSERT INTO `customer_items` (`cid`, `item`, `custno`, `week_of`, `price`, `saved_on`) VALUES
    (NULL, 'LT3WCS', 'JPFS', '2017-10-09', 3.25, '2017-10-05 09:26:48'), -- Expect this for current price start date 
    (NULL, 'LT3WCS', 'JPFS', '2017-02-27', 4.00, '2017-02-22 14:45:33'),
    (NULL, 'LT3WCS', 'JPFS', '2015-06-08', 4.00, '2015-06-04 15:55:02'), 
    (NULL, 'LT3WCS', 'JPFS', '2015-06-01', 3.75, '2015-05-28 16:03:02'),
    (NULL, 'LT3WCS', 'JPFS', '2015-03-30', 3.25, '2015-03-26 14:26:18'),
    (NULL, 'LT3WCS', 'JPFS', '2015-02-09', 3.25, '2015-02-02 16:17:48'),
    (NULL, 'LT3WCS', 'JPFS', '2015-01-05', 2.95, '2014-12-30 10:41:33'),
    (NULL, 'LT3WCS', 'JPFS', '2014-12-29', 2.95, '2014-12-23 11:30:33'),
    (NULL, 'LT3WCS', 'JPFS', '2014-11-10', 2.95, '2014-11-03 15:29:29'),
    (NULL, 'LT3WCS', 'JPFS', '2014-10-20', 2.95, '2014-10-16 18:13:58'),
    (NULL, 'LT3WCS', 'JPFS', '2014-10-13', 2.95, '2014-10-08 10:42:54'), -- Expect this for when I select the $2.95 price for the start date
    (NULL, 'LT3WCS', 'JPFS', '2014-09-22', 4.00, '2014-09-19 17:32:46'),
    (NULL, 'LT3WCS', 'JPFS', '2014-09-15', 2.95, '2014-09-11 17:29:53'),
    (NULL, 'LT3WCS', 'JPFS', '2014-09-08', 2.95, '2014-09-04 17:58:39'),
    (NULL, 'LT3WCS', 'JPFS', '2014-09-01', 2.95, '2014-08-29 18:00:35'),
    (NULL, 'LT3WCS', 'JPFS', '2014-07-07', 2.63, '2014-08-13 21:09:11');

-- Add Data for test 3 (99ST - FAMIX1212OZCS)
INSERT INTO `customer_items` (`cid`, `item`, `custno`, `week_of`, `price`, `saved_on`) VALUES
    (NULL, 'FAMIX1212OZCS', '99ST', '2017-12-11', 6.60, '2017-12-07 16:11:46'),
    (NULL, 'FAMIX1212OZCS', '99ST', '2017-11-27', 6.60, '2017-11-22 17:04:13'),
    (NULL, 'FAMIX1212OZCS', '99ST', '2017-11-20', 6.60, '2017-11-16 19:09:14'),
    (NULL, 'FAMIX1212OZCS', '99ST', '2017-11-13', 6.60, '2017-11-09 19:34:49'),
    (NULL, 'FAMIX1212OZCS', '99ST', '2017-11-06', 6.60, '2017-11-02 19:10:20'),
    (NULL, 'FAMIX1212OZCS', '99ST', '2017-10-30', 6.60, '2017-10-26 18:13:32'),
    (NULL, 'FAMIX1212OZCS', '99ST', '2017-10-23', 6.60, '2017-10-19 18:30:44'),
    (NULL, 'FAMIX1212OZCS', '99ST', '2017-08-07', 6.60, '2017-08-03 16:46:47'),
    (NULL, 'FAMIX1212OZCS', '99ST', '2017-07-10', 6.60, '2017-07-07 16:19:37'),
    (NULL, 'FAMIX1212OZCS', '99ST', '2017-06-26', 6.60, '2017-06-22 16:12:35'),
    (NULL, 'FAMIX1212OZCS', '99ST', '2017-04-03', 6.60, '2017-03-30 20:29:50'),
    (NULL, 'FAMIX1212OZCS', '99ST', '2017-03-13', 6.60, '2017-03-09 18:18:26'), -- Expect this for current price start date 
    (NULL, 'FAMIX1212OZCS', '99ST', '2017-02-27', 7.20, '2017-02-23 16:57:30'), -- Expect this for when I select the $7.20 price for the start date
    (NULL, 'FAMIX1212OZCS', '99ST', '2017-02-13', 6.60, '2017-02-09 17:08:33'),
    (NULL, 'FAMIX1212OZCS', '99ST', '2016-12-12', 6.60, '2016-12-08 19:02:19'),
    (NULL, 'FAMIX1212OZCS', '99ST', '2016-12-05', 6.60, '2016-12-01 16:48:14'),
    (NULL, 'FAMIX1212OZCS', '99ST', '2016-11-14', 6.60, '2016-11-10 15:47:55'),
    (NULL, 'FAMIX1212OZCS', '99ST', '2016-10-10', 6.60, '2016-10-04 12:22:09');

-- Add Data for test 4 (KBSF - CAST1255B )
INSERT INTO `customer_items` (`cid`, `item`, `custno`, `week_of`, `price`, `saved_on`) VALUES
    (NULL, 'CAST1255B', 'KBSF', '2017-12-11', 6.60, '2017-12-07 16:11:46'),
    (NULL, 'CAST1255B', 'KBSF', '2017-02-13', 6.60, '2017-02-09 17:08:33'),
    (NULL, 'CAST1255B', 'KBSF', '2016-12-12', 6.60, '2016-12-08 19:02:19'),
    (NULL, 'CAST1255B', 'KBSF', '2016-12-05', 6.60, '2016-12-01 16:48:14'),
    (NULL, 'CAST1255B', 'KBSF', '2016-11-14', 6.60, '2016-11-10 15:47:55'),
    (NULL, 'CAST1255B', 'KBSF', '2016-10-10', 6.60, '2016-10-04 12:22:09'); -- Expect this for current price start date
2
  • For money, don't use DOUBLE or FLOAT, use DECIMAL.
    – Rick James
    Dec 15, 2017 at 15:13
  • I agree, I didn't design the table but I am going to be changing that
    – Jeremy
    Dec 15, 2017 at 17:35

1 Answer 1

1

Maybe the following query will give you a starting point for writing your own ... Principle: select 4 columns from your customer_items table (derived table dt). Then, use 2 user-defined variables that will allow us to number the prices. At a particular date, a particular price was recorded for the first time. Its "number" will be: 1. If the same price is used again, its "number" will be 2 etc. Whenever a new/different price has been recorded, the numbering is interrupted (and the numbers start at 1 again). Example:

  select
    dt.custno
  , dt.item
  , dt.saved_on
  , @number_ := if( @price_ = dt.price, @number_, 0 ) + 1 as occp -- occurence of current price
  , @price_ := dt.price  price
  from
  (
    select
      custno
    , saved_on
    , price
    , item
    from customer_items
    order by custno, saved_on
  ) dt

-- JPFS | LT3WCS section of dt:
...
| JPFS   | LT3WCS        | 2014-08-13 21:09:11 |    1 | 2.6300 |
| JPFS   | LT3WCS        | 2014-08-29 18:00:35 |    1 | 2.9500 |
| JPFS   | LT3WCS        | 2014-09-04 17:58:39 |    2 | 2.9500 |
| JPFS   | LT3WCS        | 2014-09-11 17:29:53 |    3 | 2.9500 |
| JPFS   | LT3WCS        | 2014-09-19 17:32:46 |    1 | 4.0000 |
| JPFS   | LT3WCS        | 2014-10-08 10:42:54 |    1 | 2.9500 |
| JPFS   | LT3WCS        | 2014-10-16 18:13:58 |    2 | 2.9500 |
| JPFS   | LT3WCS        | 2014-11-03 15:29:29 |    3 | 2.9500 |
| JPFS   | LT3WCS        | 2014-12-23 11:30:33 |    4 | 2.9500 |
| JPFS   | LT3WCS        | 2014-12-30 10:41:33 |    5 | 2.9500 |
| JPFS   | LT3WCS        | 2015-02-02 16:17:48 |    1 | 3.2500 |
| JPFS   | LT3WCS        | 2015-03-26 14:26:18 |    2 | 3.2500 |
| JPFS   | LT3WCS        | 2015-05-28 16:03:02 |    1 | 3.7500 |
| JPFS   | LT3WCS        | 2015-06-04 15:55:02 |    1 | 4.0000 |
| JPFS   | LT3WCS        | 2017-02-22 14:45:33 |    2 | 4.0000 |
| JPFS   | LT3WCS        | 2017-10-05 09:26:48 |    1 | 3.2500 |
...

You are only interested in the most recent change of a particular price. Its "number" will be: 1. The most recent price labelled "1" can be found with MAX( saved_on ). Thus, we add another SELECT and some conditions eg

select
  max( saved_on )
from
(
  select
    dt.custno
  , dt.item
  , dt.saved_on
  , @number_ := if( @price_ = dt.price, @running, 0 ) + 1 as occp -- occurence of current price
  , @price_ := dt.price  price
  from
  (
    select
      custno
    , saved_on
    , price
    , item
    from customer_items
    order by custno, saved_on
  ) dt
) dt2
where occp = 1
  and custno = 'JPFS'
  and item = 'LT3WCS'
  and price = 2.95
;

-- result
+---------------------+
| max( saved_on )     |
+---------------------+
| 2014-10-08 10:42:54 |
+---------------------+
1 row in set (0.00 sec)

For the query that did not give you a result ( custno = 'ALLPR' and item = 'OYDI2505B' and price = 3.25 ) , we get:

+---------------------+
| max( saved_on )     |
+---------------------+
| 2017-10-05 09:26:48 |
+---------------------+
1 row in set (0.00 sec)

Tested with MySQL 5.7 and 5.6, using your test data. SQL Fiddle here.

1
  • Thanks! This got me close to where I needed to be. I am using MySQL 5.0 unfortunately but this gave me the direction I needed. It's a bit slow on larger data but I can work with this.
    – Jeremy
    Dec 19, 2017 at 1:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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