Finding date difference of > 28 days using CTE (lowfatspread pls read)

From my related question I would like to amend the code to check the a.abstart and abend to check if there is a day diff of greater than 28 or more days

I then want like the previous question to show all the classes that the student missed within these dates

Thanks in advance
LVL 3
lisa_mcAsked:
Who is Participating?
 
LowfatspreadCommented:

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

Open in new window

0
 
lisa_mcAuthor Commented:
Hi Lowfatspread

thats seems to be working fine Im just in the middle of testing it.

Will reply back when finished

thanks again
0
 
lisa_mcAuthor Commented:
that works perfect thank you so much I have finally got this thing sorted

0
 
lisa_mcAuthor Commented:
Thanks again
0
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.

All Courses

From novice to tech pro — start learning today.