· 8 years ago · Dec 15, 2017, 04:00 AM
1SELECT
2 saved_on
3FROM customer_items
4WHERE custno = '{$custno}'
5 AND item = '{$item}'
6 AND price = {$price}
7 AND saved_on > (
8 SELECT
9 saved_on
10 FROM customer_items
11 WHERE custno = '{$custno}'
12 AND item = '{$item}'
13 AND price <> {$price}
14 ORDER BY week_of DESC
15 LIMIT 1
16)
17ORDER BY week_of ASC
18LIMIT 1
19
20-- Create Table
21CREATE TABLE IF NOT EXISTS `customer_items` (
22 `cid` bigint(20) AUTO_INCREMENT NOT NULL,
23 `item` varchar(15) NOT NULL,
24 `custno` varchar(6) NOT NULL,
25 `week_of` date NOT NULL,
26 `price` double(12,4) NOT NULL,
27 `saved_on` datetime NOT NULL,
28 PRIMARY KEY (`cid`),
29 UNIQUE KEY `custno_item_weekof` (`custno`,`item`,`week_of`)
30) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
31
32
33-- Add Data for test 1 (ALLPR - OYDI2505B)
34INSERT INTO `customer_items` (`cid`, `item`, `custno`, `week_of`, `price`, `saved_on`) VALUES
35 (NULL, 'OYDI2505B', 'ALLPR', '2017-12-11', 4.25, '2017-12-07 16:57:50'), -- Expect this for current price start date
36 (NULL, 'OYDI2505B', 'ALLPR', '2017-10-09', 3.25, '2017-10-05 09:26:48'),
37 (NULL, 'OYDI2505B', 'ALLPR', '2017-02-27', 3.00, '2017-02-22 14:45:33'),
38 (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
39 (NULL, 'OYDI2505B', 'ALLPR', '2015-06-01', 3.75, '2015-05-28 16:03:02'),
40 (NULL, 'OYDI2505B', 'ALLPR', '2015-03-30', 3.25, '2015-03-26 14:26:18'),
41 (NULL, 'OYDI2505B', 'ALLPR', '2015-02-09', 3.25, '2015-02-02 16:17:48'),
42 (NULL, 'OYDI2505B', 'ALLPR', '2015-01-05', 2.95, '2014-12-30 10:41:33'),
43 (NULL, 'OYDI2505B', 'ALLPR', '2014-12-29', 3.00, '2014-12-23 11:30:33'),
44 (NULL, 'OYDI2505B', 'ALLPR', '2014-11-10', 2.95, '2014-11-03 15:29:29'),
45 (NULL, 'OYDI2505B', 'ALLPR', '2014-10-20', 2.95, '2014-10-16 18:13:58'),
46 (NULL, 'OYDI2505B', 'ALLPR', '2014-10-13', 3.00, '2014-10-08 10:42:54'),
47 (NULL, 'OYDI2505B', 'ALLPR', '2014-09-22', 2.95, '2014-09-19 17:32:46'),
48 (NULL, 'OYDI2505B', 'ALLPR', '2014-09-15', 2.95, '2014-09-11 17:29:53'),
49 (NULL, 'OYDI2505B', 'ALLPR', '2014-09-08', 2.95, '2014-09-04 17:58:39'),
50 (NULL, 'OYDI2505B', 'ALLPR', '2014-09-01', 2.95, '2014-08-29 18:00:35'),
51 (NULL, 'OYDI2505B', 'ALLPR', '2014-07-07', 3.00, '2014-08-13 21:09:11');
52
53
54-- Add Data for test 2 (JPFS - LT3WCS )
55INSERT INTO `customer_items` (`cid`, `item`, `custno`, `week_of`, `price`, `saved_on`) VALUES
56 (NULL, 'LT3WCS', 'JPFS', '2017-10-09', 3.25, '2017-10-05 09:26:48'), -- Expect this for current price start date
57 (NULL, 'LT3WCS', 'JPFS', '2017-02-27', 4.00, '2017-02-22 14:45:33'),
58 (NULL, 'LT3WCS', 'JPFS', '2015-06-08', 4.00, '2015-06-04 15:55:02'),
59 (NULL, 'LT3WCS', 'JPFS', '2015-06-01', 3.75, '2015-05-28 16:03:02'),
60 (NULL, 'LT3WCS', 'JPFS', '2015-03-30', 3.25, '2015-03-26 14:26:18'),
61 (NULL, 'LT3WCS', 'JPFS', '2015-02-09', 3.25, '2015-02-02 16:17:48'),
62 (NULL, 'LT3WCS', 'JPFS', '2015-01-05', 2.95, '2014-12-30 10:41:33'),
63 (NULL, 'LT3WCS', 'JPFS', '2014-12-29', 2.95, '2014-12-23 11:30:33'),
64 (NULL, 'LT3WCS', 'JPFS', '2014-11-10', 2.95, '2014-11-03 15:29:29'),
65 (NULL, 'LT3WCS', 'JPFS', '2014-10-20', 2.95, '2014-10-16 18:13:58'),
66 (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
67 (NULL, 'LT3WCS', 'JPFS', '2014-09-22', 4.00, '2014-09-19 17:32:46'),
68 (NULL, 'LT3WCS', 'JPFS', '2014-09-15', 2.95, '2014-09-11 17:29:53'),
69 (NULL, 'LT3WCS', 'JPFS', '2014-09-08', 2.95, '2014-09-04 17:58:39'),
70 (NULL, 'LT3WCS', 'JPFS', '2014-09-01', 2.95, '2014-08-29 18:00:35'),
71 (NULL, 'LT3WCS', 'JPFS', '2014-07-07', 2.63, '2014-08-13 21:09:11');
72
73-- Add Data for test 3 (99ST - FAMIX1212OZCS)
74INSERT INTO `customer_items` (`cid`, `item`, `custno`, `week_of`, `price`, `saved_on`) VALUES
75 (NULL, 'FAMIX1212OZCS', '99ST', '2017-12-11', 6.60, '2017-12-07 16:11:46'),
76 (NULL, 'FAMIX1212OZCS', '99ST', '2017-11-27', 6.60, '2017-11-22 17:04:13'),
77 (NULL, 'FAMIX1212OZCS', '99ST', '2017-11-20', 6.60, '2017-11-16 19:09:14'),
78 (NULL, 'FAMIX1212OZCS', '99ST', '2017-11-13', 6.60, '2017-11-09 19:34:49'),
79 (NULL, 'FAMIX1212OZCS', '99ST', '2017-11-06', 6.60, '2017-11-02 19:10:20'),
80 (NULL, 'FAMIX1212OZCS', '99ST', '2017-10-30', 6.60, '2017-10-26 18:13:32'),
81 (NULL, 'FAMIX1212OZCS', '99ST', '2017-10-23', 6.60, '2017-10-19 18:30:44'),
82 (NULL, 'FAMIX1212OZCS', '99ST', '2017-08-07', 6.60, '2017-08-03 16:46:47'),
83 (NULL, 'FAMIX1212OZCS', '99ST', '2017-07-10', 6.60, '2017-07-07 16:19:37'),
84 (NULL, 'FAMIX1212OZCS', '99ST', '2017-06-26', 6.60, '2017-06-22 16:12:35'),
85 (NULL, 'FAMIX1212OZCS', '99ST', '2017-04-03', 6.60, '2017-03-30 20:29:50'),
86 (NULL, 'FAMIX1212OZCS', '99ST', '2017-03-13', 6.60, '2017-03-09 18:18:26'), -- Expect this for current price start date
87 (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
88 (NULL, 'FAMIX1212OZCS', '99ST', '2017-02-13', 6.60, '2017-02-09 17:08:33'),
89 (NULL, 'FAMIX1212OZCS', '99ST', '2016-12-12', 6.60, '2016-12-08 19:02:19'),
90 (NULL, 'FAMIX1212OZCS', '99ST', '2016-12-05', 6.60, '2016-12-01 16:48:14'),
91 (NULL, 'FAMIX1212OZCS', '99ST', '2016-11-14', 6.60, '2016-11-10 15:47:55'),
92 (NULL, 'FAMIX1212OZCS', '99ST', '2016-10-10', 6.60, '2016-10-04 12:22:09');
93
94-- Add Data for test 4 (KBSF - CAST1255B )
95INSERT INTO `customer_items` (`cid`, `item`, `custno`, `week_of`, `price`, `saved_on`) VALUES
96 (NULL, 'CAST1255B', 'KBSF', '2017-12-11', 6.60, '2017-12-07 16:11:46'),
97 (NULL, 'CAST1255B', 'KBSF', '2017-02-13', 6.60, '2017-02-09 17:08:33'),
98 (NULL, 'CAST1255B', 'KBSF', '2016-12-12', 6.60, '2016-12-08 19:02:19'),
99 (NULL, 'CAST1255B', 'KBSF', '2016-12-05', 6.60, '2016-12-01 16:48:14'),
100 (NULL, 'CAST1255B', 'KBSF', '2016-11-14', 6.60, '2016-11-10 15:47:55'),
101 (NULL, 'CAST1255B', 'KBSF', '2016-10-10', 6.60, '2016-10-04 12:22:09'); -- Expect this for current price start date