1
select `tix`.`ClientID` AS `ClientID`,sum(if(((to_days(now()) - to_days(`tix`.`StartedDate`)) <= 30),1,0)) AS `TicketsCreated`,sum(if(((`tix`.`Status` = 1) and ((to_days(now()) - to_days(`tix`.`StartedDate`)) <= 30)),1,0)) AS `TicketsCurrentNew`,sum(if(((`tix`.`Status` = 2) and ((to_days(now()) - to_days(`tix`.`StartedDate`)) <= 30)),1,0)) AS `TicketsCurrentOpen`,sum(if(((`tix`.`Status` = 3) and ((to_days(now()) - to_days(`tix`.`StartedDate`)) <= 30)),1,0)) AS `TicketsStalled`,sum(if(((`tix`.`Status` = 4) and ((to_days(now()) - to_days(`tix`.`StartedDate`)) <= 30)),1,0)) AS `TicketsClosed`,sum(if(((`tix`.`Status` = 5) and ((to_days(now()) - to_days(`tix`.`StartedDate`)) <= 30)),1,0)) AS `TicketsBilled`,sum(if(((`tix`.`Status` = 6) and ((to_days(now()) - to_days(`tix`.`StartedDate`)) <= 30)),1,0)) AS `TicketsCombined`,sum(if(((`tix`.`Status` in (4,5,6)) and ((to_days(now()) - to_days(`tix`.`StartedDate`)) <= 30)),1,0)) AS `TicketsResolved`,sum(if(((`tix`.`Status` in (1,2)) and (`tix`.`DueDate` > `tix`.`StartedDate`)),if((now() > `tix`.`DueDate`),1,0),if(((`tix`.`Status` in (1,2)) and (now() > (`tix`.`StartedDate` + interval 96 hour))),1,0))) AS `TicketsOverdue`,sum(if((`tix`.`Status` = 1),1,0)) AS `TicketsUnopened`,avg(if((((to_days(now()) - to_days(`tix`.`StartedDate`)) <= 30) and (`tix`.`Status` in (4,5,6))),timestampdiff(HOUR,`tix`.`StartedDate`,`tix`.`UpdateDate`),NULL)) AS `TicketsAverageClosedHour` from `labtech`.`tickets` `tix` group by `tix`.`ClientID`