1
select `hr`.`ClientID` AS `ClientID`,`hr`.`DateGenerated` AS `DateGenerated`,date_format(`hr`.`DateGenerated`,'%Y-%j') AS `GroupDate`,`hc`.`HealthCheckGuid` AS `HealthCheckGUID`,round(avg(((`hr`.`HealthScore` / `hr`.`HealthMaxScore`) * 100)),0) AS `StraightScore`,round(((sum(`hr`.`HealthScore`) / sum(`hr`.`HealthMaxScore`)) * 100),0) AS `WeightedScore`,(select `p`.`Value` from `labtech`.`plugin_sap_properties` `p` where (`p`.`PropertyName` = 'HowToWeighScores') limit 1) AS `ScoringMethod`,(select (`p`.`Value` * 100) from `labtech`.`plugin_sap_properties` `p` where (`p`.`PropertyName` = 'GreenMin') limit 1) AS `GreenMin`,(select (`p`.`Value` * 100) from `labtech`.`plugin_sap_properties` `p` where (`p`.`PropertyName` = 'YellowMin') limit 1) AS `YellowMin`,(select (`p`.`Value` * 100) from `labtech`.`plugin_sap_properties` `p` where (`p`.`PropertyName` = 'YellowIsUnhealthy') limit 1) AS `YellowIsUnhealthy` from ((`labtech`.`plugin_sap_healthchecks` `hc` join `labtech`.`plugin_sap_healthresults` `hr` on((`hc`.`HealthCheckGuid` = `hr`.`HealthCheckGuid`))) join `labtech`.`plugin_sap_appliedhealthchecks` `ahc` on(((`hr`.`TargetType` = `ahc`.`TargetType`) and (`hr`.`TargetId` = `ahc`.`TargetId`) and (`hr`.`HealthCheckGuid` = `ahc`.`HealthCheckGuid`) and (`ahc`.`CheckEnabled` = 1)))) where (`hr`.`TargetType` = 1) group by `hr`.`ClientID`,date_format(`hr`.`DateGenerated`,'%Y-%j'),`hr`.`HealthCheckGuid` order by `hr`.`ClientID`,date_format(`hr`.`DateGenerated`,'%Y-%j') desc,`hc`.`CheckCategoryId`,`hc`.`CheckOrder`