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 
where acad_period = '10/11'

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

Open in new window

LVL 3
lisa_mcAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Bhavesh ShahLead AnalysistCommented:
Hi Lisa,

hw are you??

please check out this query.

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

Open in new window

0
lisa_mcAuthor 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
Bhavesh ShahLead AnalysistCommented:
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
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

lisa_mcAuthor 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
lisa_mcAuthor Commented:
oh sorry apologies

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

eg what module they missed, start_time, end time etc
0
Mark WillsTopic AdvisorCommented:
*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
Bhavesh ShahLead AnalysistCommented:
Great Expert entered.. =)

I'm out now....
0
Mark WillsTopic AdvisorCommented:
Hey Brichsoft - come back you chicken *laughing*
0
lisa_mcAuthor 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
Bhavesh ShahLead AnalysistCommented:

YOUR WISH MY COMMAND......
0
lisa_mcAuthor Commented:
eg the date that I am geting back is

08/09/2010 which is the first class that student has
0
Mark WillsTopic AdvisorCommented:
@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
lisa_mcAuthor Commented:
ok np thanks

0
Mark WillsTopic AdvisorCommented:
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 
where acad_period = '10/11'

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

Open in new window

0
lisa_mcAuthor 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
Mark WillsTopic AdvisorCommented:
Yes please...
0
lisa_mcAuthor Commented:
sample student attached
test-student.xls
0
lisa_mcAuthor 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
Mark WillsTopic AdvisorCommented:

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

Open in new window

0
Mark WillsTopic AdvisorCommented:
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 
where acad_period = '10/11'

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

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:
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
lisa_mcAuthor 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
Mark WillsTopic AdvisorCommented:
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
lisa_mcAuthor Commented:
i have posted another question about the counter and would be very grateful for your help again :-)

0
lisa_mcAuthor 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
Mark WillsTopic AdvisorCommented:
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
lisa_mcAuthor 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
lisa_mcAuthor 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
Mark WillsTopic AdvisorCommented:
OK, now posting in that other thread.

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

doess this mean 3 weeks and more or exactly 3 weeks

thanks
0
Mark WillsTopic AdvisorCommented:
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
Mark WillsTopic AdvisorCommented:
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
lisa_mcAuthor Commented:
thats just what i thought thanks again
0
Bhavesh ShahLead AnalysistCommented:
Hey Mark Sir,

Thank u so much...But I am going early from office as my treatment is going on...... =)

But Thank you soo much....
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.