SQLize Online / PHPize Online  /  SQLtest Online

SET @date_start := '2005-07-01'; SET @date_end := '2005-08-01'; SET @difference := DATEDIFF(@date_end, @date_start); WITH RECURSIVE july (date_july) AS ( SELECT @date_start UNION ALL SELECT @date_start := DATE_ADD(@date_start, INTERVAL 1 DAY) FROM july WHERE DATE_FORMAT(@date_start, '%d') < @difference ), data_abount_move_films (date_numb, store_id, rented_at_store, returned_at_store) AS ( SELECT DISTINCT CAST(rental_date AS DATE), store_id, COUNT(*) OVER (PARTITION BY store_id, CAST(rental_date AS DATE)), COUNT(*) OVER (PARTITION BY store_id, CAST(return_date AS DATE)) FROM inventory i INNER JOIN rental r USING(inventory_id) ORDER BY CAST(rental_date AS DATE) ) SELECT DISTINCT CAST(rental_date AS DATE), store_id, COUNT(*) OVER (PARTITION BY store_id, CAST(rental_date AS DATE)), COUNT(*) OVER (PARTITION BY store_id, CAST(return_date AS DATE)) FROM inventory i INNER JOIN rental r USING(inventory_id) ORDER BY CAST(rental_date AS DATE) /*SELECT date_july, store_id, rented_at_store, returned_at_store FROM july j LEFT OUTER JOIN data_abount_move_films damv ON damv.date_numb = j.date_july*/