Hi I have a complex sql query that gets rejected by phprunner. In MySQL it works just fine.
What is the issue? any help would be appreciated.
The query is as follows:
select rs.Surname,rs.First_Name, case when rs.weight < rs.Lower_weight_limit then 'Weight < Lower_Weight_Limit' when rs.weight > rs.Upper_weight_limit then 'Weight > Upper_Weight_Limit' when rs.w36 > 2 then '> 2kg Loss in Last 36 Days' when rs.w180 >= 10 then '> 10% Weight Loss Last 6 Months' end from ( select curr.Surname, curr.First_Name, curr.Upper_weight_limit Upper_weight_limit, curr.Lower_weight_limit Lower_weight_limit, curr.Weight Weight, w180.weight we180, w36.weight we36, (w180.weight - curr.weight)/w180.weight*100 w180, w36.weight - curr.weight w36 from #Latest Active Weight Entry (select r.Resident_ID Resident_ID, r.surname surname, r.First_Name First_Name, r.Upper_weight_limit Upper_weight_limit, r.Lower_weight_limit Lower_weight_limit, w.Weight Weight, w.Date Date from resident r, weight w, (select wd.resident_id, max(wd.date) date from weight wd where wd.reviewed_by is null group by resident_id) d where r.resident_id = w.resident_id and r.active = 1 and w.reviewed_by is null and w.Resident_ID = d.Resident_ID and w.Date = d.Date group by r.Resident_ID) curr left join #Weight Entry made in last 36 Days (select r.Resident_ID, max(r.surname) surname, max(r.First_Name) First_Name, max(r.Upper_weight_limit) Upper_weight_limit, max(r.Lower_weight_limit) Lower_weight_limit, max(w.Weight) Weight, max(w.Date) Date from resident r, weight w where r.resident_id = w.resident_id and r.active = 1 and w.reviewed_by is null and datediff(now(),date) <= 36 group by r.Resident_ID) w36 on curr.resident_id = w36.resident_id left join #Weight Entry made in last 180 Days (select r.Resident_ID, max(r.surname) surname, max(r.First_Name) First_Name, max(r.Upper_weight_limit) Upper_weight_limit, max(r.Lower_weight_limit) Lower_weight_limit, max(w.Weight) Weight, max(w.Date) Date from resident r, weight w where r.resident_id = w.resident_id and r.active = 1 and w.reviewed_by is null and datediff(now(),date) <= 180 group by r.Resident_ID) w180 on w180.resident_id =curr.resident_id) rs
