August 10, 2009 at 3:30 am
Hi All:
I am administering SQL2005 with small databases for a school . Our Teachers do a roll call once a day and enter all absent students details.
The coordinators want to be 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 dates:
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 of days more than 2 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
August 10, 2009 at 3:16 pm
I'm going to assume that you mean any time a student is absent consecutive days. Here's a solution, but I think it needs some more work to perform with a large set of data. My posting will get it back to the first page of active threads and may get you some more and/or better solutions.
DECLARE @absentees TABLE (StudentId Int, AbsenceDate SMALLDATETIME)
INSERT INTO @absentees (
StudentId,
AbsenceDate
)
SELECT
1,
'10/1/2008'
UNION ALL
SELECT
2,
'10/2/2008'
UNION ALL
SELECT
2,
'10/3/2008'
UNION ALL
SELECT
2,
'10/7/2008'
UNION ALL
SELECT
1,
'10/7/2008'
UNION ALL
SELECT
3,
'10/3/2008'
UNION ALL
SELECT
3,
'10/4/2008'
UNION ALL
SELECT
3,
'10/5/2008'
;WITH cteAbsentees AS
(
SELECT
A.StudentId,
A.AbsenceDate
FROM
@absentees A JOIN
@absentees B ON
A.StudentId = B.StudentId AND
A.AbsenceDate = DATEADD(DAY, - 1, B.AbsenceDate)
UNION -- this gets the last row which would not have a following row.
SELECT
A.StudentId,
A.AbsenceDate
FROM
@absentees A JOIN
@absentees B ON
A.StudentId = B.StudentId AND
A.AbsenceDate = DATEADD(DAY, 1, B.AbsenceDate)
)
SELECT
A.studentid,
-- this section concatenates the dates in ascending order
STUFF((
SELECT
',' + CONVERT(CHAR(10), AbsenceDate, 101)
FROM
cteAbsentees A1
WHERE
A.StudentId = A1.StudentId
ORDER BY
A1.StudentId,
A1.AbsenceDate -- you can add a DESC here if you want descnding dates.
FOR XML PATH('')),
1,1,''
)
FROM
cteAbsentees A
GROUP BY
A.studentid
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply