select eo.NameOfInstitution,cd.Date
,case datepart(DW,cd.Date)
       when 1 then 'Sunday'
       when 2 then 'Monday'
       when 3 then 'Tuesday'
       when 4 then 'Wednesday'
       when 5 then 'Thursday'
       when 6 then 'Friday'
       when 7 then 'Saturday'
       else 'Unknown' end as DayOfWeek
,cet.ShortDescription
,(select count(*) from edfi.StudentSchoolAttendanceEvent ssa where ssa.EventDate=cd.Date and ssa.SchoolId=cd.SchoolId) as DailyAttendance
,(select count(*) from edfi.StudentSectionAttendanceEvent ssa where ssa.EventDate=cd.Date and ssa.SchoolId=cd.SchoolId) as SectionAttendance
from edfi.CalendarDate cd
left join edfi.EducationOrganization eo on eo.EducationOrganizationId=cd.SchoolId
left join edfi.CalendarDateCalendarEvent cde on cde.Date=cd.Date and cde.SchoolId=cd.SchoolId
left join edfi.Descriptor d on d.DescriptorId=cde.CalendarEventDescriptorId
left join edfi.CalendarEventDescriptor acd on acd.CalendarEventDescriptorId=cde.CalendarEventDescriptorId
left join edfi.CalendarEventType cet on cet.CalendarEventTypeId=acd.CalendarEventTypeId
where cd.Date <= getdate()
order by eo.NameOfInstitution,cd.Date