# Get last record for each student in database

Hi

I have attached code below thats shows the first record for each student in my database which isn't correct

This code is outputing students who have missed 28 school days and I need it to show the last class that the student was absent not the last record in the database

If a register isnt marked the value is null

Hope this makes sense

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    -- not used
,isnull(absence_code,'') as absence_ind
,*
into  #absences
from  nrc_eRegisters_allstudents_testing_tb

create index idx_temp_absences_1 on #absences (student_id, weekrn, absence_ind)    -- dayrn not used so removed from index
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

LVL 3
###### Who is Participating?

x
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.

Hi Lisa,

hw are you??

It might helps.

- Bhavesh
select c.*
from absences_cte c, (select student_id, max(end_date)endDate
from absences_cte
group by student_id
)d
where c.student_id = D.Student_ID
And c.end_date = d.end_date

0
Author Commented:
hi brichsoft

Im good how are you?

I replaced line 25, 26, 27 with the code you supplied above and I have one error

Msg 207, Level 16, State 1, Line 32
Invalid column name 'end_date'.
0
hi,

i'm fine....

pls check this.

select c.*
from absences_cte c, (select student_id, max(end_date)endDate
from absences_cte
group by student_id
)d
where c.student_id = D.Student_ID
And c.end_date = d.enddate
0
Author Commented:
hi again

Testing this for one student and I am getting

student id  start_date end_date

this dates occur halfway through year and neither of them is the date of the last class that the student was absent
0
Author Commented:
oh sorry apologies

the end date is right
0
Author Commented:
i just need the information for that student out as well

eg what module they missed, start_time, end time etc
0
*laughing* And I thought you wanted the last day present before all those absences :)

(thats what the code does now)

So, to get the last day absent... that is something quite a bit different because there could be "groups" of absences, and assume you want the last one absent per group ?

e.g.

absent weeks 4-8 = report last absent in week 8
attended weeks 9-12
absent weeks 13-17 = report last absent in week 17

is that correct ?

0
Great Expert entered.. =)

I'm out now....
0
Hey Brichsoft - come back you chicken *laughing*
0
Author Commented:
hi again mark

do you know something I am staring at this stuff for so long I have completly lost focus

I do have to do the last class that they were present for but the code above only shows me the very fist class that that student had
0

0
Author Commented:
eg the date that I am geting back is

08/09/2010 which is the first class that student has
0
@lisa_mc

Yep the code at the top in your question is meant to be returning the last class ATTENDED prior to the absence

as per "would there be any way to show the last class each student attended " in : http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_26884477.html#a35233763

Will need to debug the code to make sure it is the last class present, rather than the first class. Reckon it will be the ORDER BY and should be date desc (not asc) but we probably have to add in start time and end time as well to that order by.

Gimme a moment...

@brichsoft:
*laughing* welcome back - where you been ?
0
Author Commented:
ok np thanks

0
OK, think it is the Order By...

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    -- not used
,isnull(absence_code,'') as absence_ind
,*
into  #absences
from  nrc_eRegisters_allstudents_testing_tb

create index idx_temp_absences_1 on #absences (student_id, weekrn, absence_ind)    -- dayrn not used so removed from index
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 desc, end_time desc, start_time desc) ab

0
Author Commented:
im testing on e particular student who has been absent 2 periods of four weeks

first absence is 15/9/2010 - 19/11/2010
second absence is 07/01/2011 - 11/03/2011

the to records I am getting outputted for this student is 10/09/2010 and which seems to be the first date the student was absent

and 17/12/2011 which is the date before the second group of four weeks absent

do you want me to send you this particular student would that make it easier
0
0
Author Commented:
sample student attached
test-student.xls
0
Author Commented:
ive been checking students who have only missed one block of 4 weeks and it seems to be working fine

checking a few more now
0

Not for Lisa_mc   (secret expert only business)

@brichsoft : If you wanna play with the spreadsheets, all I have been doing is importing the spreadsheet into a table and running the above code. e.g.

-- definitely NOT for lisa

drop table nrc_eRegisters_allstudents_testing_tb
Select * into nrc_eRegisters_allstudents_testing_tb FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:\ee\test-student.xls;HDR=Yes;imex=1', 'SELECT * FROM [sheet1\$]') as a

0
OK, the challenge with the example student is the classes before the first absence have NULL as the absence_code

All I was checking was not "O", so we need to make sure that it is one of '/', 'P','C' etc... Or, more simply put, checking the original absence_code for isnull(na.absence_code,'O') <> 'O'

And (feeling slightly foolish) when I was importing the test data, the NULL value in the absence_code was importing as a four character string, not as NULL

So, with that in mind :) and a new check to eliminate NULL selection (which we need to do because of the outer apply anyway) then :

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    -- not used
,isnull(absence_code,'') as absence_ind
,*
into  #absences
from  nrc_eRegisters_allstudents_testing_tb

create index idx_temp_absences_1 on #absences (student_id, weekrn, absence_ind)    -- dayrn not used so removed from index
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_code <> 'O' order by student_id, date desc, end_time desc, start_time desc) ab
where ab.absence_code is not NULL

0

Experts Exchange Solution brought to you by

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

Author Commented:
ok thats great thats coming back with the date 17/12/2010 which is correct

going to test a few more

one more thing I need to put a counter in there to say how many days they have been absent

eg for this particular student last class attended was 17/12/2010 but from their first absence on 07/01/2011 until the 11/03/2011 the period which cause them to breach 4 weeks absent they have missed 17 days

maybe I should post this as another question :-)

0
Author Commented:
ill post another question in a min

one more question mark

say I wanted to check students who have breached 3 weeks would I change the code

weekrn + 4 to weekrn + 3
0
Yep... So long as you are counting "school weeks" and not calendar periods then simply change the "weekrn + 4"

Oh, and you dont have to keep posting new questions.

Just put all the details you need as output.
0
Author Commented:
i have posted another question about the counter and would be very grateful for your help again :-)

0
Author Commented:
sorry im posting another question as I think you deserve more than 500 points as you have been very good helping me

if I wanted to say like above instead of 4 weeks off i now wanted >=3 weeks off but less than 4 weeks off is that possible
0
OK, I wont complain, but the points are only a small part of why I do this. Mainly interested in helping and getting the hard questions, so I dont pop up all the time, just for the good times :)

Not so sure about a "range" because of the day element.

Doing >= weekrn + 3 is easy enough, but doing >= 3 weeks and < 4 weeks means we need to cut off the 4th week at the end of the (prior) day in that 4th week.

So if our first day absent was Week 3 Tue  then we want to extract everything up to Week 6 Tue or Week 7 Mon   (being less than 4 weeks). Which we can do, but that "where" statement changes shape quite a bit when checking day_num. And then we will need a slightly different "exists" to accommodate an acceptable range, because we might have an attended day anywhere within the acceptable range.

Hmmmm.... would have to think about the "range" a fair bit me thinks...
0
Author Commented:
well I suppossed thats something to think about

believe me you deserve the points as every part of that question was hard

would you be able to help me with my other question if poss

ID: 26918849

thanks
0
Author Commented:
sorry bit confused

>=weekrn+3 - does this mean every student who has missed 3 or more weeks
so if a student has missed 4 weeks they would show here

then what does weekrn+3 mean will this not also show people who have missed 3 or more weeks

0
OK, now posting in that other thread.

Have we finished in this one ?
0
Author Commented:
yeah just want to know what does weekrn+3

doess this mean 3 weeks and more or exactly 3 weeks

thanks
0
Ooops, didnt see the above question.

>=weekrn+3 - does this mean every student who has missed 3 or more weeks
so if a student has missed 4 weeks they would show here

Yes thats what I said, but thinking it is flawed. I wouldnt be doing this. More likely trying to show everything (which is wrong). That whole "range" thing requires a fair bit of thinking through.

then what does weekrn+3 mean will this not also show people who have missed 3 or more weeks

Yes that is correct. it will show at least 3 weeks (and it becomes a bit like a rolling period of 3 weeks)

0
just to clarify

doess this mean 3 weeks and more or exactly 3 weeks

it means at least 3 weeks and could be more e.g.

wk1 'O'  shows
wk2 'O'  shows
wk3 'O'  shows
wk4 '/'
wk5 '/'
wk6 'O'  shows
wk7 'O'  shows
wk8 'O'  shows
wk9 'O'  shows
wk10 '/'
wk11 '/'

0
Author Commented:
thats just what i thought thanks again
0