Solved

Problem with CTE (Lowfatspread please read if you can)

Posted on 2011-03-07
26
268 Views
Last Modified: 2012-08-14
Hi

I was being helped by expert lowfatspread on getting a list of students off 5 consec days absent

The code works grand but when I tested it more I found there was a student who wasnt appearing in the list and I cant understand why

hopefully you can help (code is attached)
;with cte as -- identify the columns of interest/the academic year
(
select [acad_period] as YR
      ,convert(datetime,[date]) as RegDate
      ,convert(datetime,[date]+' '+[start_time]) as lessStart
      ,x.*
  from nicisreports..nrc_eRegisters_allstudents_tb as x   --yourtable
  Where [acad_period]='10/11'
  )    
 -- select * from cte
, CTE1 as -- identify students who have been off a full day
         (select student_id,regdate,MIN(lessstart) firstless,MAX(lessStart) lastless
              ,count(*) as numless
              ,SUM(case absence_code when 'o' then 1 else 0 end) as absentless
            from cte
           group by student_id,regdate
          Having SUM(case absence_code when 'o' then 1 else 0 end) = COUNT(*)
          )
    --    select * from cte1
,cte2 as --
       (select a.student_id,MIN(a.regdate) as Abstart
                  ,MAX(b.regdate) as Abend
                  ,COUNT(*) as Lessoncount,COUNT(distinct c.regdate) as AbsentDays
                  ,SUM(case absence_code when 'o' then 1 else 0 end) as Absents
          from CTE1 as a   
          Inner Join cte1 as B
            on a.student_id=b.student_id
           and b.RegDate>a.RegDate
          Inner Join cte as c
           on a.student_id = c.student_id
           and c.RegDate between a.RegDate and b.regdate 
     where b.RegDate >= DATEADD(DAY,5,a.regdate)      
     group by a.student_id
     having COUNT(distinct c.regdate) >= 5                         
      ) 
,cte3 as --  students with absence periods of 5 or more days
   (      
    select * 
      from cte2
      Where Absents=lessoncount
   )  
   select x.*, AbsentDays, Abstart, Abend
     from cte as x
    Inner Join cte3 as a
      on x.student_id=a.student_id
     and x.RegDate between a.Abstart and a.Abend
    order by x.student_id,x.lessstart

Open in new window

0
Comment
Question by:lisa_mc
  • 14
  • 10
  • 2
26 Comments
 
LVL 3

Author Comment

by:lisa_mc
ID: 35058463
I think I have figured out what is wrong with it at the min

in cte1 this selects all the specific dates that a student was off for the whole day

The spreadsheet below shows the output from cte 1 for a particular student - this student has had 17 different days off - the output i am getting is wrong for this students

Sheet 2 is all the information stored for that student highlighting full days they have been off

even though this student has missed alot of days the most consec days  that this student has missed is 4 days shown by row 125-137

whereas your code would show it as 17 days absent from 22/09/2010 to 25/11/2010

is there any way to fix this to only show days which follow each other

Hope I have explained this ok
example-student.xls
0
 
LVL 34

Expert Comment

by:James0628
ID: 35067223
If I'm reading it correctly, I think the basic problem is line 34:

     having COUNT(distinct c.regdate) >= 5                        

 That's including any student that had 5 or more absent days in between their first absence and their last one.  I don't see any easy way to add a check for consecutive days to that.  I think you might be able to do this by having the query build a list of each starting and ending absent date pair that represented at least 5 days of consecutive absences, and then narrowing that list down to one row per student, but I'm hoping that someone else will have a simpler solution.

 James
0
 
LVL 3

Author Comment

by:lisa_mc
ID: 35067990
Hi James0628

Thats exactly what I need.

At the moment I have each individual day for each student (this is the output from cte1) and I have the number of lessons that day and the number of lessons missed, I need a way of checking each of these lines for the first date where lessons = lessonsmissed start a counter here = 1, then check the next row if same again then counter = 2 and so on.  when the lessons <> lessonsmissed and if the counter is at 5 at this stage then I want to store this record, restart the counter and go to the next line where lessons = lessons missed and start the whole process again.

I just don't know how to implement this in sql have you any ideas
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35068909
please try this
;with cte as -- identify the columns of interest/the academic year
(
select [acad_period] as YR
      ,convert(datetime,[date]) as RegDate
      ,convert(datetime,[date]+' '+[start_time]) as lessStart
      ,x.*
  from --nicisreports..nrc_eRegisters_allstudents_tb as x   --yourtable
       student_REgister as x
  Where [acad_period]='10/11'
  )    
 -- select * from cte
, CTE1 as -- identify students attendance per day
         (select student_id,regdate,MIN(lessstart) firstless,MAX(lessStart) lastless
              ,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 cte
           group by student_id,regdate
          )
, cte1a as (select * from CTE1 where present=0)  -- absent student days
--, cte1p as (select * from cte1 where present>0) -- student days where at least partial attendence
    --    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 cte1 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(*)>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
       )
     select * from cte2 ORDER BY 1,2
 --  students with absence periods of 5 or more days
 
   select x.*, AbsentDays, Abstart, Abend
     from cte as x
    Inner Join cte2 as a
      on x.student_id=a.student_id
     and x.RegDate between a.Abstart and a.Abend
    order by x.student_id,x.lessstart

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35068989
sorry must have got confused the first time i thought i'd dealt wuith the consecutive day problem in my cte2 previously...

ok i've put back the exists test i had been playing with in the cte2 area before...


so

1 .  (cte)  get a proper starttime for lessons and limit to an academic year.

2.  (cte1) organise a view of student attendance per day... lessons absent or "not absent"

3.  (cte1a) students absent for the whole day

4. (cte2max) workout a max end "continuous" absence date for each "start" absent date...
      for absences of at least 5 "academic days"

5. (cte2)  slim down to just absence periods get min start date for each END Date

6.  report the missed lessons in the students absence periods...
0
 
LVL 3

Author Comment

by:lisa_mc
ID: 35069366
hiya

thanks for replying

testing it now will get back asap thanks again
0
 
LVL 3

Author Comment

by:lisa_mc
ID: 35069832
hi again

Im running that now and 50 mins have passed and its still running

There are about two million records should it take that long
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35070199
possibly...

what indexes do you have?

can you post the table ddl?

i would suggest an index on acad_period,student_id,date  as a minimum

0
 
LVL 3

Author Comment

by:lisa_mc
ID: 35070236
Shown below for my table setup

I dont have any indexes

The tables that Im extracting from cannot be changed or edited in any way
USE [nicisreports]
GO
/****** Object:  Table [dbo].[nrc_eRegisters_allstudents_tb]    Script Date: 03/08/2011 15:36:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[nrc_eRegisters_allstudents_tb](
	[acad_period] [varchar](255) NULL,
	[register_id] [varchar](255) NULL,
	[register_group] [varchar](255) NULL,
	[par_aos_sess] [varchar](255) NULL,
	[par_full_desc] [varchar](255) NULL,
	[course_coordinator] [varchar](255) NULL,
	[child_aos_sess] [varchar](255) NULL,
	[child_full_desc] [varchar](255) NULL,
	[day_num] [varchar](255) NULL,
	[day] [varchar](255) NULL,
	[week_no] [int] NULL,
	[week_range] [varchar](255) NULL,
	[date] [datetime] NULL,
	[start_time] [datetime] NULL,
	[end_time] [datetime] NULL,
	[dept_code] [varchar](255) NULL,
	[dept_desc] [varchar](255) NULL,
	[campus_id] [varchar](600) NULL,
	[campus_desc] [varchar](600) NULL,
	[staff_code] [varchar](255) NULL,
	[staff_title] [varchar](255) NULL,
	[staff_name] [varchar](255) NULL,
	[student_id] [varchar](255) NULL,
	[title] [varchar](255) NULL,
	[stud_fam_name] [varchar](255) NULL,
	[stud_surname] [varchar](255) NULL,
	[stage_code] [varchar](255) NULL,
	[absence_code] [varchar](255) NULL,
	[attend_mode] [varchar](255) NULL,
	[lect_staff_code] [varchar](255) NULL,
	[reg_staff_code] [varchar](255) NULL,
	[reg_title] [varchar](255) NULL,
	[reg_name] [varchar](255) NULL,
	[register_mark_date] [datetime] NULL,
	[day_diff] [varchar](255) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35071423
performance isn't going to improve without some indexes on your table....


if you can create temp tables and indexes on them then this may help



drop table #absencerep
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_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_tb as x   --yourtable
                 student_REgister as x
           where acad_period='10/11' 
           group by student_id,[date]
           order by 1,2,present
  
  ;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(*)>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
       )
 --    select * from cte2 ORDER BY 1,2
 --  students with absence periods of 5 or more days
 
   select x.*, AbsentDays, Abstart, Abend
     from --nicisreports..nrc_eRegisters_allstudents_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]

Open in new window

0
 
LVL 3

Author Comment

by:lisa_mc
ID: 35081260
hi lowfatspread

that ran in 20 sec I am checking it now

I was wondering can I put this into a stored proc and pass the value 5 and 9

this is to check all students who have been off >= 5 days but <= 9 days

and once I get this then I can put this into a table and link crystal to it
0
 
LVL 34

Expert Comment

by:James0628
ID: 35081883
Ah, good.  Lowfatspread to the rescue.  :-)  FWIW, I wrote a reply last night, but then the site was down when I tried to post it and I lost the reply, and forgot to come back later and try again.  But hopefully Lowfatspread has got you covered.

 FWIW, instead of using a count, I had this idea of changing cte2 (going back to your original query) to include one row for every a.regdate and b.regdate that were at least 5 days apart (instead of just MIN (a.regdate) and MAX (b.regdate)), and then changing the HAVING to only include the rows where the number of days between a.regdate and b.regdate matched COUNT (distinct c.regdate), meaning that the days in between were all absent days.  You could get multiple rows for some students, but another GROUP BY, maybe in the SELECT that built cte3, could have compressed those down to one row per student.

 I wasn't sure if it would work, much less how it would perform.  Just an idea I was toying with.


 Also, going back to the original query again, it seems like the WHERE on line 32 is off.

     where b.RegDate >= DATEADD(DAY,5,a.regdate)


 It seems like that should be adding 4, not 5.  Then again, that may be a moot point, since the latest query doesn't seem to have that test anymore.  But I thought I'd mention it, just in case.

 James
0
 
LVL 3

Author Comment

by:lisa_mc
ID: 35083238
Hi again

Lowfatspread i have got that into a sp and it creates a table to link to my crystal report

the only thing I am having difficulty with is selecting all students that are off between 5 and 9 days

I tried

HAVING COUNT(*)>4 and  COUNT(*) < 10  -- >4 absence of "5 or more days"  

Having count(*) between 4 and 8

and neither worked can you help
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35085137
can you post your stored procedure?

just to be clear

you only want to list students who have had a period of continuous absence lasting between 5 and 9 days

ie .. if they have been absent for 10 or more days they are not to be in the list...

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35085371
sorry if they have absence periods of

6 days
2 days
11days


should they appear or not?

if they appear does only the 6 day absence want to be shown?
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35085694
this appears to work for me....

declare @rc int
exec @rc=usp_studentAbsenceRep 5,9
print 'Return code=('+convert(varchar(11),@rc)+')'


what table do you want to insert to?

how are you inserting ?
  within the procedure
 or using the procedure...
  declare @rc int
   insert into mytable (columnlist)
     exec @rc=usp_studentAbsenceRep 5,9
  print 'Return code=('+convert(varchar(11),@rc)+')'


alter procedure usp_studentAbsenceRep
    @MinAbsence int 
   ,@maxabsence int
   ,@AcadYr varchar(5) = '10/11'
as
set nocount on

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

if @maxabsence<@MinAbsence
begin
    set @rc=-1
    return @rc
end
    
--drop table #absencerep
if @acadyr is null 
begin
   select @acadyr=MAX(acad_period) 
      from  --nicisreports..nrc_eRegisters_allstudents_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_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_tb as x   --yourtable
                 student_REgister as x
           where acad_period=@acadyr
           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(*) between @minabsence and @maxabsence 
     -->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
       )
 --    select * from cte2 ORDER BY 1,2
 --  students with absence periods of 5 or more days
 
   select x.*, AbsentDays, Abstart, Abend
     from --nicisreports..nrc_eRegisters_allstudents_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]
    
    Select @err=@@ERROR,@rows=@@ROWCOUNT
    if @err =0 
    begin
       set @rc=0
    end
    return @rc
    go

Open in new window

0
 
LVL 3

Author Comment

by:lisa_mc
ID: 35093403
Hi

I have attached a spreadsheet to show exactly what I mean

The student I have attached is missing (shown by highlighed colour) first block of 10 days then second block of colour shows 6 days

If I wanted to select all absences between 5 and 9 then I would only want to show the second block of 6 days

So to answer your above question about a student absent

6 days
2days
11 days

Then I just want to show 6 days

I have tried the new code and I am still getting the two black of absences out for this student except it is saying the student is off 9 days then six days

When it says 9 days the first absence is 2/12/2010 as oppossed to 01/12/2010 so it seems to be cutting a day off

The second sheet in the spreadsheet shows the exact output I am getting from the new code
incorrect-output.xls
0
 
LVL 3

Author Comment

by:lisa_mc
ID: 35093421
Hi again

I have attached my sp

The table I create for my crystal reports is nrc_INF0145_5consec_daysoff_tb
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_5consec_daysoff]

@pAcadPeriod char(5),
@MinAbsence int ,
@maxabsence int

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]'))
  delete from nrc_INF0149_5consec_daysoff_tb where acad_period=@pAcadPeriod 
else
    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
    )



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

if @maxabsence<@MinAbsence
begin
    set @rc=-1
    return @rc
end

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(*) between @minabsence and @maxabsence 
     -->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
       )
 --    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

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35094279
what is the difference between DATE and REGISTER_MARK_DATE?   column L and AG in the first sheet?


0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 35094642
ok the between test needs to be done in 2 parts

the min test is in the having of cte2max

the max test is in cte2


hope thats it.
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_5consec_daysoff]

@pAcadPeriod char(5),
@MinAbsence int ,
@maxabsence int

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 @maxabsence<@MinAbsence
begin
    set @rc=-1
    return @rc
end

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(*) >= @minabsence  
     -->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 Absents <= @maxabsence
       )
 --    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

Open in new window

0
 
LVL 3

Author Comment

by:lisa_mc
ID: 35094962
Hi again

L is the date the class took place and AG is the date that the register was marked

Im just going to try the code now will reply asap
0
 
LVL 3

Author Comment

by:lisa_mc
ID: 35095091
hi again

I ran that code and I am getting 45 records as my output which isnt correct

Im just thinking if its too complicated or too much more work then maybe just 5 days will do because I also have to run a report to show 10 consec days so the students will always show in  one of the reports I think we could just keep it the way it is

Can I ask would it be possible to check absentences that have a day diff of > 28 days

ie abstart and max(abend) check if the day diff between these two dates > 28 days

Is that possible to amend the above code to accomodate this

0
 
LVL 3

Author Comment

by:lisa_mc
ID: 35095295
Hi again

I changed the line 151 and Absents <= @maxabsence

to AbsentDays <= @maxabsence

And I am gettin more records back with absentences between these values  Im just going to test it some more

So what do you think about the datediff would that be more complicated to do
0
 
LVL 3

Author Closing Comment

by:lisa_mc
ID: 35096601
thanks again
0
 
LVL 3

Author Comment

by:lisa_mc
ID: 35096610
I have asked about the date in a new question please reply to it if you can
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35096780
yes sorry should have been absentdays not absents (lessons) being tested

28 days  should just be  exec usp_studentAbsenceRep '10/11',28,28
or exec usp_studentAbsenceRep '10/11',28,999

or do you actually mean a calendar datediff of 28 days rather than 28 lesson days...?

     that would be a line 151 of datediff(d,abstart,abend)=28   ?  
    you may want to do that in a different procedure since the minabsences test probably needs to
    be changed as well,,, (at line 140 HAVING COUNT(*) >= @minabsence , you may just want to have this as
        HAVING COUNT(*) > 1

?
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now