Solved

Query a database

Posted on 2011-03-04
10
176 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
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)?
0
 
LVL 7

Expert Comment

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

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
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
0
 
LVL 7

Expert Comment

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

Accepted Solution

by:
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
 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 3

Author Comment

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

Author Comment

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

Author Comment

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


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
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
0
 
LVL 3

Author Closing Comment

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

Thanks again
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…

867 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

18 Experts available now in Live!

Get 1:1 Help Now