Hint: Their teams ended up taking quizzes, too.
;with cte as -- identify the columns of interest/the academic year ( select [acad_period] as YR ,convert(datetime,[date]) as RegDate ,convert(datetime,[date]+' '+[start_time]) as lessStart ,x.* from nicisreports..nrc_eRegisters_allstudents_tb as x --yourtable Where [acad_period]='10/11' ) -- select * from cte , CTE1 as -- identify students who have been off a full day (select student_id,regdate,MIN(lessstart) firstless,MAX(lessStart) lastless ,count(*) as numless ,SUM(case absence_code when 'o' then 1 else 0 end) as absentless from cte group by student_id,regdate Having SUM(case absence_code when 'o' then 1 else 0 end) = COUNT(*) ) -- select * from cte1 ,cte2 as -- (select a.student_id,MIN(a.regdate) as Abstart ,MAX(b.regdate) as Abend ,COUNT(*) as Lessoncount,COUNT(distinct c.regdate) as AbsentDays ,SUM(case absence_code when 'o' then 1 else 0 end) as Absents from CTE1 as a Inner Join cte1 as B on a.student_id=b.student_id and b.RegDate>a.RegDate Inner Join cte as c on a.student_id = c.student_id and c.RegDate between a.RegDate and b.regdate where b.RegDate >= DATEADD(DAY,5,a.regdate) group by a.student_id having COUNT(distinct c.regdate) >= 5 ) ,cte3 as -- students with absence periods of 5 or more days ( select * from cte2 Where Absents=lessoncount ) select x.*, AbsentDays, Abstart, Abend from cte as x Inner Join cte3 as a on x.student_id=a.student_id and x.RegDate between a.Abstart and a.Abend order by x.student_id,x.lessstart
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.