Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.
;with cte as (SELECT acad_period, student_id, student_name, student_surname, stage_code, campus_desc, dept_code, dept_desc, c_title, c_initial, c_surname, course_code, course_period, course_desc, register_id, register_group, module_code, module_period, module_desc, day_num, day, week_no, start_time, end_time, date as 'date_of_first_absence', absence_code, moa, ROW_NUMBER() OVER (PARTITION BY register_id, register_group, student_id ORDER BY date,start_time) AS rownum FROM eRegisters_allstudents_tb where stage_code like 'ENR' ) , XX as (select a.register_id,a.register_group,a.student_id,a.rownum,count(*) as Missed From Cte as a Inner join cte as B on a.rownum<=b.rownum and a.register_id=b.register_id and a.register_group=b.register_group and a.student_id=b.student_id and a.absence_code=b.absence_code Where A.absence_code='O' and not exists (select x.student_id from cte as x where a.student_id=x.student_id and a.register_id=x.register_id and a.register_group=x.register_group and x.rownum between a.rownum and b.rownum and ( (x.absence_code <> 'O') or (x.absence_code is NULL)) ) group by a.register_id,a.register_group,a.student_id,a.rownum having count(*) >= 2 ) , yy as (select a.register_id,a.register_group,a.student_id,a.rownum,a.missed From xx as a Where not exists (select x.student_id from xx as x where a.student_id=x.student_id and a.register_id=x.register_id and a.register_group=x.register_group and X.rownum < a.rownum and a.rownum <=x.rownum + x.missed) ) Select A.*,b.missed from Cte as A Inner Join YY as b on a.register_id=b.register_id and a.register_group=b.register_group and a.student_id=b.student_id and a.rownum=b.rownum
|Access Database To Track Coworker Daily Fitness Accomplishments||32||36|
|How to install and run SQL Server Management Studio Express||8||28|
|T-SQL to Update Table Dynamically||5||23|
|ODBC--call failed error Access 2016/SQL||6||15|
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
11 Experts available now in Live!