x
Solved

# problem with datediff > 28 using cte

Posted on 2011-03-14
Medium Priority
605 Views
I was thinking about the date diff of 28 days that Lowfatspread helped me with on friday and of course I have come up with one minor problem which will give a small number of students but the output will be incorrect

I have attached a sample student data to show you what I mean

When testing for 28 days (4 weeks) I haven't taken into account that the college will break for christmas, easter and so on, and the way this college works is the week number will continue on each week of the holidays.  Looking at the example student on spreadsheet I will try to explain

When I run the search of >=28 days on the example student I get the rows highlighted in blue out.  Looking at the dates there is a date diff of >  28 days but the students has only been off 3 weeks.  You will notice that week_no 18+ 19 are not there this represents christmas (the reason why they are not included in my table is because I only take each week that the course is active for).  The query was to find students who have been off for 4 weeks or more, its a way of checking students who have breached over 4 weeks absence and I don't want this student being contacted because really they haven't missed four weeks of college so they could complain.

When checking absences > 28days I need to check that each week_no is sequential ie 17, 18, 19 to ensure that the students has missed > 28 days.  For each week number is missing I need to add 7 days to the datediff of 28 days
so in ref to the example student because week 18 + 19 are missed then I need to add 14 days to the 28 days and then check for 42 days diff.

I really hope this makes sense

Hope you can help

incorrect-student.xls
0
Question by:lisa_mc
• 21
• 14
• 3

LVL 2

Expert Comment

ID: 35128118
Hi

Rather than datediff it may be better to use the Week numbers to check for 4 consecutive weeks of absence. I've attached an example. I've called my table "Table_1" so you'll need to change that to the name of your table.  It's a bit rough around the edges but should get you going.
ee-datediff.sql
0

LVL 3

Author Comment

ID: 35139527
hi Mr B

Yes I do understand that you solution is actually better but its implementating it into the code that I already have thats causing the problems

0

LVL 2

Expert Comment

ID: 35147595
Just looking for the orignal script from your previous question but can't see it, could you re-post please?  I also only just noticed your question was directed to Lowfatspread so sorry for diving straight in. Maybe they will pick up next time their logged on but there may be others can help in the mean time.

0

LVL 3

Author Comment

ID: 35179455
Hi Mr B

Code is shown below

This shows all students who have been off more than 27 days
``````USE [nicisreports]
GO
/****** Object:  StoredProcedure [dbo].[nrc_INF0149_5consec_daysoff]    Script Date: 03/10/2011 09:49:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[nrc_INF0149_long_absence]

@Absentperiod int -- min days absent

AS

SET NOCOUNT ON

/* Create tables */
if exists (select * from nicisReports.dbo.sysobjects
where id = object_id(N'[nicisReports].[dbo].[nrc_INF0149_5consec_daysoff_tb]'))
begin
end
else
begin
CREATE TABLE [dbo].[nrc_INF0149_5consec_daysoff_tb] (
register_id VARCHAR(255),
register_group VARCHAR(255),
par_aos_sess VARCHAR(255),
par_full_desc VARCHAR(255),
course_coordinator VARCHAR(255),
child_aos_sess VARCHAR(255),
child_full_desc VARCHAR(255),
day_num VARCHAR(255),
day VARCHAR(255),
week_no int,
--week_range varchar(255),
date datetime,
start_time datetime,
end_time datetime,
dept_code varchar(255),
dept_desc VARCHAR(255),
campus_id VARCHAR(600),
campus_desc VARCHAR(600),
staff_code VARCHAR(255),
staff_title VARCHAR(255),
staff_name VARCHAR(255),
student_id VARCHAR(255),
title VARCHAR(255),
stud_fam_name VARCHAR(255),
stud_surname VARCHAR(255),
stage_code varchar(255),
absence_code VARCHAR(255),
attend_mode VARCHAR(255),
lect_staff_code VARCHAR(255),
reg_staff_code VARCHAR(255),
reg_title VARCHAR(255),
reg_name VARCHAR(255),
register_mark_date datetime,
day_diff varchar(255),
AbsentDays int,
Abstart datetime,
Abend datetime
)
end

Declare @rc int,@err int,@rows int
set @rc=-999

if exists (select * from nicisReports.dbo.sysobjects
where id = object_id(N'[nicisReports].[dbo].[#absencerep]'))

--drop table #absencerep
begin
from  nicisreports..nrc_eRegisters_allstudents_testing_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_testing_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_testing_tb as x   --yourtable
--student_REgister as x
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

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

--    select * from cte1
,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(*) >1
-->4    -- >4 absence of "5 or more days"
)

,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 datediff(d,abstart,Abend) >=@Absentperiod
)
--    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
from nicisreports..nrc_eRegisters_allstudents_testing_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
order by x.student_id,x.[date],x.[start_time]

-- allows the fields of the table to be referenced through the sp in crystal
select *
from nicisReports.dbo.nrc_INF0149_5consec_daysoff_tb

Select @err=@@ERROR,@rows=@@ROWCOUNT
if @err =0
begin
set @rc=0
end
return @rc
go

SET NOCOUNT OFF
``````
0

LVL 3

Author Comment

ID: 35187744
alias99 please read my comment in the automated request for attention id love to know your thoughts
0

LVL 3

Author Comment

ID: 35189685
hi Mr B

now that i have posted the code any suggestions on how to change it to accomodate my request?
0

LVL 53

Expert Comment

ID: 35195115
Hi, would love to help.

But have to go out for a couple of hours. Will be back later though, and if still here, will have a crack at it then. Just letting you know that the call for experts has gone out, and I for one like the challenge of this question :)
0

LVL 2

Expert Comment

ID: 35197122
Apologies for not responding have been away for a few days.  I'll hopefully get more time to look at your script soon if it's not answered in the mean time, although i'm off again next week.
0

LVL 53

Expert Comment

ID: 35197518
OK,

Couple of questions... And sorry about the 20 questions, just wanna be sure...

1) are the number of absent days actually absent (calendar) days, or, a count of individual classes missed ? e.g. if nail art is run twice a week then the number of absences for nail art could be 8 over a 4 week period, or, do we want to count the 4 nail art absences ? Or, are all classes weekly based (even though the student might have different subjects within the week) ?

2) do you have a calendar of school / campus / subjects (time table) and holidays anywhere ?

3) if not, would you consider / how difficult would it be for you to maintain one ?

4) if not, could we assume that all the unique/different weeks (or dates) regardless of student would adequately represent actual college attendance dates (the idea is there will always be at least 1 student in attendance - or - do you ever have a class where no one turns up) ?

5) the query used to generate the spreadsheet - can we use that as "source data" - it seems to be quite similar to the information used in the SP (albeit more like the results). e.g. could I import that and pretend that it is like nrc_eRegisters_allstudents_testing_tb  ? Or, do you have some test data to play with ?

6) You seem to be building up a permanent table : nrc_INF0149_5consec_daysoff_tb  and repopulating for the requested period. Is this table used for other things ? If not, then do we need to build and maintain it ? It does seem to be absolutely everyone for the academic year ("10/11" from your spreadsheet), is the idea here to make it quicker by building nrc_INF0149_5consec_daysoff_tb in say an overnight run, and then during the day simply query off that table (reason for asking is that every student / class combo could get big and therefore some performance implications - if performance is not a big concern by populating overnight then we have options).

7) Do you want us to try to fix the current code, or more simply, come up with a solution ?

I will try to trace back to the "related" questions but got in a loop, hence the questions above - and again I am sorry for going over old ground, but it is important to understand. I am not so sure we need such a complex cascading CTE. Maybe we do...

0

LVL 3

Author Comment

ID: 35197783
hi mark_wills

no thats fine I don't mind explaining

Firstly my stored procedure  was created to show all e-registers marks against each student so if a students course is active 2,3,4,5,6,10,11,13 and the register is only marked on week 11,13 then week 2,3,4,5,6,10 will still be shown but will remain null.

1) Absent days is when a student is absent for a full day of classes (depending on course student could have one or more different classes (subjects in a day).  If a computing student has 2 different classes on mon then he needs to miss both to be absent.  If a student has 2 days of classes per week then after 4 weeks absent they would have missed 8  days of classes.  Yes for full time coures a student will have the same classes each week, whereas a part time student could have the same class every 2 weeks and depending on a course this decides which day, time each class takes place

2 + 3) I have written a  stored proc which links all the tables (I can't change or modify these in any way) I need and populates a table all students.  This table stores all students on every module for each week that the course is active therefore table is over 2 million records.  In my table I only hold the weeks that the course is active eg if a course is active week 2,3,4,5 and then week 6 is a hol then my table will ignore this week and start again at week 7 so I never have to amend my data to cater for holidays.  the information for each school/campus/subjects is stored over 10 tables this is why I run my sp to I have this info all in one table

4) We are looking at this at student level not a class level this table also shows the register marks for each students / = present    o = absent. This is how we know if a student is absent all day.
Note to consider is a register is not marked then it will be null for that particular week no so I cannot assume that the student is off, the student is only absent by mark o.  When checking absences for a day you need to check that they have been absent for every class that day (ie full day absent)

5) the example above is information from my stored procedure with a few fields missing as they arent important like module desc, staff name and so on they can be added in at the end so yes this can be taken as test data

6) my stored procedure is run weekly (takes about 30 mins) , then I run the code above and output information to the table nrc_INF0149_5consec_daysoff_tb this table is then linked to a crystal report and info displayed to user.  Yes my sp is run for every student in every campus for acad_year 10/11

7) fix current code or come up with another solution  I really don't mind

Hope this clarifys it
0

LVL 53

Expert Comment

ID: 35199884
Right,

That is getting a lot clearer. Thanks. Just need to clear up "modules" definition given the above comments.

So, we know what module a student is meant to be attending and on what day, it is just the register being marked for that day that we need to check.

You already have the table "nrc_eRegisters_allstudents_testing_tb"  and it shows (amongst other things) student, date, absence  where date is the date that a course/class/module was scheduled, and the absence is either "/" or "o" depending on partial (or full) attendance or no register mark for any course on that day.

Now, the spreadsheet does show "Nail Art" as the module and a week date corresponding to day 2. Above you talked about course and class. Are there other modules for that student on the same day, and are there other days for that student within the same week (assuming w/c starts on Monday ie day 1) ?

If so, and you said in 4 above "We are looking at this at student level not a class level" do we need to consolidate those back to a week (as in if there was any attendance that week, then that week was deemed attended), or, is "module" (e.g. Nail Art) a class or a course ? Or we simply need to report by modules missed ?

What about those part-time students, do we still count 4 weeks or 4 instances of missed attendance ?

The challenge going back into "nrc_INF0149_5consec_daysoff_tb" is that the table seems to be a LOT more detailed than just the student level. So, joining them might give the wrong impression with student metrics being calculated at a higher level than is being reported.

I will start playing with the spreadsheet as input. Ideally get more of a snapshot for a single student.

0

LVL 3

Author Comment

ID: 35200266
I need to explain a few things

classes and modules are the same thing eg module = database and that class takes place on a tues

Course will have modules and the table will show which date/time these modules (classes) take place.  An absent mark is recorded against each modules (class) so when checking a days absence you need to check that the student was absent for all modules that day.

>> and the absence is either "/" or "o" depending on partial (or full) attendance or no register mark for any course on that day.

like i explained above each module (is a single record in the database) gets a register mark if absent the student = o, if present = / and if not amrked then NULL

>> Now, the spreadsheet does show "Nail Art" as the module and a week date corresponding to day 2. Above you talked about course and class. Are there other modules for that student on the same day, and are there other days for that student within the same week (assuming w/c starts on Monday ie day 1) ?

This is what im explaining this depends on the course an IT student could have classes mon, thurs, fri
a beauty student could have classes mon, tues, friday
an electrical student could be in 5 days a week
on each of these days a student could have 2 different modules being taught (classes) again this depends on the course - think of it like a university

you could be an IT student
on monday 12-3 you have databases
tues 11-1 you have wed besign
tues 3 - 5 you have maths
wed 3- 5 you have programming
off thursday
friday 9-12 you have systems design

you will have these same classes at the same time each week - If you wanted to see if I was absent on a fulll day eg tuesday you would have to check both maths and programming.  If I am only absent for maths then I am not off the full day

you could be an electrical student
on monday 9-10.30 you have circuit design
mon 11-1 you have logic programming
mon 3 - 5 you have maths
off tues
wed 3- 5 you have pheumatics
off thursday
friday 9-12 you have electrical principles

you will have these same classes at the same time each week, shown above you would have to check that I have missed all three classes on a monday to be absent all day

IN my above examples I have shown that a course can only be 3 days a week in this case my database will only store the day and weeks that the course is active so that only required information is stored

>> If so, and you said in 4 above "We are looking at this at student level not a class level" do we need to consolidate those back to a week (as in if there was any attendance that week, then that week was deemed attended), or, is "module" (e.g. Nail Art) a class or a course ? Or we simply need to report by modules missed ?

Looking at student level you go through every module they have for a particular course and check if they have been off for more than 4 weeks
(weeks is determined by change of week_no) so yes we need to check  if all modules for that week have been missed starting from the frist absence
A student could have classes min, tues, thurs, fri say he was present on mon and absent from tuesday on then you would need to check modules for rest of week, next week, week after that, week after that, then  all of mondays classes in the fourth week to show that he has been absent 4 or more weeks (by ensuring that week_no has changed 4 times)

again part time students if only one class per week then I need to count just that one module over 4 weeks again shown by change in week_no

>> The challenge going back into "nrc_INF0149_5consec_daysoff_tb" is that the table seems to be a LOT more detailed than just the student level. So, joining them might give the wrong impression with student metrics being calculated at a higher level than is being reported.

this table reports a line for each week num on each module that each student is enrolled.  What are you trying to join this table to
0

LVL 53

Expert Comment

ID: 35200471
Noticed in the SP that it was inserting back into that table from a detailed table joined to the CTE. Sorry about the confusion, but the net result is potentially the same. e.g.

select x.*, AbsentDays, Abstart, Abend
from nicisreports..nrc_eRegisters_allstudents_testing_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
order by x.student_id,x.[date],x.[start_time]

Where absentDays is already a count(*) so if being applied back to individual modules then it should represent a progressive count otherwise should not accumulate again.

But I think I now have enough information. Thanks for your patience. Time to start coding :)
0

LVL 53

Expert Comment

ID: 35214131

OK, can I please get some good test data ? The trickier the better :)

Now the full code currently just shows the first instance of the 28 days (or more accurately the first instance where there is a 4th anniversary of an absentee day without any interjecting attended days)

Are there any indexes on the table ? Can we create indexes if needed ?
0

LVL 3

Author Comment

ID: 35215274
hi again

I have attached some good tricky test data

what table are you talking about? If it is all the table I have to link to get the info then no I can't do anything to them.  If you mean my tables no there are no indexes on them
test-data.xls
0

LVL 3

Author Comment

ID: 35215310
the examples I have gave you the most a students has been off is 7 days I think I cant get any students who have been off for 4 weeks as I changed my table setup and havent modified my sp yet so cant get any data.

I had a suggestion rather than to check for 28 days check that a student has been off by checking the week_no variable the example I have gave you does have a student off over a 2 week period so this data would b a good starting point

when i get my code updated I will post up more results
0

LVL 53

Expert Comment

ID: 35215714
>> I can't do anything to them.

>> the most a students has been off is 7 days

Thats OK I can dummy up a few

But noticed the absence-code also has "X","C",'P" and null where it hasnt been entered. What do the "X","C","P" mean - I was thinking it was only "/" and "O"

0

LVL 53

Expert Comment

ID: 35215973
Oh, and yes, using week-no is a big part - basically we will get a sequence of weeks...
0

LVL 3

Author Comment

ID: 35215990
x i think is late
c means class cancelled
and p means programme
and null means not marked

the student can only be classes as absent using the absence_code O
0

LVL 53

Expert Comment

ID: 35226628
Hi, any new test data ?

rather than waiting, here is the code I have been playing with. Obviously destined for use as a SP...

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

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

select a.*
from #absences a
outer apply (select top 1 student_id, weekrn, dayrn, week_no, day_num, 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.weekrn is not null
and not exists (select NULL from #absences c where c.student_id = a.student_id and c.weekrn between a.weekrn and b.weekrn and c.dayrn between a.dayrn and b.dayrn and c.absence_ind <> 'O')
``````

not doing any inserts at this point - seems the column names arent quite the same - so will  need to name the column names rather than the simple "select *" shown above.

0

LVL 3

Author Comment

ID: 35229952
hi Mark

Im just updating my tables now so bear with me and I'll get you some more test data
0

LVL 3

Author Comment

ID: 35230414
Hi again mark

I have ran my prevous code for an absent period of 28 or more days (4 week period) and I have attached an example of the students being returned

The purpose of this report is to show students who have been off for 4 or more weeks

In the spreadsheet I have included a student MCG10054334 that has more than one period of a date diff of 28 days absent.

These dates are
15/09/2010 - 19/11/2010
07/01/2011 - 11/03/2011

Another student CLA10056458 is a good example of how the date difference of >= 28 days will not work.

This report highlights potential withdrawn students (as they have been absent for so long) so I really need the information to be accurate and this shows the problem I am having

I am getting the absence of CLA10056458 to be from the 21/12/2010 - 18/01/2011  although this is a datediff of >= 28 days the student has actually only missed 3 weeks as shown by week_no 17, 20, 21 - this is why I need to count the week_no instead of a date_diff

The code you gave above can you explain whats its suppossed to do

Thanks

MORE-TEST-DATA.xls
0

LVL 3

Author Comment

ID: 35230437
sorry I meant to say in this particular report I only want to see a summary of the student information like student_id, last class attended and date of this last class

Thanks again
0

LVL 3

Author Comment

ID: 35230506
hi again

I have been looking at your code and checking a few student results against the current list of student I have.  The few that I have tested seem to be the same but your results doesnt show all the classes missed example for one student has 71 records (showing all classes missed) and in your results that same student only has 15 records at the moment I cannot see why all the records arent being displayed - I'll continue to have a look and if I see anything ill post it up
0

LVL 3

Author Comment

ID: 35230774
In reference to my comment above I have copied the particular student that I was referring to into a spreadsheet so that you can see for yourself

In the first sheet this shows all the information for a particular student this is from my allstudents table.
(I have highlighted that particualr instance where this student has missed more than 27 days)

The next sheet shows the output from the current code I have (71 recrods which have been tested against the allstudents table and are accurate)

the last sheet shows the output from your code (as you can see only 15 records are shown)

more-data-2.xls
0

LVL 53

Expert Comment

ID: 35230801
Will have a look into it a bit more....

The dense_rank basically does a straight count, so weeks 17, 20, 21  would be counted as (e.g.) weeks 1,2,3 and that is how we overcome any missing dates because we revert the weeks into actual integer consecutive sequences.

We also do the same for days - given the pattern repeats, it is easier to use a counter by day as if it were the record pointer (because a whole day is either attended or not)

Have not worried about time at this point, just the day granularity.

I will test against the spreadsheet above. and provide a full description of the procedure once we know it is working properly.
0

LVL 3

Author Comment

ID: 35230859
ok thats great

I am also looking into it and if I see anything I will let you know

thanks
0

LVL 53

Expert Comment

ID: 35230892
I think I know what it is....

a problem with interpretation of the problem...

Week 24 and week 26 you can count 28 days missing (even taking into account the missing week 25) but (say) week 27 - you cannot say that it was 28 days because w/c 28th Feb + 28 days takes you to the 27th March - non of which has been marked / registered yet.

So the only ones we can categorically say are not attended for 28 days are those that are 28 days or older.

What you spreadsheet is showing are continuous absences from the weeks 24 and 26 up until the 28days from (should be) week 26.

So, one shows 28days and older, the other is showing everything within that 28 days (even if some of them are only a few days ago).

Does that make sense ?

0

LVL 3

Author Comment

ID: 35231075
sorry im really confused im really not understanding what you are trying to say

in the example student supplied I need to see this absnet period as 8/2/2011 - 22/3/2011

0

LVL 3

Author Comment

ID: 35231080
sorry really need clarification on this comment

>> Week 24 and week 26 you can count 28 days missing (even taking into account the missing week 25) but (say) week 27 - you cannot say that it was 28 days because w/c 28th Feb + 28 days takes you to the 27th March - non of which has been marked / registered yet.
0

LVL 53

Accepted Solution

Mark Wills earned 2000 total points
ID: 35233472
Sure....  will try :)

My interpretation of the question was influenced by the word "datediff"

I understood that to mean (ignoring gaps in weeks for now) an absence of 28 days (or more)

So, my code checks to see absences of that age.

If we consider (from the test data) weeks 24, 26, 27, 28 and the last registered attendance (or non-absence) was 23rd March then :
week 24 + 28 days <= 23rd March = OK to show
week 26 + 28 days <= 23rd March = OK to show
week 27 + 28 days > 23rd March = Not yet 28 days old
week 28 + 28 days > 23rd March = Not yet 28 days old

But what I think you are asking for is continuous absence of 28 days or greater... not necessarily 28 days old.

So, the block of continuous absence from week 24 through to week is > 28 days, so show them all.

Probably helps if I show you explicitly :

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

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

select a.student_id, a.weekrn as start_weekrn, a.dayrn as start_dayrn, a.week_no as start_week_no, a.day_num as start_day_num, a.date as start_date
, b.weekrn as end_weekrn, b.dayrn as end_dayrn, b.week_no as end_week_no, b.day_num as end_day_num, b.date as end_date
from #absences a
outer apply (select top 1 student_id, weekrn, dayrn, week_no, day_num, 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.weekrn is not null
and not exists (select NULL from #absences c where c.student_id = a.student_id and c.weekrn between a.weekrn and b.weekrn and c.dayrn between a.dayrn and b.dayrn and c.absence_ind <> 'O')
``````

Notice the start and end dates ? I was only showing the start - not everything in between.

So, to show everything in between, we need to list all the absences, which means a join back to absences. But there will be overlapping periods, so we want distinct rows. Easiest is to first resolve the query for all those start and end periods. And in reality, we really dont need all the columns, just those required to extract the final row selections. So, we will go to a CTE.

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

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

;with absences_cte as
(
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
inner join #absences ab on ab.student_id = c.student_id and ab.date between c.start_date and c.end_date
``````

0

LVL 3

Author Comment

ID: 35233601
hiya

yeah I thought you maight be thinking that

if a student is off 40 days I want to see this students because they are off more than 28 days

im just trying this code now so wil reply asap
0

LVL 3

Author Comment

ID: 35233763
HI Mark

at the moment that looks really good im just going to spend abit of time testing (the fun part :-)

could I ask one more thing you know the way this shows every class that the student attended

would there be any way to show the last class each student attended
Im asking as I would like to show not only a detailed report but also  a summary report

so I would like to show that last class that each student had

does that make sense
0

LVL 53

Expert Comment

ID: 35235192
Well the last class attended is pretty much the same query. Just the very last bit changes so we find the top 1 row for each student prior to that 'start_date' from the subquery (or CTE query).

Oh, and I also noticed that we dont really use the DAYRN dense_rank so that could be removed from the above as well (initial query into #absence and the index)

``````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
``````
0

LVL 53

Expert Comment

ID: 35235231
BUT.... the above is really a different question...

You have also asked for a similar "summary" thing over at : http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_26916404.html

So, will also post the code above into that thread, but, might need to work on those 5 and 9 day params. The code above is pretty much week aligned (and 4 weeks at that) and pretty much dependant on resolving this one first.
0

LVL 3

Author Comment

ID: 35239331
hi mark

my fault I really should have asked this as another question can I accept both questions and award points to you and then ask a related question where the 5 and 9 parameters hav to be worked on

would that be ok?  Im asking as it think you are very deserving of the points as you have helped me so much
0

LVL 3

Author Closing Comment

ID: 35239436
hey mark thanks very much for your help

I am going to ask a related question to the code you posted above would really appreciate your help again

Thanks again
0

LVL 53

Expert Comment

ID: 35239711
No worries, love your questions - they are so involved they are great to work on, and you always get back with good examples and stay engaged. Great to work with you, and always happy to help :)
0

## Featured Post

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.