LSSD Daily Attendance Report

LSSDreport.sql

	select 
			--s.clastname as 'lastname', 
			--s.cfirstname as 'firstname',
			s.clastname + ', ' + s.cFirstName + ' (' + ltrim(rtrim(cast(s.istudentid as CHAR))) + ')' as [combinedname],
			dbo.getgradesname(s.igradesid) as [grade],
			h.cName as [homeroom],
			l.cphone as [phone],
			--a.iClassID as [class], 
			--a.ddate as [date],
			(	select COUNT(*) from Attendance att 
				join AttendanceReasons attr 
					on attr.iAttendanceReasonsID = att.iAttendanceReasonsID
				join AttendanceStatus atts
					on atts.iAttendanceStatusID = att.iAttendanceStatusID
				where s.istudentid = att.istudentid
				and att.iAttendanceStatusID = 2		--absent
			) as [TotalAbs],
			(	select COUNT(*) from Attendance att 
				join AttendanceReasons attr 
					on attr.iAttendanceReasonsID = att.iAttendanceReasonsID
				join AttendanceStatus atts
					on atts.iAttendanceStatusID = att.iAttendanceStatusID
				where s.istudentid = att.istudentid
				and att.iAttendanceStatusID = 3		--late
			) as [TotalLate],
			(	select COUNT(*) from Attendance att 
				join AttendanceReasons attr 
					on attr.iAttendanceReasonsID = att.iAttendanceReasonsID
				join AttendanceStatus atts
					on atts.iAttendanceStatusID = att.iAttendanceStatusID
				where s.istudentid = att.istudentid
				and att.iAttendanceStatusID = 6		--leave early
			) as [TotalLE],
			a.iBlockNumber as [period]
		from student s
		join Homeroom h
			on h.iHomeroomID = s.iHomeroomID
		join Location l
			on l.ilocationid = s.ilocationid
		join Attendance a
			on	a.iStudentID = s.iStudentID and (a.ddate >= 'Jan 1, 2012') and (a.dDate <= 'Mar 8, 2013')