clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591703 fiddles created (45707 in the last week).

CREATE TABLE `inventory_history` ( `inventory_date` date NOT NULL, `products_id` mediumint(11) unsigned NOT NULL, `quantity` mediumint(11) NOT NULL, KEY `inventory_history_pid` (`products_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 hidden batch(es)


INSERT INTO `inventory_history` VALUES ('2014-10-27',13495,1),('2014-10-30',13495,0),('2015-01-09',13495,6),('2015-01-23',13495,5),('2015-02-01',13495,3),('2015-02-16',13495,2),('2015-02-17',13495,1),('2015-04-14',13495,0),('2015-05-23',13495,5),('2015-05-28',13495,4),('2015-06-15',13495,3),('2015-07-03',13495,7),('2015-07-30',13495,6),('2015-08-10',13495,4),('2015-09-03',13495,3),('2015-09-04',13495,4),('2015-09-14',13495,3),('2015-09-23',13495,2),('2015-10-09',13495,1),('2015-10-22',13495,11),('2015-11-19',13495,10),('2015-12-27',13495,7),('2015-12-28',13495,6),('2016-01-15',13495,5),('2016-02-13',13495,8),('2016-04-27',13495,6),('2016-05-07',13495,5),('2016-06-15',13495,4),('2016-07-15',13495,3),('2016-07-20',13495,2),('2016-07-26',13495,5),('2016-08-13',13495,4),('2016-10-22',13495,3),('2016-12-21',13495,2),('2016-12-29',13495,1),('2017-01-11',13495,4),('2017-01-19',13495,3),('2017-01-29',13495,2),('2017-04-22',13495,1),('2017-04-23',13495,2),('2017-05-05',13495,4),('2017-06-05',13495,2),('2017-06-27',13495,4),('2017-07-22',13495,3),('2017-08-14',13495,2),('2017-09-15',13495,1),('2017-12-11',13495,0),('2017-12-13',13495,3),('2018-03-29',13495,2),('2018-04-21',13495,1),('2018-05-19',13495,0),('2018-09-29',13495,8),('2018-11-07',13495,7),('2018-12-29',13495,6),('2019-01-03',13495,5),('2019-03-21',13495,4),('2019-05-02',13495,3),('2019-08-06',13495,2),('2019-08-13',13495,1),('2019-08-16',13495,0),('2019-09-20',13495,4),('2019-09-23',13495,3),('2019-10-10',13495,2),('2019-10-30',13495,1),('2019-12-05',13495,0),('2020-07-18',13495,8),('2020-07-21',13495,7),('2020-08-30',13495,6),('2020-09-21',13495,5),('2020-10-05',13495,3),('2020-10-23',13495,2),('2020-11-06',13495,8),('2020-11-30',13495,7),('2020-12-05',13495,6),('2020-12-06',13495,5),('2020-12-11',13495,4),('2020-12-12',13495,2),('2020-12-16',13495,1),('2021-01-10',13495,0),('2021-01-26',13495,8),('2021-02-10',13495,7),('2021-02-21',13495,5),('2021-02-24',13495,4),('2021-02-27',13495,3),('2021-03-02',13495,4),('2021-03-20',13495,3),('2017-04-16',23129,1),('2017-08-19',23129,0),('2020-01-27',23129,1),('2020-02-07',23129,0),('2020-03-27',23129,1),('2020-05-11',23129,0),('2020-07-01',23129,2),('2020-10-04',23129,1),('2020-12-15',23129,0),('2021-04-08',23129,4),('2019-06-21',30590,2),('2019-08-05',30590,1),('2020-12-13',30590,0),('2021-01-26',30590,2),('2019-06-21',30591,1),('2020-05-27',30591,0),('2020-07-01',30591,1),('2020-09-21',30591,0),('2020-10-26',30591,2);
 hidden batch(es)


CREATE TABLE `products` ( `products_id` int(11) NOT NULL AUTO_INCREMENT, `products_type` int(11) NOT NULL DEFAULT '1', `products_quantity` float NOT NULL DEFAULT '0', `products_model` varchar(32) DEFAULT NULL, `products_upc` varchar(32) DEFAULT NULL, `products_isbn` varchar(32) DEFAULT NULL, `products_image` varchar(64) DEFAULT NULL, `products_price` decimal(15,4) NOT NULL DEFAULT '0.0000', `products_virtual` tinyint(1) NOT NULL DEFAULT '0', `products_date_added` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', `products_last_modified` datetime DEFAULT NULL, `products_date_available` datetime DEFAULT NULL, `products_weight` float NOT NULL DEFAULT '0', `products_status` tinyint(1) NOT NULL DEFAULT '0', `products_tax_class_id` int(11) NOT NULL DEFAULT '0', `manufacturers_id` int(11) DEFAULT NULL, `products_ordered` float NOT NULL DEFAULT '0', `products_quantity_order_min` float NOT NULL DEFAULT '1', `products_quantity_order_units` float NOT NULL DEFAULT '1', `products_priced_by_attribute` tinyint(1) NOT NULL DEFAULT '0', `product_is_free` tinyint(1) NOT NULL DEFAULT '0', `product_is_call` tinyint(1) NOT NULL DEFAULT '0', `products_quantity_mixed` tinyint(1) NOT NULL DEFAULT '0', `product_is_always_free_shipping` tinyint(1) NOT NULL DEFAULT '0', `products_qty_box_status` tinyint(1) NOT NULL DEFAULT '1', `products_quantity_order_max` float NOT NULL DEFAULT '0', `products_sort_order` int(11) NOT NULL DEFAULT '0', `products_discount_type` tinyint(1) NOT NULL DEFAULT '0', `products_discount_type_from` tinyint(1) NOT NULL DEFAULT '0', `products_price_sorter` decimal(15,4) NOT NULL DEFAULT '0.0000', `master_categories_id` int(11) NOT NULL DEFAULT '0', `products_mixed_discount_quantity` tinyint(1) NOT NULL DEFAULT '1', `metatags_title_status` tinyint(1) NOT NULL DEFAULT '0', `metatags_products_name_status` tinyint(1) NOT NULL DEFAULT '0', `metatags_model_status` tinyint(1) NOT NULL DEFAULT '0', `metatags_price_status` tinyint(1) NOT NULL DEFAULT '0', `metatags_title_tagline_status` tinyint(1) NOT NULL DEFAULT '0', `cost` decimal(15,4) DEFAULT NULL, `landed_cost` decimal(15,4) DEFAULT NULL, `landed_cost_multiplier` decimal(15,4) DEFAULT NULL, `par_level` int(11) NOT NULL DEFAULT '0', `barcode` varchar(15) NOT NULL DEFAULT '', `backorder` int(11) NOT NULL DEFAULT '0', `oversize` tinyint(1) NOT NULL DEFAULT '0', `offsite_inventory` int(11) NOT NULL DEFAULT '0', `discount_rate` decimal(15,4) DEFAULT NULL, `selling_price` decimal(15,4) DEFAULT NULL, `exchange_rate` decimal(15,4) DEFAULT NULL, `duty` decimal(15,4) DEFAULT NULL, `shipping` decimal(15,4) DEFAULT NULL, `wholesale_multiplier` decimal(15,4) DEFAULT NULL, `wholesale_price` decimal(15,4) DEFAULT NULL, `mfg_part_number` varchar(32) DEFAULT NULL, PRIMARY KEY (`products_id`), KEY `idx_products_date_added_zen` (`products_date_added`), KEY `idx_products_status_zen` (`products_status`), KEY `idx_products_date_available_zen` (`products_date_available`), KEY `idx_products_ordered_zen` (`products_ordered`), KEY `idx_products_model_zen` (`products_model`), KEY `idx_products_price_sorter_zen` (`products_price_sorter`), KEY `idx_master_categories_id_zen` (`master_categories_id`), KEY `idx_products_sort_order_zen` (`products_sort_order`), KEY `idx_manufacturers_id_zen` (`manufacturers_id`) ) ENGINE=InnoDB AUTO_INCREMENT=35831 DEFAULT CHARSET=latin1
 hidden batch(es)


INSERT INTO `products` VALUES (13495,6,3,'BRS', NULL,NULL,'sb.JPG', 123.0000,0,'2014-10-21 16:27:54','2021-03-02 12:05:39',NULL,3, 1,1,112,56,1,1,0,0,0,1,0,1,0,0, 0,0,123.0000,271,1,0,0,0,0,0,123.0000,NULL,NULL,2,'',0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,''), (23129,6,4,'03A2',NULL,NULL,'03a2-2.JPG',123.0000,0,'2017-04-15 16:27:33','2021-04-08 13:16:03',NULL,-1000,1,1,107,7, 1,1,0,0,0,1,0,1,0,5, 0,0,123.0000,264,1,1,1,1,0,1,123.0000,NULL,NULL,0,'',0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'03A2'), (30590,6,2,'37A2',NULL,NULL,'37a2.jpg', 123.9900,0,'2019-06-21 12:40:04','2021-01-26 11:27:53',NULL,-1000,1,1,107,2, 1,1,0,0,0,1,0,1,0,0, 0,0,123.0000,264,1,1,1,1,0,1,123.0000,NULL,NULL,0,'',0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'37A2'), (30591,6,2,'7A2', NULL,NULL,'7a2.jpg', 123.0000,0,'2019-06-21 12:43:18','2020-10-26 10:52:06',NULL,-1000,1,1,107,2, 1,1,0,0,0,1,0,1,0,-1,0,0,123.0000,264,1,1,1,1,0,1,123.0000,NULL,NULL,0,'',0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'7A2');
 hidden batch(es)


select p.products_id, ( SELECT count(*) from ( select ( SELECT if(v1.quantity, v1.quantity, null) FROM inventory_history v1 where v1.products_id = p.products_id AND v1.inventory_date <= t1.date ORDER BY v1.inventory_date desc limit 1 ) as quantity FROM ( SELECT a.date FROM ( SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS date FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c ) a WHERE a.date >= curdate() - INTERVAL 400 DAY ) t1 having quantity is not null ) x ) as count from products p where p.master_categories_id=264;
Unknown column 'p.products_id' in 'where clause'
 hidden batch(es)