ClassRoomCommunitySudentAttendanceCountForDate

SELECT
 c.id, cmlt.text,
 COUNT(cma.communityMember) AS totalStudent,
COUNT(CASE WHEN cma.attendanceStatus = 'ATTENDANCE_STATUS.PRESENT' THEN cma.id END) AS presentCount,
COUNT( CASE WHEN cma.attendanceStatus = 'ATTENDANCE_STATUS.ABSENT' THEN cma.id END ) AS absentCount,
cma.subjectCode AS subject,
 cma.attendanceContext AS attendanceContext
FROM programtld.worknode wn
JOIN bridgedb.user u
JOIN bridgedb.userrolemap urm ON urm.UserId = u.Id
JOIN programtld.workforce wf ON wn.id = wf.WorkNode AND wf.UserRole = urm.Id
JOIN programtld.worknodecommunity wnc ON wnc.WorkNode = wf.WorkNode
JOIN bridgedb.community c ON c.id = wnc.Community AND  c.CommunityMinorType != 'COMMUNITY_MINOR_TYPE.SCHOOL'
JOIN bridgedb.communityname cn on c.id = cn.Community
JOIN bridgedb.mltext cmlt on cn.name = cmlt.id
JOIN bridgedb.communitymember cm on c.id = cm.communityid AND cm.MembershipMinorType = "COMMUNITY_MEMBER_MINOR_TYPE.STUDENT"
JOIN ltlddb.communitymemberattendance cma on cm.id = cma.communitymember
WHERE wn.EntityID = <::SCHOOL_ID::>
AND u.id = <::CURRENT_USER_ID::> AND u.TenantId = <::CURRENT_TENANT_ID::>
AND IF( Time(cma.Date) = '18:30:00', DATE_ADD(cma.Date, interval 330 MINUTE), DATE(cma.Date) ) =  <::FOR_DATE::>
AND cma.createdTenant = <::CURRENT_TENANT_ID::>
AND wn.isActive=1
AND wf.isActive=1
AND wnc.isActive=1
AND c.isActive=1
GROUP BY cma.communityId,
cma.subjectCode, cma.attendanceContext;