Solved

Query a database

Posted on 2011-03-04
10
173 Views
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
1-student.xls
0
Comment
Question by:lisa_mc
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 15

Expert Comment

by:derekkromm
Comment Utility
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)?
0
 
LVL 7

Expert Comment

by:lozzamoore
Comment Utility
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?)

Thanks,
WITH IntermediaryTable (RowNumber, Student_id, date, StartTime, Absent_Code)
AS
(
	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

0
 
LVL 3

Author Comment

by:lisa_mc
Comment Utility
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
0
 
LVL 7

Expert Comment

by:lozzamoore
Comment Utility
Just replace "dbo.AllStudents" with the table name you are using...
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
Comment Utility
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
 then

     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
      ,x.*
  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

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 3

Author Comment

by:lisa_mc
Comment Utility
hi lowfatspread - bear with me im testing it now
0
 
LVL 3

Author Comment

by:lisa_mc
Comment Utility
hi lozzamoore - I ran that code and Im gettin no results back at all
0
 
LVL 3

Author Comment

by:lisa_mc
Comment Utility
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   


CHANGE to 

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

0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
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
0
 
LVL 3

Author Closing Comment

by:lisa_mc
Comment Utility
thank you very much I have had this problem for a very long time and its finally sorted

Thanks again
0

Featured Post

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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql Audit table 3 46
Data quality checks 2 30
Oracle SQL Select within a Where Clause 9 37
CREATE DATABASE ENCRYPTION KEY 1 41
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Creating and Managing Databases with phpMyAdmin in cPanel.
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…

763 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

8 Experts available now in Live!

Get 1:1 Help Now