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. 2555266 fiddles created (37433 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 SUM( DATEDIFF(to_date, GREATEST(from_date, CURDATE() - INTERVAL 400 DAY)) * factor ) FROM ( SELECT IF(h.products_id = @p, @d, CURDATE() + INTERVAL 1 DAY) AS to_date , @d := h.inventory_date AS from_date , (quantity > 0) AS factor , @p := h.products_id AS products_id FROM inventory_history AS h ORDER BY h.products_id ASC , h.inventory_date DESC ) AS v1 WHERE v1.products_id = p.products_id AND v1.to_date >= CURDATE() - INTERVAL 400 DAY ) AS count FROM (SELECT @p := null, @d := null) AS init , products AS p WHERE p.master_categories_id=264 ;
products_id count
23129 236
30590 357
30591 331
 hidden batch(es)