SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Query

Author

Topic

moonlight
Starting Member

3 Posts

Posted - 2009-08-10 : 05:18:47

Hi All:
I am administering an SQL 2005. Teachers do a roll call once a day and enter all absent students.

The coordinators want to be an alerted when a particular student is a way for more than two days.

I thought to create a query and get SQL server to email the query result using database mail.

I know the logic of the query but not sure how to create the conditional select statement for date

The statement I need is like this:

Select * from student, Absentees
Where student.Id = Absentees.StudentId

The part I am having trouble with

Count the number of absent days for a particular student
If number are more than 2 days then
Check if dates are sequential in backward sequence. (i.e. 10/8/09, 9/8/09, 8/8/09…..)
If a condition is met then include all absent days row in the report.
Email report using database mail

Your help is much appreciated

Sachin.Nand

2937 Posts

Posted - 2009-08-10 : 07:48:35

quote:
Originally posted by moonlight

The part I am having trouble with

Count the number of absent days for a particular student
If number are more than 2 days then
Check if dates are sequential in backward sequence. (i.e. 10/8/09, 9/8/09, 8/8/09…..)
If a condition is met then include all absent days row in the report.
Email report using database mail

Your help is much appreciated

What abt weekends when u want to check sequential dates?Are weekend entries included in your table?

PBUH

moonlight
Starting Member

3 Posts

Posted - 2009-08-10 : 08:21:22

Hi Idera:

Thanks for the response, No I dont actually need to include weekends. only school days when a particular student was absent from school.

Regards

moonlight
Starting Member

3 Posts

Posted - 2009-08-10 : 08:27:04

Sorry I think I missed one of your questions. Well basically every day after the data is entered and sql code must produce the new report. it should look at entered records and compare them. if ant student code has been repeated more than twice and the dates are the past three days in sequence then he or she should be included in the report.

I hope that makes sense.

Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources