The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.
;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.