show students absent >= 3 weeks but less than 4 weeks
Hi
I have attached code that shows students who have been absent for 3 or more weeks from their course and I would like to add to the code so that it will restrict students to show students absent >= 3 weeks but less than 4 weeks
I have added more absence_codes ('A','C','E','H','O','P', 'V','W','S','T') basically I wanted to check when the student last attended class if any as all the above codes represent class cancelled, authorised absence, holiday, sick etc. So if not any of these codes I know the student was at class.
Thanks in advance
I have attached code that shows students who have been absent for 3 or more weeks from their course and I would like to add to the code so that it will restrict students to show students absent >= 3 weeks but less than 4 weeks
I have added more absence_codes ('A','C','E','H','O','P', 'V','W','S','T') basically I wanted to check when the student last attended class if any as all the above codes represent class cancelled, authorised absence, holiday, sick etc. So if not any of these codes I know the student was at class.
Thanks in advance
select dense_rank() over (partition by student_id order by week_no) as weekrn
,isnull(absence_code,'') as absence_ind
,*
into #lastabsence
from nrc_eRegisters_allstudents_tb
where acad_period = '10/11'
and stage_code = 'ENR' --shows enrolments
and moa in ('01','20') -- full time students
create index idx_temp_absences_1 on #lastabsence (student_id, weekrn, absence_ind)
create index idx_temp_absences_2 on #lastabsence (student_id, date, absence_ind)
;with absences_cte as
( -- this will return the absence "blocks" of days - at the moment 3 school weeks using weekrn+3 indicates 3x7 = 21 "school" days not 21 calendar days
select a.student_id, a.date as start_date, b.date as end_date
from #lastabsence a
outer apply (select top 1 date from #lastabsence c where c.student_id = a.student_id and c.weekrn = a.weekrn+3 and c.day_num >= a.day_num-1 ) b
where a.absence_ind = 'O'
and a.date < getdate()
and b.date is not null
and not exists (select NULL from #lastabsence 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 #lastabsence na where na.student_id = c.student_id and na.date < c.start_date and na.absence_code not in ('A','C','E','H','O','P', 'V','W','S','T') order by student_id, date desc, end_time desc, start_time desc) ab
where ab.absence_code is not NULL
order by ab.student_id, ab.week_no, ab.day_num, ab.start_time
Sharath S
Can you provide the result of your query with expected result?
What do you want to do with the following scenarios ?
Scenario 1
wk1 attended
wk2 absent
wk3 absent
wk4 absent
wk5 absent
wk6 attended
Scenario 2
wk1 attended
wk2 attended
wk3 absent
wk4 absent
wk5 absent
wk6 attended
Scenario 3
wk1 attended
wk2 absent
wk3 absent
wk4 absent
wk5 absent
wk6 absent
wk7 absent
wk8 attended
My guess is:
Scenario 1 being 4 weeks should not show
Scenario 2 being just 3 weeks should show
Scenario 3 is the curious one - is that two times 3 weeks or one lot of 6 weeks and therefore should not show.
Could you please confirm the above ?
Scenario 1
wk1 attended
wk2 absent
wk3 absent
wk4 absent
wk5 absent
wk6 attended
Scenario 2
wk1 attended
wk2 attended
wk3 absent
wk4 absent
wk5 absent
wk6 attended
Scenario 3
wk1 attended
wk2 absent
wk3 absent
wk4 absent
wk5 absent
wk6 absent
wk7 absent
wk8 attended
My guess is:
Scenario 1 being 4 weeks should not show
Scenario 2 being just 3 weeks should show
Scenario 3 is the curious one - is that two times 3 weeks or one lot of 6 weeks and therefore should not show.
Could you please confirm the above ?
ASKER
hi again
scenario 1 - does not show
scenario 2 - should show 3 week absent
scenario 3 - 6 continuous weeks absent so should not show as it isnt less than 4 weeks
another scenario
week1 absent
week2 absent
week3 absent
week4 present
week5 present
week6 absent
week7 absent
above answer should only show week 1,2 3 and not 6 and 7 as its only 2 weeks
another scenario
week1 absent
week2 absent
week3 absent
week4 present
week5 present
week6 absent
week7 absent
week8 absent
week9 absent
again mshould only show week 1,2,3 and not 6,7,8,9 as this is 4 weeks or more
If a student has 2 seperate periods of absences that fit the criteria of >= 3 weeks but less than 4 weeks then they should appear twice (providing like before they have actually attended a class in between these periods)
scenario 1 - does not show
scenario 2 - should show 3 week absent
scenario 3 - 6 continuous weeks absent so should not show as it isnt less than 4 weeks
another scenario
week1 absent
week2 absent
week3 absent
week4 present
week5 present
week6 absent
week7 absent
above answer should only show week 1,2 3 and not 6 and 7 as its only 2 weeks
another scenario
week1 absent
week2 absent
week3 absent
week4 present
week5 present
week6 absent
week7 absent
week8 absent
week9 absent
again mshould only show week 1,2,3 and not 6,7,8,9 as this is 4 weeks or more
If a student has 2 seperate periods of absences that fit the criteria of >= 3 weeks but less than 4 weeks then they should appear twice (providing like before they have actually attended a class in between these periods)
ASKER
Hi again mark
The purpose of this report is to show a list of students who have been absent 3 weeks or more but less than 4 weeks. When a student is off 4 weeks they have breached an absence policy and their information should be investigated. Now I would like to show a list of students who are about to breach the absence policy and as you know from the previous questions as classes are run on different days, it is better to just to include a range of >= 3 weeks and < 4 weeks
Students who do not fall within the range >=3weeks and < 4 weeks absent at any one period should not be shown
Hope I have explained this ok
The purpose of this report is to show a list of students who have been absent 3 weeks or more but less than 4 weeks. When a student is off 4 weeks they have breached an absence policy and their information should be investigated. Now I would like to show a list of students who are about to breach the absence policy and as you know from the previous questions as classes are run on different days, it is better to just to include a range of >= 3 weeks and < 4 weeks
Students who do not fall within the range >=3weeks and < 4 weeks absent at any one period should not be shown
Hope I have explained this ok
What details are needed on the report - is the summry information from your other question enough ?
ASKER
hi
Yeah the information that I have used in the other report is ok for this one
the only thing that is different is the other report shows students >= 4 weeks
and this one will show >=3 weeks but < 4 weeks
Yeah the information that I have used in the other report is ok for this one
the only thing that is different is the other report shows students >= 4 weeks
and this one will show >=3 weeks but < 4 weeks
Based on our summary report, please have a look at :
NB the last line "having max(missed_weekrn) - last_weekrn = 3" can be commented out whilst testing and a new counter is beingincluded which shows the number of weeks absent.
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
,isnull(absence_code,'') as absence_ind
,*
into #absences
from nrc_eRegisters_allstudents_testing_tb with (nolock)
where acad_period = '10/11'
and stage_code = 'ENR'
--and student_id = 'mar10055848' -- doesnt help when testing other students :)
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 at least 3 school weeks using weekrn+3 indicates 3x7 = 21 "school" days not 21 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+3 and c.day_num >= a.day_num order by c.date asc) 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 >= a.date and c.date < b.date and c.absence_ind <> 'O' )
group by a.student_id, a.date, b.date
)
-- temporary comment out the group by to see details
select student_id, max(missed_weekrn) - last_weekrn as weeks_missed, count(*) as classes_absent, count(distinct missed_date) as days_absent, last_date,last_start_time,last_end_time,last_module_code,last_module_desc
from (
Select distinct c.student_id
,'missed >>' as missed_ind, m.weekrn as missed_weekrn, m.date as missed_date, m.start_time as missed_start, m.end_time as missed_end, m.module_code as missed_module
,'lastclass >>' as last_ind, l.weekrn as last_weekrn, isnull(l.date,0) as last_date, isnull(l.start_time,0) as last_start_time,isnull(l.end_time,0) as last_end_time,isnull(l.module_code,'') as last_module_code,isnull(l.module_desc,'') as last_module_desc
from absences_cte c
inner join #absences m on m.student_id = c.student_id and m.date >= c.start_date and m.date < c.end_date
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 not in ('A','C','E','H','O','P', 'V','W','S','T') order by student_id, date desc, end_time desc, start_time desc) l
) sq
group by student_id, last_weekrn, last_date,last_start_time,last_end_time,last_module_code, last_module_desc
having max(missed_weekrn) - last_weekrn = 3NB the last line "having max(missed_weekrn) - last_weekrn = 3" can be commented out whilst testing and a new counter is beingincluded which shows the number of weeks absent.
Oh, and we might need to add in additional code for the possibility of no "last attended".
ASKER
hi mark
thank you so much for coming back to question
as you know i cannot test until monday so ill reply back then
thanks again
thank you so much for coming back to question
as you know i cannot test until monday so ill reply back then
thanks again
No worries, hope it tests out OK...
ASKER
Hi mark
I have tested that code and most of the results are correct but I am getting a few incorrect students
I have attached their information so that you can test them yourself
In example 1 (first student BAX09038471) the output from the code states that this student has missed 8 classes and 5 days but if you look at the information highlighted you will see that this student is off 6 daysand the student has missed 9 classes
In the second example (FAL07019457) the output from the code states that this student has missed 15 classes and 3 days which is correct but it is also saying that this student has missed 3 weeks but if you look at the highlighted section this student is only off exactly 2 weeks they would of had to have been off thursday 27/01/2011 to make it 3 or more weeks absent but less than 4 weeks
any suggestions??
Thanks
2-example-students.xls
I have tested that code and most of the results are correct but I am getting a few incorrect students
I have attached their information so that you can test them yourself
In example 1 (first student BAX09038471) the output from the code states that this student has missed 8 classes and 5 days but if you look at the information highlighted you will see that this student is off 6 daysand the student has missed 9 classes
In the second example (FAL07019457) the output from the code states that this student has missed 15 classes and 3 days which is correct but it is also saying that this student has missed 3 weeks but if you look at the highlighted section this student is only off exactly 2 weeks they would of had to have been off thursday 27/01/2011 to make it 3 or more weeks absent but less than 4 weeks
any suggestions??
Thanks
2-example-students.xls
Hmmm...
I see FAL07019457 being off for all of weeks 19,20 and 21 - so the code is counting that as three.
The more serious consideration is BAX09038471
I think the code would not count from the Friday 25th in week 26 because that would make it 4 continuous weeks, the three weeks are 27,28 and 29 which makes 8 classes, but really should not be reporting that one because there are more than 3 weeks, but what has thrown it out of whack is there is no friday class in week 29, just the two mondays so the pattern is interrupted as well (and thats why it is not taking it from the Friday 25th because of that pattern and the next day monday 21st needs to be ignored because there is one attendance).
Might need to think a bit more about this case...
I see FAL07019457 being off for all of weeks 19,20 and 21 - so the code is counting that as three.
The more serious consideration is BAX09038471
I think the code would not count from the Friday 25th in week 26 because that would make it 4 continuous weeks, the three weeks are 27,28 and 29 which makes 8 classes, but really should not be reporting that one because there are more than 3 weeks, but what has thrown it out of whack is there is no friday class in week 29, just the two mondays so the pattern is interrupted as well (and thats why it is not taking it from the Friday 25th because of that pattern and the next day monday 21st needs to be ignored because there is one attendance).
Might need to think a bit more about this case...
ASKER
yeah I see what you mean awww so close yet so far
back to the drawing board it seems
do you need any more example data that is showing up as incorrect
back to the drawing board it seems
do you need any more example data that is showing up as incorrect
Well, not entirely back to the drawing board...
I think both are being reported correctly given the conditions and circumstances.
for FAL... all of weeks 19,20 and 21 is three weeks. Nes pas ?
for BAX... then the condition of three weeks from the Friday 25 because of the missing Friday in week 29. However the subsequent Monday does show three weeks.
The big question is do we have to accommodate missing days from the "pattern" of a week ? The code currently checks for the next instance of a class, and for BAX... it happens to be a Monday where there is an attendance being recorded therefore the Friday 25th is being excluded.
So coming up with a "rule" that suit all cases will be interesting. And then, are these exceptions or viable cases that need to be accounted for and if yes, then what is the difference for these that we can clearly identify that makes them either appear or not. I could reasonably argue that they should both appear. Maybe you need to tell me why they shouldnt (or how or why they should).
I think both are being reported correctly given the conditions and circumstances.
for FAL... all of weeks 19,20 and 21 is three weeks. Nes pas ?
for BAX... then the condition of three weeks from the Friday 25 because of the missing Friday in week 29. However the subsequent Monday does show three weeks.
The big question is do we have to accommodate missing days from the "pattern" of a week ? The code currently checks for the next instance of a class, and for BAX... it happens to be a Monday where there is an attendance being recorded therefore the Friday 25th is being excluded.
So coming up with a "rule" that suit all cases will be interesting. And then, are these exceptions or viable cases that need to be accounted for and if yes, then what is the difference for these that we can clearly identify that makes them either appear or not. I could reasonably argue that they should both appear. Maybe you need to tell me why they shouldnt (or how or why they should).
ASKER
ok I messed up again sorry I have been looking at so much data that my head is wrecked and thank you for you very sensible explanation to make me see sense again :-)
saying someone was off wed week 2 , 3, 4 and all classes inbetween I was counting this as 2 weeks as I was going wed to wed to wed whereas I should have been going wed to tues, wed to tues and then wed week 4 would have been start of week 3
I am going to start testing this again with my new way of thinking thanks to you.
For the likes of students like BAX09038471 I presume this miscount only happens when the frist day of absence isnt matched to that exact day 3 weeks later (ie because there was no friday that particular week) it affects this student alot as they are only in mon and friday and considering the amount of bank holidays on a monday and holidays on a friday
The only condition that needs sorted is for student BAX09038471 - at the mo I am happy with the results for this student as it is def >3 weeks but less than 4 weeks that is the main concern but I would like to get the count correct if possible
thanks
saying someone was off wed week 2 , 3, 4 and all classes inbetween I was counting this as 2 weeks as I was going wed to wed to wed whereas I should have been going wed to tues, wed to tues and then wed week 4 would have been start of week 3
I am going to start testing this again with my new way of thinking thanks to you.
For the likes of students like BAX09038471 I presume this miscount only happens when the frist day of absence isnt matched to that exact day 3 weeks later (ie because there was no friday that particular week) it affects this student alot as they are only in mon and friday and considering the amount of bank holidays on a monday and holidays on a friday
The only condition that needs sorted is for student BAX09038471 - at the mo I am happy with the results for this student as it is def >3 weeks but less than 4 weeks that is the main concern but I would like to get the count correct if possible
thanks
ASKER
Hi again
Im going to throw another spanner in the works as I have a problem
I am running the report to see a list of students who have bee off 3 or more weeks but less than 4 weeks. I have attached information of another student who's details have been showing up but should not be as hey have been absent 4 weeks
Looking at the highlighted section we can see that the student is absent from wed week 26 to wed week 29
this with my new way of thinking ;-) is 4 or more weeks
wed week 26 to tues week 27 - week 1
wed week 27 to tues week 28 - week 2
wed week 28 - tues week 29 - week 3
wed week 29 onwards - week 4
Sorry I have highlighted this as I need my data to be very accurate as I don't want the incorrect students to be contacted
Thanks again
student-4-weeks.xls
Im going to throw another spanner in the works as I have a problem
I am running the report to see a list of students who have bee off 3 or more weeks but less than 4 weeks. I have attached information of another student who's details have been showing up but should not be as hey have been absent 4 weeks
Looking at the highlighted section we can see that the student is absent from wed week 26 to wed week 29
this with my new way of thinking ;-) is 4 or more weeks
wed week 26 to tues week 27 - week 1
wed week 27 to tues week 28 - week 2
wed week 28 - tues week 29 - week 3
wed week 29 onwards - week 4
Sorry I have highlighted this as I need my data to be very accurate as I don't want the incorrect students to be contacted
Thanks again
student-4-weeks.xls
ASKER
hi again mark
sorry been off work and I am off all this week so can't test anything.
only replying now as I had pc problems at home im having a good week ;-)
Will be able to test code next tuesday hope you can come back to question then
Many thanks and hope you had a nice easter
sorry been off work and I am off all this week so can't test anything.
only replying now as I had pc problems at home im having a good week ;-)
Will be able to test code next tuesday hope you can come back to question then
Many thanks and hope you had a nice easter
No problems.
Must have missed the previous post. Will start playing with Student-4
Must have missed the previous post. Will start playing with Student-4
ASKER
thanks very much talk to you soon
ASKER
hi mark
back at work so if there is anything you need or any data you need let me know
thanks
back at work so if there is anything you need or any data you need let me know
thanks
Nah, dont delete...
I am back now and ready for a real challenge in life :)
I am back now and ready for a real challenge in life :)
OK, I think the problem with that last one is the day. We are checking to see if week count = 3, but not that the end day fits within the appropriate range. So, it is trickling over into the next period.
So week 29 - week 26 = 3 weeks but there are also days > tuesday (which should be the end of the week).
Hmmm.... this will be fun :)
So week 29 - week 26 = 3 weeks but there are also days > tuesday (which should be the end of the week).
Hmmm.... this will be fun :)
ASKER
Hi mark
welcome back and I agree it is a real challenge in life - I always get the real awkard problems
yeah I agree with what you are saying above
two possibilities need to be looked at
1 (example using week 26 - 29) not only do we need to check week count + 3 but also that day_num (week 26) < day_num (week 29)
2 if a student is off fri of week 26 and then there is not friday in week 29 this student still needs to be recorded as 3 weeks off
anything i can do let me know
thanks again
welcome back and I agree it is a real challenge in life - I always get the real awkard problems
yeah I agree with what you are saying above
two possibilities need to be looked at
1 (example using week 26 - 29) not only do we need to check week count + 3 but also that day_num (week 26) < day_num (week 29)
2 if a student is off fri of week 26 and then there is not friday in week 29 this student still needs to be recorded as 3 weeks off
anything i can do let me know
thanks again
I think scenario 2 is OK.
the other one I am tripping over is :
Start of break = Wed week 26
End of break = Mon week 29
still is three weeks, but if Tuesday week 29 is attended, then it is also not 3 weeks off.
So, I am playing with some of the earlier bits to say exactly 3 weeks rather than at least 3 weeks. But then again, doing that starts to overlap a bit with the logic we have to accomodate scenario 2 - fun stuff :)
One thing we might need is a bit more test data. I am currently playing with the spreadsheet above (the one with 4 weeks) but we might need some more and some that cross over the school holiday / public holiday periods - the ones where simple calendar arithmetic doesnt work.
the other one I am tripping over is :
Start of break = Wed week 26
End of break = Mon week 29
still is three weeks, but if Tuesday week 29 is attended, then it is also not 3 weeks off.
So, I am playing with some of the earlier bits to say exactly 3 weeks rather than at least 3 weeks. But then again, doing that starts to overlap a bit with the logic we have to accomodate scenario 2 - fun stuff :)
One thing we might need is a bit more test data. I am currently playing with the spreadsheet above (the one with 4 weeks) but we might need some more and some that cross over the school holiday / public holiday periods - the ones where simple calendar arithmetic doesnt work.
ASKER
yeah although the above does show 3 weeks using weekrn - it isnt exactly 3 weeks until the student is absent all of tuesday in week 29 - this is why the day_num needs to be checked here
day_num week 26 should be one less than day_num week 29
In my table I only hold the information where the class I active so holidays will not be included in my table.
Ill post them up asap
thanks
I can try and get you some information where day_nums are not the same in the ending week so that you can test the above scenario
day_num week 26 should be one less than day_num week 29
In my table I only hold the information where the class I active so holidays will not be included in my table.
Ill post them up asap
thanks
I can try and get you some information where day_nums are not the same in the ending week so that you can test the above scenario
Yep, that would be good (gulp)
ASKER
okay I have 3 students and if this doesnt wreck your head I dunno what will take a deep breath before you look at it :-)
I have highlighted the sections for each student
student 1+ 2 are off exactly 3 weeks and student 4 is off 4 weeks
need anything else let me know
thanks
3-example-students.xls
I have highlighted the sections for each student
student 1+ 2 are off exactly 3 weeks and student 4 is off 4 weeks
need anything else let me know
thanks
3-example-students.xls
Havent forgotten you, have been working on this. Tricky... But getting close :)
ASKER
hi mark
thats great been off for a few days back in work 2day if you need anything let me know
thanks again
thats great been off for a few days back in work 2day if you need anything let me know
thanks again
OK,
Struggling with BEZ10053752
It ends on Wednesday which is > Tuesday so isnt that a 4th week ?
You are correct, it is doing my head in. Had no idea just how highly variable the week to week (day) patterns are...
Struggling with BEZ10053752
It ends on Wednesday which is > Tuesday so isnt that a 4th week ?
You are correct, it is doing my head in. Had no idea just how highly variable the week to week (day) patterns are...
ASKER
hi mark
they are tough examples I had to change them around so that you could test information where a class is not on the same day every week (doesnt happen very often but as you know can happen)
In the case of BEZ10053752 - the student is 3 or more weeks not exactly 3 weeks
eg tues 08/02/2011 until end of mon 21/02/2011 week 1
tues 22/2/2011 until end of mon 28/02/2011 week 2
anyclass after monday 28/02/2011 this would be week 3
can you check this just to make sure im right as I may get confused again :-)
they are tough examples I had to change them around so that you could test information where a class is not on the same day every week (doesnt happen very often but as you know can happen)
In the case of BEZ10053752 - the student is 3 or more weeks not exactly 3 weeks
eg tues 08/02/2011 until end of mon 21/02/2011 week 1
tues 22/2/2011 until end of mon 28/02/2011 week 2
anyclass after monday 28/02/2011 this would be week 3
can you check this just to make sure im right as I may get confused again :-)
Will check again. The above sounds right - just not showing at the moment. Others are OK.
ASKER
great
thanks very much talk soon
thanks very much talk soon
ASKER
hi mark
the condition we are checking for is absence_code = O to show that they are absent would it be easy to change the code to check for either absence_code = O or absence_code is NULL
the assumption is now that if a register isnt marked then the student is absent - would it be hard to change the code to reflect this???
Mark if you read this I am begging as I really need your help to finish this time is running out
If you can't help (or dont want to) please just let me know as I feel like I am always requesting attention for you (so I can stop bothering you). I mean this in the nicest possible way so please don't take this in a bad way - I just need to know whats happening
I really, really, really do appreciate your help so far
thanks again
the condition we are checking for is absence_code = O to show that they are absent would it be easy to change the code to check for either absence_code = O or absence_code is NULL
the assumption is now that if a register isnt marked then the student is absent - would it be hard to change the code to reflect this???
Mark if you read this I am begging as I really need your help to finish this time is running out
If you can't help (or dont want to) please just let me know as I feel like I am always requesting attention for you (so I can stop bothering you). I mean this in the nicest possible way so please don't take this in a bad way - I just need to know whats happening
I really, really, really do appreciate your help so far
thanks again
:)
I am still here. I have been distracted by real (other) life for a bit.
Checking for NULL is OK too all we do is use the isnull() function e.g. isnull(absence_code,'O') = 'O' will be true for NULL or 'O' so should be easy to do that.
Got pretty close to the three weeks exactly. It is a challenging requirement with non contiguous days and weeks and non repeating day patterns from week to week and variables like holiday periods and individual holidays.
Back on it tonight...
I am still here. I have been distracted by real (other) life for a bit.
Checking for NULL is OK too all we do is use the isnull() function e.g. isnull(absence_code,'O') = 'O' will be true for NULL or 'O' so should be easy to do that.
Got pretty close to the three weeks exactly. It is a challenging requirement with non contiguous days and weeks and non repeating day patterns from week to week and variables like holiday periods and individual holidays.
Back on it tonight...
ASKER
hi
so happy to hear from you thanks for replying
thats great in the meantime I'll test the nulls to see if it is being counted as an absence
if you need anything or need me to do anything let me know
thanks
so happy to hear from you thanks for replying
thats great in the meantime I'll test the nulls to see if it is being counted as an absence
if you need anything or need me to do anything let me know
thanks
OK,
Very confusing...
BEZ should also have Monday 7th absent to qualify for full 3 weeks, other wise it is only 2 weeks and 1 day. The label on your spreadsheet says "exactly 3 weeks" hence some of the confusion.
Now, putting in a check to make NULL a 'O' if and only if there is subsequent activity (ie a 'O' or a '/') means that BEZ having a NULL on 7/02/2011 is now absent since 31st Jan rather than the hghlighted 8th Feb in your spreadsheet and so is also > 3 weeks.
So, the only one in your spreadsheet given the rules, is FAW
So, we now get not only the last anniversary of the absent day, but we now also get the next day of non absent activity. If there are any absences in between those two dates, then it must be more than the required 'n' weeks absent.
I think the following should give more positive results. And if you want 4 weeks, then change the weekrn+3 to weekrn+4
Very confusing...
BEZ should also have Monday 7th absent to qualify for full 3 weeks, other wise it is only 2 weeks and 1 day. The label on your spreadsheet says "exactly 3 weeks" hence some of the confusion.
Now, putting in a check to make NULL a 'O' if and only if there is subsequent activity (ie a 'O' or a '/') means that BEZ having a NULL on 7/02/2011 is now absent since 31st Jan rather than the hghlighted 8th Feb in your spreadsheet and so is also > 3 weeks.
So, the only one in your spreadsheet given the rules, is FAW
So, we now get not only the last anniversary of the absent day, but we now also get the next day of non absent activity. If there are any absences in between those two dates, then it must be more than the required 'n' weeks absent.
I think the following should give more positive results. And if you want 4 weeks, then change the weekrn+3 to weekrn+4
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
,isnull(absence_code,'') as absence_ind
,*
into #absences
from nrc_eRegisters_allstudents_testing_tb with (nolock)
where acad_period = '10/11'
and stage_code = 'ENR'
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)
-- handle a NULL absence ie assume absence='O' when there has been subsequent (real) attendance activity recorded
update #absences set absence_ind = 'O', absence_code = 'O'
where absence_ind = ''
and exists (select NULL from #absences na where na.student_id = #absences.student_id and na.date > #absences.date and na.date <= getdate() and na.absence_code in ('O','/'))
;with absences_cte as
( -- this will return the absence "blocks" of days - at the moment at least 3 school weeks using weekrn+3 indicates 3x7 = 21 "school" days not 21 calendar days
select a.student_id, a.date as start_date, b.date as end_date, isnull(m.date,b.date) as date_back --, a.weekrn, b.weekrn, m.weekrn,*
from #absences a
outer apply (select top 1 date,weekrn from #absences c where c.student_id = a.student_id and c.weekrn >= a.weekrn+3 and c.day_num >= a.day_num order by c.date asc) b
outer apply (select top 1 date,weekrn from #absences c where c.student_id = a.student_id and c.absence_ind <> 'O' and c.date > b.date order by c.date asc) m
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 >= a.date and c.date < b.date and c.absence_ind <> 'O' )
and not exists (select NULL from #absences d where d.student_id = a.student_id and d.date >= b.date and d.date < isnull(m.date,b.date) and d.absence_code = 'O')
group by a.student_id, a.date, b.date, m.date
)
select student_id, count(*) as classes_absent, count(distinct missed_date) as days_absent, last_date,last_start_time,last_end_time,last_module_code,last_module_desc
from (
Select distinct c.student_id ,c.start_date, c.end_date, c.date_back
,'missed >>' as missed_ind, m.weekrn as missed_weekrn, m.date as missed_date, m.start_time as missed_start, m.end_time as missed_end, m.module_code as missed_module
,'lastclass >>' as last_ind, l.weekrn as last_weekrn, isnull(l.date,0) as last_date, isnull(l.start_time,0) as last_start_time,isnull(l.end_time,0) as last_end_time,isnull(l.module_code,'') as last_module_code,isnull(l.module_desc,'') as last_module_desc
from absences_cte c
inner join #absences m on m.student_id = c.student_id and m.date >= c.start_date and m.date < c.end_date
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 not in ('A','C','E','H','O','P', 'V','W','S','T') order by student_id, date desc, end_time desc, start_time desc) l
Where not exists (select NULL from absences_cte d where d.student_id = c.student_id and c.start_date > d.start_date and c.start_date < d.end_date)
) sq
group by student_id, last_weekrn, last_date,last_start_time,last_end_time,last_module_code, last_module_desc
Apologies, think I posted the earlier version of the code...
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
,isnull(absence_code,'') as absence_ind
,*
into #absences
from nrc_eRegisters_allstudents_testing_tb with (nolock)
where acad_period = '10/11'
and stage_code = 'ENR'
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)
-- handle a NULL absence ie assume absence='O' when there has been subsequent (real) attendance activity recorded
update #absences set absence_ind = 'O', absence_code = 'O'
where absence_ind = ''
and exists (select NULL from #absences na where na.student_id = #absences.student_id and na.date > #absences.date and na.date <= getdate() and na.absence_code in ('O','/'))
;with absences_cte as
( -- this will return the absence "blocks" of days - at the moment at least 3 school weeks using weekrn+3 indicates 3x7 = 21 "school" days not 21 calendar days
select a.student_id, a.date as start_date, b.date as end_date, isnull(m.date,b.date) as date_back --, a.weekrn, b.weekrn, m.weekrn,*
from #absences a
outer apply (select top 1 date,weekrn from #absences c where c.student_id = a.student_id and c.weekrn >= a.weekrn+3 and c.day_num >= a.day_num order by c.date asc) b
outer apply (select top 1 date,weekrn from #absences c where c.student_id = a.student_id and c.absence_ind <> 'O' and c.date > b.date order by c.date asc) m
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 >= a.date and c.date < b.date and c.absence_ind <> 'O' )
and not exists (select NULL from #absences d where d.student_id = a.student_id and d.date >= b.date and d.date < isnull(m.date,b.date) and d.absence_code = 'O')
group by a.student_id, a.date, b.date, m.date
)
select student_id, count(*) as classes_absent, count(distinct missed_date) as days_absent, last_date,last_start_time,last_end_time,last_module_code,last_module_desc
from (
Select distinct c.student_id ,c.start_date, c.end_date, c.date_back
,'missed >>' as missed_ind, m.weekrn as missed_weekrn, m.date as missed_date, m.start_time as missed_start, m.end_time as missed_end, m.module_code as missed_module
,'lastclass >>' as last_ind, l.weekrn as last_weekrn, isnull(l.date,0) as last_date, isnull(l.start_time,0) as last_start_time,isnull(l.end_time,0) as last_end_time,isnull(l.module_code,'') as last_module_code,isnull(l.module_desc,'') as last_module_desc
from absences_cte c
inner join #absences m on m.student_id = c.student_id and m.date >= c.start_date and m.date < c.end_date
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 not in ('A','C','E','H','O','P', 'V','W','S','T') order by student_id, date desc, end_time desc, start_time desc) l
Where not exists (select NULL from absences_cte d where d.student_id = c.student_id and c.start_date > d.start_date and c.start_date < d.end_date)
) sq
group by student_id, last_weekrn, last_date,last_start_time,last_end_time,last_module_code, last_module_descASKER
Hi mark
Sorry although I said that they are looking to assume that null means marked absent they have not confirmed this yet - I have to discuss this tomorrow - so for the meantime I need to keep it that they are only absent if absence_code is = 'O' - sorry for the confusion here.
I am just testing this code now and one of the results I am getting back here is a student off 6 + weeks
but the problem I need solved here is to show students off 3 or more weeks but less than 4
this is how I assume weeks
If a student first absent day is tuesday then I check up to next monday and if they are absent then they are off one week.
If a student first day off is a thursday and they are absent up to next wednesday then they are absent 1 week
I gave you that really tough example to show what would happen if a student didnt have a class on a particular week ie
if a student had a class on mon, tues, wed, thursday week 1
and tues, wed, thursday week 2 and the student first absence was tues week 1 then I need to check up to monday week 2 (even though there is no class mon week 2). To do this the day_num needs to be checked as weel as the weekrn.
Ie weekrn = 2 to represent second week but day_num = 1 (represents mon week 2) this is less than day_num = 2 (represents tues week 2) so therefore we know that the student has been off one week
In the highlighted example BEZ.... you get the first tues that he was absent 08/02 then you check till monday next week - absent 1 week
tues until mon next week absent 2 week
tues until mon next week absent 3 weeks
(sorry the dates in this example just from 08/2 - 21/2 this is because I needed to delete some records to make the example tougher)
does this make sense
Sorry although I said that they are looking to assume that null means marked absent they have not confirmed this yet - I have to discuss this tomorrow - so for the meantime I need to keep it that they are only absent if absence_code is = 'O' - sorry for the confusion here.
I am just testing this code now and one of the results I am getting back here is a student off 6 + weeks
but the problem I need solved here is to show students off 3 or more weeks but less than 4
this is how I assume weeks
If a student first absent day is tuesday then I check up to next monday and if they are absent then they are off one week.
If a student first day off is a thursday and they are absent up to next wednesday then they are absent 1 week
I gave you that really tough example to show what would happen if a student didnt have a class on a particular week ie
if a student had a class on mon, tues, wed, thursday week 1
and tues, wed, thursday week 2 and the student first absence was tues week 1 then I need to check up to monday week 2 (even though there is no class mon week 2). To do this the day_num needs to be checked as weel as the weekrn.
Ie weekrn = 2 to represent second week but day_num = 1 (represents mon week 2) this is less than day_num = 2 (represents tues week 2) so therefore we know that the student has been off one week
In the highlighted example BEZ.... you get the first tues that he was absent 08/02 then you check till monday next week - absent 1 week
tues until mon next week absent 2 week
tues until mon next week absent 3 weeks
(sorry the dates in this example just from 08/2 - 21/2 this is because I needed to delete some records to make the example tougher)
does this make sense
ASKER
the more and more I think about this I really do understand that it sounds very complicated
would this way of looking at things help
right check for first absence say in this example student is off tues 01/01/2011 you need to check up to mon 07/01 that they were absent all week. Now if a student is not in class that mon then you could check that all absences from 01/01 until 07/01 ie date of first day of absence + 6 days (if they exist or not) are all absent
ie first date of absence +6 days if all records in between these two dates are absent then they are off one week. then start next absence date of tues 08/01 and check the next 6 days from this and if absent then you know off 2 weeks
would this way of looking at things help
right check for first absence say in this example student is off tues 01/01/2011 you need to check up to mon 07/01 that they were absent all week. Now if a student is not in class that mon then you could check that all absences from 01/01 until 07/01 ie date of first day of absence + 6 days (if they exist or not) are all absent
ie first date of absence +6 days if all records in between these two dates are absent then they are off one week. then start next absence date of tues 08/01 and check the next 6 days from this and if absent then you know off 2 weeks
>> only absent if absence_code is = 'O'
OK, no problems, need to comment out the update on lines 15,16,17 in the above code snippet. It will make a huge difference to your testing.
>> In the highlighted example BEZ....
Yep, been through that one a thousand time and still believe it is not quite three weeks. Needs to include the following Monday otherwise it is short of three weeks.
>> the problem I need solved here is to show students off 3 or more weeks but less than 4
Yeah, I thought that is what I had - it shows up all the spreadsheets so far - even with my added manipulations in the 'raw' data.
So, how about we comment out that update statement ?
OK, no problems, need to comment out the update on lines 15,16,17 in the above code snippet. It will make a huge difference to your testing.
>> In the highlighted example BEZ....
Yep, been through that one a thousand time and still believe it is not quite three weeks. Needs to include the following Monday otherwise it is short of three weeks.
>> the problem I need solved here is to show students off 3 or more weeks but less than 4
Yeah, I thought that is what I had - it shows up all the spreadsheets so far - even with my added manipulations in the 'raw' data.
So, how about we comment out that update statement ?
The problem using date arithmetic is that the next week (ie day + 6) might not exist - not just the day, it might be a week off due to school term break, so we dont really know how many days the next week will be.
ASKER
ok
I have commented out that update statement just running it now - will get back asap
>>>> Yep, been through that one a thousand time and still believe it is not quite three weeks. Needs to include the following Monday otherwise it is short of three weeks. <<<<
can you show me how you are counting your weeks ie what do you think is week 1 or week 2 and I can see whats happening so we can settle this mystery :- )
I have commented out that update statement just running it now - will get back asap
>>>> Yep, been through that one a thousand time and still believe it is not quite three weeks. Needs to include the following Monday otherwise it is short of three weeks. <<<<
can you show me how you are counting your weeks ie what do you think is week 1 or week 2 and I can see whats happening so we can settle this mystery :- )
ASKER
hi mark
there are students off more than 4 weeks showing - one has been off 4 weeks, another has been off 4 weeks and one student has been off 8 + weeks
do you need these examples
there are students off more than 4 weeks showing - one has been off 4 weeks, another has been off 4 weeks and one student has been off 8 + weeks
do you need these examples
ASKER
hi mark
I just noticed this comment
<<< The problem using date arithmetic is that the next week (ie day + 6) might not exist - not just the day, it might be a week off due to school term break, so we dont really know how many days the next week will be. >>>
and as always your right because my table only includes information of when the class is active so this wouldnt work
ill just be quiet :-)
I just noticed this comment
<<< The problem using date arithmetic is that the next week (ie day + 6) might not exist - not just the day, it might be a week off due to school term break, so we dont really know how many days the next week will be. >>>
and as always your right because my table only includes information of when the class is active so this wouldnt work
ill just be quiet :-)
ASKER
sorry i dont know why I keep asking do you want data i will upload this spreadsheet now
first two students are off 4 weeks + and the last student is off alot of weeks of two seperate occassions
3-more-students.xls
first two students are off 4 weeks + and the last student is off alot of weeks of two seperate occassions
3-more-students.xls
ASKER
hi again mark
I noticed you have used c.weekrn >= a.weekrn+3 does this not mean that any student off 3 or more weeks will be shown
Its just I cant see where in the code you have put >= 3 weeks but less than 4
I noticed you have used c.weekrn >= a.weekrn+3 does this not mean that any student off 3 or more weeks will be shown
Its just I cant see where in the code you have put >= 3 weeks but less than 4
Thanks for the spreadsheet... Will look and load.
The >= 3 weeks is because we dont actually know if there are that many weeks (or more, or could even be less) what we are trying to do with that period is get an "end date". Not the last one in terms of absences, but a date before which everything needs to be absent.
We try to clear out more than 3 weeks by checking concurrency of absenses. We do that with help of the above, and then the next non-absent day from the above date if there are absences, it means that there are more than 3 weeks.
Anyway, thats the theory, and you have a spreadsheet to show me otherwise. Best look at that before I comment more :)
The >= 3 weeks is because we dont actually know if there are that many weeks (or more, or could even be less) what we are trying to do with that period is get an "end date". Not the last one in terms of absences, but a date before which everything needs to be absent.
We try to clear out more than 3 weeks by checking concurrency of absenses. We do that with help of the above, and then the next non-absent day from the above date if there are absences, it means that there are more than 3 weeks.
Anyway, thats the theory, and you have a spreadsheet to show me otherwise. Best look at that before I comment more :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hi mark
thats great thanks v much just testing it now so will get back asap
thats great thanks v much just testing it now so will get back asap
ASKER
hi mark
everything is working great - just in time for my meeting
I really cant thank you enough for helping me
thanks again
everything is working great - just in time for my meeting
I really cant thank you enough for helping me
thanks again
ASKER
top class solution thanks