SQLize Online / PHPize Online  /  SQLtest Online

WITH rent_data AS ( SELECT DATE(rental_date) AS rental_date, staff_id, COUNT(rental_id) AS rented_count FROM rental WHERE rental_date >= DATE('2005-07-01') AND rental_date < DATE('2005-08-01') GROUP BY rental_date, staff_id ) , return_data AS ( SELECT DATE(return_date) AS return_date, staff_id, COUNT(rental_id) AS returned_count FROM rental WHERE return_date >= DATE('2005-07-01') AND return_date < DATE('2005-08-01') GROUP BY return_date, staff_id ) -- , RECURSIVE July_daily AS ( -- SELECT DATE('2005-07-01') AS date -- UNION ALL -- SELECT DATE_ADD(date, INTERVAL 1 DAY) -- FROM July_daily -- WHERE date < '2005-07-31' -- ) SELECT * FROM rent_data rnd FULL OUTER JOIN return_data rtd ON rtd.return_date = rnd.rental_date AND rtd.staff_id = rnd.staff_id -- SELECT -- -- (CASE WHEN MONTH(rental_date) = 07 AND store_id = 1 THEN DATE(rental_date) ELSE NULL END) AS 'rent_1', -- (CASE WHEN MONTH(return_date) = 07 AND store_id = 1 THEN DATE(return_date) ELSE NULL END) AS 'return_1', -- (CASE WHEN MONTH(rental_date) = 07 AND store_id = 2 THEN DATE(rental_date) ELSE NULL END) AS 'rent_2', -- (CASE WHEN MONTH(return_date) = 07 AND store_id = 2 THEN DATE(return_date) ELSE NULL END) AS 'return_2' -- FROM rental r -- JOIN staff s USING(staff_id) -- WHERE MONTH(rental_date) = 07 OR MONTH(return_date) = 07;