SQLize | PHPize | SQLtest

with recursive date_range AS (select STR_TO_DATE('2005-07-01', '%Y-%m-%d') as july_day union all select july_day + interval 1 day from date_range where july_day < STR_TO_DATE('2005-07-31', '%Y-%m-%d')), july_rented as (select DATE_FORMAT(rental_date, '%Y-%m-%d') as rental_date, store_id, count(rental_id) as rented from rental r join inventory i on r.inventory_id=i.inventory_id where rental_date between '2005-07-01' and '2005-07-31' group by store_id, DATE_FORMAT(rental_date, '%Y-%m-%d')), july_returned as (select DATE_FORMAT(return_date, '%Y-%m-%d') as return_date, store_id, count(rental_id) as returned from rental r join inventory i on r.inventory_id=i.inventory_id where return_date between '2005-07-01' and '2005-07-31' group by store_id, DATE_FORMAT(return_date, '%Y-%m-%d')) select july_day as "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 date_range left join (select rental_date, rented as rented_at_store_1 from july_rented where store_id=1) ren1 on july_day = ren1.rental_date left join (select rental_date, rented as rented_at_store_2 from july_rented where store_id=2) ren2 on july_day = ren2.rental_date left join (select return_date, returned as returned_at_store_1 from july_returned where store_id=1) ret1 on july_day = ret1.return_date left join (select return_date, returned as returned_at_store_2 from july_returned where store_id=2) ret2 on july_day = ret2.return_date