Query a database

Posted on 2011-03-04
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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.


Expert Comment

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

Accepted Solution

Lowfatspread earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Exchange 2013 tmp files 3 43
Coldusion - DATA insert syntax problem 12 51
SQL query and VBA 5 46
SQL - Simple Pivot query 8 15
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
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…

820 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