problem with datediff > 28 using cte

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

hi Mr B

Thanks for the reply and sorry for my late reply

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

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.

 

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]

@pAcadPeriod char(5),
@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
  delete from nrc_INF0149_5consec_daysoff_tb where acad_period=@pAcadPeriod
end   
else
begin
    CREATE TABLE [dbo].[nrc_INF0149_5consec_daysoff_tb] (
    acad_period VARCHAR(255),
	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]'))
  delete from [dbo].[#absencerep]  where acad_period=@pAcadPeriod 

    
--drop table #absencerep
if @pAcadPeriod is null 
begin
   select @pAcadPeriod=MAX(acad_period) 
      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
           where acad_period=@pAcadPeriod
           group by student_id,[date]
           order by 1,2,present
  select @err=@@ERROR,@rows=@@ROWCOUNT
  if @err<>0 
  begin
     --
     set @rc=1
     return @rc
  end
  
  ;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 
where acad_period=@pAcadPeriod

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

SET NOCOUNT OFF

Select allOpen in new window

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

hi Mr B

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

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 :)

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.

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

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

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.


 

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

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 :)

Sorry about the pause. Had work issue to attend to...

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 ?

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

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

>> I can't do anything to them.

That answers my question. Pity.

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

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

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

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

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')

Select allOpen in new window

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.

hi Mark

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

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

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

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

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

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.

ok thats great

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

thanks

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 ?

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

Can you explain more about your comment above

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.

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

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

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

Select allOpen in new window

BUT.... the above is really a different question...

You have also asked for a similar "summary" thing over at : https://www.experts-exchange.com/questions/26916404/Only-output-last-record-from-a-cte.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.

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

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

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 :)