Only output last record from a cte

SELECT TOP (1) ...
FROM ...
WHERE ...
ORDER BY <column_name> DESC

Select allOpen in new window

select top(1) x.*, AbsentDays, Abstart, Abend, Absents
     from nicisreports..nrc_eRegisters_allstudents_tb as x   --yourtable
                -- student_REgister as x
    Inner Join cte2 as a
      on x.student_id=a.student_id
     and x.[date] between a.Abstart and a.Abend
	where stage_code = 'ENR'
	and moa in ('01', '02', '20')
    order by x.student_id,x.[date],x.[start_time] desc

Select allOpen in new window

--drop table #absencerep
if @pAcadPeriod is null 
begin
   select @pAcadPeriod=MAX(acad_period) 
      from  nicisreports..nrc_eRegisters_allstudents_tb as x   --yourtable
               --  student_REgister as x
end

select student_id,DATE as regdate,CONVERT(integer,0) as numless,CONVERT(integer,0) as absentless
         ,CONVERT(integer,0) as present
         into #absencerep
         from nicisreports..nrc_eRegisters_allstudents_tb as x   --yourtable
                 --student_REgister as x
        where 0 = 1  
		


       
 create clustered index xxx on #absencerep (student_id,regdate,present)

 -- identify students attendance per day
     Insert into #absencerep
         select student_id,[date] as regdate
              ,count(*) as numless
              ,SUM(case absence_code when 'o' then 1 else 0 end) as absentless
              ,SUM(case absence_code when 'o' then 0 else 1 end) as present
            
            from nicisreports..nrc_eRegisters_allstudents_tb as x   --yourtable
                 --student_REgister as x
           where acad_period=@pAcadPeriod
           group by student_id,[date]
           order by 1,2,present
  select @err=@@ERROR,@rows=@@ROWCOUNT
  if @err<>0 
  begin
     --
     set @rc=1
     return @rc
  end

--select * from #absencerep


  
  ;with      
  cte1a as (select * from #absencerep where present=0)  -- absent student days

        --select * from cte1a


,cte2MAX as -- GET MAX ENDDATE FOR A START DATE
       (select a.student_id,a.regdate as Abstart
                  ,MAX(b.regdate) as Abend
                  ,sum(b.numless) as Lessoncount,COUNT(*) as AbsentDays
                  ,SUM(b.absentless) as Absents
                     
          from CTE1a as a   
          Inner Join cte1a as B
            on a.student_id=b.student_id
           and b.RegDate>=a.RegDatE
     where not exists (select student_id from #absencerep as x
                        where a.student_id=x.student_id
                         AND X.RegDate BETWEEN A.RegDate AND B.RegDate
                         AND X.present>0
                      )
      group by a.student_id,a.regdate
     HAVING COUNT(*) >= @minabsence  
     -->4    -- >4 absence of "5 or more days"   
      ) 

--select * from cte2MAX

 
 ,CTE2 AS -- GET THE Abscence PERIOD RANGE FOR A STUDENT
       (SELECT STUDENT_ID,ABstart,abend,lessoncount,absentdays,absents
          FROM (SELECT X.*
                     ,ROW_NUMBER() OVER (PARTITION BY STUDENT_ID,ABEND ORDER BY ABSTART ) AS RN
                  FROM cte2MAX as x
                ) as x
         where RN=1
          and AbsentDays <= @maxabsence
       )
    -- select * from cte2 ORDER BY 1,2
-- EP added following CTE    
,CTE3 AS
(
	SELECT Student_ID, MAX(ABstart) AS ABstart, MAX(abend) AS ABend
	FROM CTE2
	GROUP BY Student_ID, lessoncount, absentdays, absents
)

 --  students with absence periods of 5 or more days

	insert into nrc_INF0149_5consec_daysoff_tb
 
   select x.*, AbsentDays, Abstart, Abend, Absents
     from nicisreports..nrc_eRegisters_allstudents_tb as x   --yourtable
                -- student_REgister as x
    Inner Join cte2 as a
      on x.student_id=a.student_id
     and x.[date] between a.Abstart and a.Abend
-- EP added following join     
    Inner Join cte3 as a1 on x.student_id = a1.student_id and a.ABstart = a1.ABstart
	where stage_code = 'ENR'
	and moa in ('01', '02', '20')
    order by x.student_id,x.[date],x.[start_time]

Select allOpen in new window

select x.*, AbsentDays, a1.Abstart, a1.Abend, Absents
     from nicisreports..nrc_eRegisters_allstudents_tb as x   --yourtable
                -- student_REgister as x
    Inner Join cte2 as a
      on x.student_id=a.student_id
     and x.[date] between a.Abstart and a.Abend
-- EP added following join     
    Inner Join cte3 as a1 on x.student_id = a1.student_id and a.ABstart = a1.ABstart
	where stage_code = 'ENR'
	and moa in ('01', '02', '20')
    order by x.student_id,x.[date],x.[start_time]

Select allOpen in new window