MySQL Get all categories using Cross join
-- Each category with each status will be retrieved using CROSS JOIN (between categories and statuses without any join condition), then the LEFT JOIN for comparing and calculation the actual complaint counts.
select c.name as category, ss.name as status, Count(aa.complaint_id) as ComplaintCount
from categories c
cross join statuses ss
left join (
select cc.id as complaint_id, cc.category_id, cc.status_id,cc.consulate_office_id
from
complaints cc
) aa on aa.category_id=c.id and aa.status_id=ss.id -- and aa.consulate_office_id=12
group by c.name ,ss.name
-- Same query but now with months instead of categories
ReplyDeleteDELIMITER //
-- drop PROCEDURE Testproc;
CREATE PROCEDURE Testproc()
BEGIN
declare v_CurrentDate datetime(3) ; declare v_PreviousDate datetime(3);
set v_CurrentDate=Cast( Concat(year(NOW(3)),'-' , month(NOW(3)),'-' , '01') as Date);
set v_PreviousDate=TIMESTAMPADD(Month,-11, Cast( Concat(year(NOW(3)),'-' , month(NOW(3)),'-' , '01') as Date));
select v_CurrentDate, v_PreviousDate;
select c.monthname , c.year, ss.name as status , Count(aa.complaint_id) as ComplaintCount
from
(
select distinct monthname,year from datedim where fulldate between v_PreviousDate AND v_CurrentDate
) c
cross join statuses ss
left join
(
select cc.id as complaint_id,cc.status_id,cc.consulate_office_id,monthname(cc.created_at) as MonthName , year(cc.created_at) as year
from
complaints cc
) aa on aa.year=c.year and aa.monthname=c.monthname and aa.status_id=ss.id
group by c.monthname , c.year, ss.name;
END//
DELIMITER ;
call Testproc()