May 9, 2011 at 6:57 pm
We are running SQL server 2005 and have a students database. The information report we need should come from the following two tables:
Student
_______________________________
cmpy_codechar(2)Unchecked
stud_codechar(8)Unchecked
surnamechar(30)Unchecked
given_namechar(30)Checked
preferred_namechar(20)Checked
par_codechar(8)Checked
year_grpsmallintChecked
form_clschar(1)Checked
Student_Absent
_______________________________
record_idintUnchecked
cmpy_codechar(2)Unchecked
stud_codechar(8)Unchecked
absent_datedatetimeUnchecked
absent_typechar(3)Unchecked
absent_timedatetimeChecked
If a student is absent from school for 3 days or more, an alert message should be sent to the concerned people for follow up. Your help and guidance is most appreciated.
May 9, 2011 at 9:06 pm
I am assuming that you are recording the data for each absent day. If this is the case you just need to select the data for the student absent table where there are 3 days absent previous to the current day getdate() function - 3 and they have a row count = to 3
here is an example
CREATE TABLE [dbo].[Date](
[id] [int] NULL,
[absent] [datetime] NULL
) ON [PRIMARY]
select id as studentid from Date
where absent > getdate()- 4
group by id
having COUNT(id) > 2
May 10, 2011 at 2:31 pm
henners72 thanks for the help. I just need to clarify something please, why do I need to create another table. Your assumption was right; the data is entered on daily basis. So I am thinking I need a select statement to select data from student and absent tables then check the result to see if any student was away for 3 days or more. Maybe I need to create a view then check data in that view on daily basis to see if condition is met
Regards
May 10, 2011 at 3:09 pm
This will get the bad children for you:
select s.stud_code, s.given_name, x.Num_Absences
from Student s
inner join
(
select stud_code, count(*) Num_Absences
from Student_Absent
group by stud_code
having count(*) > 2
) x on s.stud_code = x.stud_code;
As far as alerting you, you could have a job that runs this query in a procedure and send an email to certain staff.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
May 10, 2011 at 8:21 pm
Have to notice that these children will not get a chance to improve 😉
The query above will include all kids that were absent 3 or more days, regardless the fact that it might have happened some time ago and not recently and already reported to whoever was concerned.
Additionally, is it important if they are absent 3 or more days in a row, or 3 days in total?
It seems to me that, if there is no track of what was already reported, you will end up with a report that is getting longer every day (and over the time no-one will want to read it).
May 10, 2011 at 8:38 pm
ModestyBlaise84 (5/10/2011)
Have to notice that these children will not get a chance to improve 😉The query above will include all kids that were absent 3 or more days, regardless the fact that it might have happened some time ago and not recently and already reported to whoever was concerned.
Additionally, is it important if they are absent 3 or more days in a row, or 3 days in total?
It seems to me that, if there is no track of what was already reported, you will end up with a report that is getting longer every day (and over the time no-one will want to read it).
🙂 You are right. Wha is needed is 3 days or more in a row on any given date ( ie if the report is generated today for example and a student has been away for the past 3 days then he/ she should be included. so my plan is to query the database on daily basis after absentees have been entered and and the needed query should show students who have been away for 3 days or more in a row for teacher follow up. Hope it is clear now.
Regards
May 10, 2011 at 10:09 pm
It is clear :-), but it's not that easy.
Here is the example of the code:
declare @d datetime --input parameter for the procedure
--set @d='2011-05-09'
declare @StartDate datetime --first day of a period, has to be calculated and exclude Sundays/Saturdays
declare @EndDate datetime --last day of a period that is neing checked
set @EndDate =@d --or could be calculated.More likely taht this would be set @EndDate=dateadd(d,-1,@d)
set @StartDate= case DATEPART(Dw,@EndDate) when 2 then DATEADD(d,-4,@EndDate)--@EndDate is Monday
when 3 then DATEADD(d,-4,@EndDate) --@EndDate is Tuesday
else DATEADD(d,-2,@enddate)
end
select stud_code,COUNT(*) as AbsNum from Student_Absent
where Absent_date between @StartDate and @EndDate
group by stud_code
having COUNT(*)>2
This is based on the following assumptions:
1. School is not working on Sundays and Saturdays
2. Server is set up so Sunday is the first day (default)
3. You don't need to show how many days in a row is student absent, just to show that they were absent 3 or more days
4. Doesn't take into consideration public holidays
In case you need to take into consideration Public Holidays/school non-working days it gets really tricky, probably should have an additional table for that.
May 11, 2011 at 2:58 am
Hey, I was just providing an example of the code you could use to select this information, as in teach a man to fish......refining the logic should be up to you
May 11, 2011 at 4:44 am
Hey, sorry if it sounded offensive - actually wasn't commenting your post.
When I was reading the original post I realized that it's not really simple as it seemed and that the things could get much more complicated with the weekends, holidays etc.
🙂
May 11, 2011 at 4:51 pm
ModestyBlaise84 No problems thanks for the help, Now I know it is not that simple :-), but sometimes we humans make the wrong assumptions.
Thanks you everyone for the feedback and help, most appreciated.
May 11, 2011 at 7:01 pm
henners72 (5/11/2011)
Hey, I was just providing an example of the code you could use to select this information, as in teach a man to fish......refining the logic should be up to you
Thanks Rookie, I know what you mean but it is challenging, hopefully i can catch some fish now :-). I believe I understand the logic of building the query, but working with dates is challenging for a beginner like me. I am trying to put the pieces together. please be patient with a beginner fisherman who does not know the difference between Snapper or Bream. :hehe:
The logic of the query ( hope this is logical) as I understand it :
(This is the easy part)
Select basic details from Student and absent tables
where follow-up flag is null ( This is to avoid recurrent reporting of absent students)
(this is the hard part)
based on current date check if student was absent for 3 days or more in the current year ( when a students is reported absent for 3 days he/she is reported to the teacher for follow up. once followed up the student follow up flag is set to yes)
if yes
Check if these days are in a row from current date
group by student id
So the query will very much depend on the date it is executed and the entered data in the absent table, holidays and weekends won't matter at all.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply