select distinct(se.ClientNo)   as [Client ID] , s.school_name as [School]   , se.schoolID as [School ID]   , dbo.GetRefDataDesc(11000, csd.school_year, 2) as [School Year] , Convert(CHAR,se.AttendanceDate,106) as [Attendance Date]   , se.AttendanceDay   as [AttendanceDay]   , se.[Session]    as [Session]   , dbo.GetRefDataGovtCode(11040, se.AttendanceType, 2) as [AttendanceType]   , (select IsNull(count(se2.[AttendanceType]),0)  from SchoolEducationAttendance se2   = s2.school_ID          where se2.ClientNo = c.Client_No       and AttendanceType not in (select AttendanceType from SchoolAttendanceTypeMapping where AttendanceParentType = 5)   t)      as [Total Available_]     ,(select IsNull(count(se2.[AttendanceType]),0) from SchoolEducationAttendance se2          where se2.ClientNo = c.Client_No       and AttendanceType in (select AttendanceType from SchoolAttendanceTypeMapping where AttendanceParentType = 1)   t)      as [Total Present_]     ,(select IsNull(count(se2.[AttendanceType]),0) from SchoolEducationAttendance se2          where se2.ClientNo = c.Client_No       and AttendanceType in (select AttendanceType from SchoolAttendanceTypeMapping where AttendanceParentType = 2)   t)      as [Total Authorised Absence_]     ,(select IsNull(count(se2.[AttendanceType]),0) from SchoolEducationAttendance se2          where se2.ClientNo = c.Client_No       and AttendanceType in (select AttendanceType from SchoolAttendanceTypeMapping where AttendanceParentType = 3)   t)      as [Total Unauthorised Absence_]     ,(select IsNull(count(se2.[AttendanceType]),0) from SchoolEducationAttendance se2          where se2.ClientNo = c.Client_No       and AttendanceType in (select AttendanceType from SchoolAttendanceTypeMapping where AttendanceParentType = 4)      )      as [Total Educational Activity_]    from SchoolEducationAttendance se    inner join Clients c on se.ClientNo = c.client_no    inner join Schools s on se.SchoolID = s.school_id    inner join Client_School_Details csd on se.SchoolID = csd.School_ID    where se.EducationAttendanceID = se.EducationAttendanceID    and se.ClientNo = 510798   order by se.schoolID, se.[Attendance Date]