[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 date=s.date 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.


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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

In this article, we’ll look at how to deploy ProxySQL.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

649 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