How to fetch the started on date when a price is changed (price is not incremental)

Question

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:

Works: http://sqlfiddle.com/#!9/4ae18/9
Fails: http://sqlfiddle.com/#!9/4ae18/12

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

I agree, I didn't design the table but I am going to be changing that — Jeremy, Commented Dec 15, 2017 at 17:35

stefan · Accepted Answer · 2017-12-17 12:59:24Z

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.

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, Commented Dec 19, 2017 at 1:37

Stack Exchange Network

How to fetch the started on date when a price is changed (price is not incremental)

1 Answer 1

Your Answer

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

Linked

Hot Network Questions

How to fetch the started on date when a price is changed (price is not incremental)

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

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

Linked

Related

Hot Network Questions

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