SQLize Online / PHPize Online  /  SQLtest Online

WITH RECURSIVE dates AS ( SELECT CAST('2005-07-01' AS DATE) date UNION ALL SELECT date_add(date, INTERVAL 1 DAY) FROM dates WHERE date < '2005-07-31' ), store_1_discs_issued AS ( SELECT DATE_FORMAT(rental_date, '%Y-%m-%d') AS date, COUNT(inventory_id) AS rented_at_store_1 FROM rental JOIN inventory USING(inventory_id) WHERE DATE_FORMAT(rental_date, '%Y-%m-%d') IN (SELECT * FROM dates) AND store_id = 1 GROUP BY DATE_FORMAT(rental_date, '%Y-%m-%d')), store_2_discs_issued AS ( SELECT DATE_FORMAT(rental_date, '%Y-%m-%d') AS date, COUNT(inventory_id) AS rented_at_store_2 FROM rental JOIN inventory USING(inventory_id) WHERE DATE_FORMAT(rental_date, '%Y-%m-%d') IN (SELECT * FROM dates) AND store_id = 2 GROUP BY DATE_FORMAT(rental_date, '%Y-%m-%d')), store_1_discs_returned AS ( SELECT DATE_FORMAT(return_date, '%Y-%m-%d') AS date, COUNT(inventory_id) AS returned_at_store_1 FROM rental JOIN inventory USING(inventory_id) WHERE DATE_FORMAT(return_date, '%Y-%m-%d') IN (SELECT * FROM dates) AND store_id = 1 GROUP BY DATE_FORMAT(return_date, '%Y-%m-%d')), store_2_discs_returned AS ( SELECT DATE_FORMAT(return_date, '%Y-%m-%d') AS date, COUNT(inventory_id) AS returned_at_store_2 FROM rental JOIN inventory USING(inventory_id) WHERE DATE_FORMAT(return_date, '%Y-%m-%d') IN (SELECT * FROM dates) AND store_id = 2 GROUP BY DATE_FORMAT(return_date, '%Y-%m-%d')) SELECT date, IFNULL(rented_at_store_1, 0) AS rented_at_store_1, IFNULL(returned_at_store_1, 0) AS returned_at_store_1, IFNULL(rented_at_store_2, 0) AS rented_at_store_2, IFNULL(returned_at_store_2, 0) AS returned_at_store_2, (IFNULL(rented_at_store_1, 0) + IFNULL(rented_at_store_2, 0)) AS total_rented, (IFNULL(returned_at_store_1, 0) + IFNULL(returned_at_store_2, 0)) AS total_returned FROM dates LEFT JOIN store_1_discs_issued USING(date) LEFT JOIN store_2_discs_issued USING(date) LEFT JOIN store_1_discs_returned USING(date) LEFT JOIN store_2_discs_returned USING(date) ORDER BY date; ; show status like 'Last_query_cost';