Improve company productivity with a Business Account.Sign Up


Query a database

Posted on 2011-03-04
Medium Priority
Last Modified: 2012-05-11
Hi experts

I was wondering if anyone could help me with a very big problem

I have attached a spreadsheet which shows how a student record is stored in my database.

Currently I run a sp which populates a table to show all the courses that a student is on for each week of the academic year.  This table is called all students.  I want to query the table all students and report back all the students who have missed five consec days of classes (5 consec days is when the class took place eg could be thurs, fri, mon, tues, thursday).  A student can have more than one class per day.  

A student is absent by the code 'O'.  A student can only be included in the 5 consec days off is the absent_code = '0'.  If a register is not marked then the field will be null this cannot contribute to the five consec days absent.  If a student is off 3 consec days then one the fourth day the register hasnt been marked then the program needs to forget this info, look for the next absent code = '0' and start again.

NOTE:  students can have more than one class per day so when checking the first day of absence we need to  check that its the first class of the day that they are absent and when checking the 5th day of absence we need to check that it is the last class of the day that they are also absent

I have attached a spreadsheet to explain further
In spreadsheet in row 7 this student is absent but because the register has not been marked for her other two classes that day we cannot assume that the student was absent this day, we then go onto line 8.  Student is absent and its her first class that day so we can store this info we then check all the rest of her absences which just happens to be 5 full consec days so we then store all this information which is then outputted to a crystal report.  I would like to show in this crystal report all the modules (classes) that this student has missed and how many consec days they were absent and when was the date of the first full day of absence

I would to do this in a sp as there are nearly 2 million records.  I know what I am asking is difficult and if I could assign more points I would.   I am sure there must be an expert out there that can do this please as I am at my wits end

Thanks in advance

I am using sql server 2005
Question by:lisa_mc
  • 5
  • 2
  • 2
  • +1
LVL 15

Expert Comment

ID: 35035990
Could you verify these facts:

- you want to find students who have missed 5 consecutive days of classes
- absentees are indicated by a status "O"
- students can have more than 1 class/day
- students are only considered absent for the day if they are absent from every class that day
- any status other than "O", including NULL, results in "not absent" for the day (and therefore resets any sort of cumulative counting obtained up to that point)

is 5 consecutive days considered 5 calendar days (i.e. thu/fri/mon/tue/wed)? or 5 days of classes (e.g., student only has classes on tuesdays, so student needs to be absent 5 straight tuesdays)?

Expert Comment

ID: 35036096
OK, so this could take a while :-)

The first step is to extract just the list of Students and Days where they were fully absent for the day.

I've done this using the attached.

The next step is to analyse this data for consecutive days.

Can you explain a little more about what consecutive days are? Would these be days that immediately follow each other, e.g. mon tues weds thur fri, and thurs fri mon tues wed, or are we looking at a different rule to this? (The records look like they are stored weekly?)

WITH IntermediaryTable (RowNumber, Student_id, date, StartTime, Absent_Code)
	SELECT ROW_NUMBER() OVER (PARTITION BY student_id, date ORDER BY date) AS RowNumber,
	student_id, date, [start time], absent_code
	FROM dbo.AllStudents s
	WHERE NOT EXISTS (SELECT * FROM dbo.AllStudents WHERE student_id=s.student_id AND AND ISNULL(absent_code, 'P')<>'O')
	--AND student_id='HUT08026089'
SELECT student_id, date
INTO #StudentAbsentDays
FROM IntermediaryTable
GROUP BY student_id, date
ORDER BY student_id, date

Open in new window


Author Comment

ID: 35036413
Hi guys thanx for the replies

derekkromm - you have got it spot on and 5 consec days represents the 5 consec days that the student is in class ie if student only has class on tuesday then we need to check 5 tuesdays

lozzamoore - im getting one error
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.AllStudents'.

As explained above the 5 days isnt 5 consec calendar days its 5 consec days that the student is in class

one strudent could have class mon, tues, wed, thurs, mon

another could have tues, thurs, mon, tues, thurs

That example is just for one week of a particular student.  I work in a college so think of the classes like the students would do at uni
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.


Expert Comment

ID: 35036428
Just replace "dbo.AllStudents" with the table name you are using...
LVL 50

Accepted Solution

Lowfatspread earned 2000 total points
ID: 35036439
this list the student data for all period of absence >= 5 days....

if you just want the absence period rather than all the lesson details

     select * from cte3
      order by student_id,abstart

i took your criteria to be ... only count absence days as being a full days register of absent_code(s)

;with cte as -- identify the columns of interest/the academic year
select [Year] as YR
      ,convert(datetime,[Date]) as RegDate
      ,convert(datetime,[date]+' '+[Start time]) as lessStart
  from student_register as x   --yourtable
  Where [Year]='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 absent_code when 'o' then 1 else 0 end) as absentless
            from cte
           group by student_id,regdate
          Having SUM(case absent_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 absent_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.*
     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


Author Comment

ID: 35036472
hi lowfatspread - bear with me im testing it now

Author Comment

ID: 35036576
hi lozzamoore - I ran that code and Im gettin no results back at all

Author Comment

ID: 35036989
hi lowfatspread that code is working fine

I have two questions if you dont mind

1) Is here any way I can get the number of days absent that the student missed

2) Say I wanted to know between 5 and 9 days would i change the code below
where b.RegDate >= DATEADD(DAY,5,a.regdate)      
     group by a.student_id
     having COUNT(distinct c.regdate) >= 5   


where (b.RegDate >= DATEADD(DAY,5,a.regdate)  and b.RegDate <= DATEADD(DAY,9,a.regdate))    
     group by a.student_id
     having (COUNT(distinct c.regdate) >= 5 )  and (COUNT(distinct c.regdate) <= 9 )

Open in new window

LVL 50

Expert Comment

ID: 35037341
1) the absentdays column is the number of absent days in the period  (in cte2 or cte3)
       Abstart , Abend are the begining and end dates of the absence period...
       lessoncount is the number of Lession periods missed in the period

2) yes but please use BETWEEN

b.regdate Between dateadd(d,5,a.regdate) and dateadd(d,9,a.regdate)

having count(distinct c.regdate) between 5 and 9

Author Closing Comment

ID: 35057186
thank you very much I have had this problem for a very long time and its finally sorted

Thanks again

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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.

Join & Write a Comment

This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
A method of moving multiple mailboxes (in bulk) to another database in an Exchange 2010/2013/2016 environment...
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Watch the video of Kernel Migrator for SharePoint, which demonstrate the process easily of migration from SharePoint to SharePoint, OneDrive for Business & Google Drive servers, Public Folder to SharePoint, File Server to SharePoint. The tool has va…

606 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