;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