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 post 1 (of 2 total)
You must be logged in to reply to this topic. Login to reply