How can I see the live teacher-child ratio at any given time. (Please add a custom report).

Posted on: 12/12/2018 7:00:12 PM
Replied by : support@icaresoftware.com

Dear User,

Use the Add Custom Report feature to view the live teacher-child ratio. Copy & paste the following query in the Custom Report pop-up and click Test/Run Query link to view/export the output;

-- QUERY STARTS HERE --

;with finalData as( select * from(select  isnull(RoomName,'Not-specified') as RoomName,'Child' as type,'CCount' as Childtype,isnull(count(distinct pa.id),0) as ChildCount,rs.LegislatedRatio 

from po_roomstructure rs
inner join cr_child_room crm on rs.RoomId=crm.RoomId 
inner join pa_Attendance pa on crm.Child_Id=pa.id and ischild=1
where convert (varchar(10),pa.date,101)=convert (varchar(10),getdate(),101)
group by roomname,LegislatedRatio
UNION 
select  isnull(RoomName,'Not-specified') as RoomName,'Staff' as type,'SCount' as Childtype,isnull(count(distinct pacopy.id),0) as ChildCount,rs.LegislatedRatio 
from po_roomstructure rs
inner join sr_Staff_HireDischarge hd on rs.RoomId=hd.RoomId
inner join pa_Attendance pacopy on hd.Staff_id=pacopy.id and ischild=0
where convert (varchar(10),pacopy.date,101)=convert (varchar(10),getdate(),101)
group by roomname,LegislatedRatio) table1 
PIVOT  (sum([ChildCount])   FOR [Type] IN (Child,Staff)) AS p)
select  RoomName as 'Room', isnull(LegislatedRatio,0) as 'Required Ratio', isnull(suM(Cast(Staff as INT) ),0) as 'Staff Count', isnull(suM(Cast(Child as INT) ),0) as 'Child Count', case when  (convert(varchar(max), isnull(sum(Staff),0.00))='0'  and convert(varchar(max), isnull(sum(child),0.00))!='0') then 'N/A' when   ( convert(varchar(max), isnuLL(sum(child),0.00))='0'   and convert(varchar(max), isnull(sum(Staff),0.00))='0') then '0.00'  else   isnull(convert(varchar(max), convert(decimal(18,2),COnvert (decimal(18,2),sum(child))/convert(decimal(18,2),sum(Staff)))),0.00)  end as 'Live Ratio' from finalData group by roomname,LegislatedRatio

-- QUERY ENDS HERE --
 
NOTE: Since this report is based on attendance data, please mark attendance for children and staff before testing/running the query.
 

Click here to see the help article on using Custom Reports feature.

Please free to contact us here or at support@icaresoftware.com for any questions or suggestions.

Thank you,

iCare Support

Leave a Comment / Question

Copyright © 2020 Orgamation Technologies, Inc. All rights reserved.