Only output last record from a cte

Hi experts

I am currently working on alot of attendance reports

The code below allows the user to enter two value say for example 5 and 9.  This will then show all the students who have missed between 5 and 9 consecutive days off their course (student has been absent for all classes each day).  The code also shows which classes (subjects) that the students have missed for each day so if you have 15,000 students quite alot of information is outputted.

What I would like to do is a summary report like for each student only have the last class they attended and what date that occured and how many days absent and possibly absent start and absent end.

Basically I need to get the last record for each student and only output this to the user

Thanks
--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


 --  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
	where stage_code = 'ENR'
	and moa in ('01', '02', '20')
    order by x.student_id,x.[date],x.[start_time]

Open in new window

LVL 3
lisa_mcAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ericpeckhamCommented:
The usual practice for getting the last record of anything is...

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

Open in new window

0
lisa_mcAuthor Commented:
hi eric so how would I apply it to the above code
0
lisa_mcAuthor Commented:
i put top 1 in on line 90 and it will only give me first record of all students but I need last recrod for each student
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ericpeckhamCommented:
Don't have time to study your whole query, but I think the following should do the trick (I have slightly modified your final select):

 
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

Open in new window


Note the desc to order the start date in descending order - I presume that will give you the last record.
0
lisa_mcAuthor Commented:
hi again eric

again thats only pulling out one student record out of my whole dataset

but I need one record for each student
0
ericpeckhamCommented:
Oh, wait - you want the last record for each student, not for all students.  Duh! :-)  Solution coming...
0
lisa_mcAuthor Commented:
yeah thats right :-)

I need the last class that the student attended
0
ericpeckhamCommented:
Ok - trying to save my reputation after that really dumb answer I already gave - I've modified your code - my additions have been commented with my initials EP:

 
--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]

Open in new window


The cte (cte3) I added should get only one record for each student, and then we join to that in the final select, which should also result in only one record for each student.  I'm sorry, as I don't have your base tables, I couldn't test the code, but even if there's an error in it, I hope you get the idea and that with minor adjustments it will work for you.  Good luck!
0
lisa_mcAuthor Commented:
hi again

Im getting two errors

Msg 209, Level 16, State 1, Line 34
Ambiguous column name 'Abstart'.
Msg 209, Level 16, State 1, Line 34
Ambiguous column name 'Abend'.
0
ericpeckhamCommented:
Ah, of course - you need to now prefix them as follows, because of the addition join with the same column names:

 
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]

Open in new window

0
lisa_mcAuthor Commented:
sorry to say thats not giving me one record for example I have a student appearing 15 times

from what I can see all of the students have more than one record
0
ericpeckhamCommented:
Hmm, your complex query is going to need more study and time, then, which I'm afraid I don't have at the moment.  I'll come back to this later on, if someone hasn't given you the solution before then.  I was hoping you would get some ideas from what I was doing, and then apply them correctly to your situation.  There must be another statement that is returning multiple records with the same dates - if the multiple records you're seeing for each student are identical, do SELECT DISTINCT in your final select statement.  Otherwise, I hope either you or someone else will be able to find you a solution.
0
lisa_mcAuthor Commented:
yeah there are records for the same dates as a student can have mnore than one class per day

some students can have 1 class some can have 4 classes per day - this depends on the course
0
Mark WillsTopic AdvisorCommented:
You have also asked for a similar thing over at : http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_26884477.html (admittedly as an add-on to the question in progress there).

So, will also post the code below into that thread, but, might need to work on those 5 and 9 day params. The code below is pretty much week aligned (and 4 weeks at that) and pretty much dependant on the other requirement in that other thread.

So, if you would like to finish off that other one first, we can then continue the summary requirement in here...

if object_id('tempdb..#absences','U') is not null drop table #absences

select dense_rank() over (partition by student_id order by week_no) as weekrn
--    ,dense_rank() over (partition by student_id order by week_no,day_num) as dayrn
      ,isnull(absence_code,'') as absence_ind
      ,*
into  #absences
from  nrc_eRegisters_allstudents_testing_tb 
where acad_period = '10/11'

create index idx_temp_absences_1 on #absences (student_id, weekrn, absence_ind)
create index idx_temp_absences_2 on #absences (student_id, date, absence_ind)

;with absences_cte as
( -- this will return the absence "blocks" of days - at the moment 4 school weeks using weekrn+4 indicates 4x7 = 28 "school" days not 28 calendar days
 select a.student_id, a.date as start_date, b.date as end_date  
 from #absences a
 outer apply (select top 1 date from #absences c where c.student_id = a.student_id and c.weekrn = a.weekrn+4 and c.day_num >= a.day_num ) b   
 where a.absence_ind = 'O'
 and a.date < getdate()
 and b.date is not null
 and not exists (select NULL from #absences c where c.student_id = a.student_id and c.date between a.date and b.date and c.absence_ind <> 'O')
 group by a.student_id, a.date, b.date
)
Select distinct ab.*
from absences_cte c
outer apply (select top 1 * from #absences na where na.student_id = c.student_id and na.date < c.start_date and na.absence_ind <> 'O' order by student_id, date) ab

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lisa_mcAuthor Commented:
perfect solution

thanks again
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.