SQLize Online / PHPize Online  /  SQLtest Online

WITH RECURSIVE DateList AS (SELECT '2005-07-01' AS date UNION ALL SELECT date + INTERVAL 1 day FROM DateList WHERE date < '2005-07-31' ), rent_store_1 AS (SELECT DATE(rental_date) AS rental_date_1, COUNT(rental_date) AS rented_at_store_1 FROM rental AS r JOIN inventory AS i ON i.inventory_id=r.inventory_id WHERE store_id=1 AND YEAR(rental_date)='2005' AND MONTH(rental_date)='07' GROUP BY 1), return_store_1 AS (SELECT DATE(return_date) AS return_date_1, COUNT(return_date) AS returned_at_store_1 FROM rental AS r JOIN inventory AS i ON i.inventory_id=r.inventory_id WHERE store_id=1 AND YEAR(return_date)='2005' AND MONTH(return_date)='07' GROUP BY 1), rent_store_2 AS (SELECT DATE(rental_date) AS rental_date_2, COUNT(rental_date) AS rented_at_store_2 FROM rental AS r JOIN inventory AS i ON i.inventory_id=r.inventory_id WHERE store_id=2 AND YEAR(rental_date)='2005' AND MONTH(rental_date)='07' GROUP BY 1), return_store_2 AS (SELECT DATE(return_date) AS return_date_2, COUNT(return_date) AS returned_at_store_2 FROM rental AS r JOIN inventory AS i ON i.inventory_id=r.inventory_id WHERE store_id=2 AND YEAR(return_date)='2005' AND MONTH(return_date)='07' GROUP BY 1) SELECT date, COALESCE(rented_at_store_1,0) AS rented_at_store_1, COALESCE(returned_at_store_1,0) AS returned_at_store_1, COALESCE(rented_at_store_2,0) AS rented_at_store_2, COALESCE(returned_at_store_2,0) AS returned_at_store_2, COALESCE(rented_at_store_1,0)+COALESCE(rented_at_store_2,0) AS total_rented, COALESCE(returned_at_store_1,0)+COALESCE(returned_at_store_2,0) AS total_returned FROM DateList AS dl LEFT JOIN rent_store_1 AS rent1 ON rent1.rental_date_1=dl.date LEFT JOIN return_store_1 AS return1 ON return1.return_date_1=dl.date LEFT JOIN rent_store_2 AS rent2 ON rent2.rental_date_2=dl.date LEFT JOIN return_store_2 AS return2 ON return2.return_date_2=dl.date ; show status like 'Last_query_cost';