November 26, 2008 at 4:50 am
What i have is a table recording information regarding visits to an examination centre for students, i need to find a way of identifying visits where students who had a score of 5 or less and did not have a repeat visit within three days.
CREATE TABLE visit(
VisitID INT,
StudentID INT,
visitDate DATETIME,
score INT,
PRIMARY KEY(Visit))
INSERT INTO visit(VisitID, StudentID,visitDate,score)
SELECT 1 , 1, '09/21/2008' , 5 UNION ALL
SELECT 2 , 1, '09/22/2008' , 5 UNION ALL
SELECT 3 , 1, '09/27/2008' , 8 UNION ALL
SELECT 4 , 1, '09/27/2008' , 9 UNION ALL
SELECT 5 , 1, '09/28/2009' , 3 UNION ALL
SELECT 6 , 2, '09/22/2008' , 5 UNION ALL
SELECT 7 , 2, '09/23/2008' , 6 UNION ALL
SELECT 8 , 2, '09/24/2008' , 4 UNION ALL
SELECT 9 , 2, '09/25/2008' , 5
From this example visit 1 in the table would have been a record falling into that category as that student scored five and returned 1 day later. But visitID 2 - the same student scored 5 again but failed to return until 5 days later.
Hope this makes sense - any help appreciated.
November 26, 2008 at 5:08 am
will this work for you?
DECLARE @visit TABLE
(VisitID INT PRIMARY KEY,
StudentID INT,
visitDate DATETIME,
score INT)
INSERT INTO @visit(VisitID, StudentID,visitDate,score)
SELECT 1 , 1, '09/21/2008' , 5 UNION ALL
SELECT 2 , 1, '09/22/2008' , 5 UNION ALL
SELECT 3 , 1, '09/27/2008' , 8 UNION ALL
SELECT 4 , 1, '09/27/2008' , 9 UNION ALL
SELECT 5 , 1, '09/28/2009' , 3 UNION ALL
SELECT 6 , 2, '09/22/2008' , 5 UNION ALL
SELECT 7 , 2, '09/23/2008' , 6 UNION ALL
SELECT 8 , 2, '09/24/2008' , 4 UNION ALL
SELECT 9 , 2, '09/25/2008' , 5
SELECT *
FROM @visit
SELECT b.*,DATEDIFF(dd,b.visitDate,a.visitDate) as [DATEDIFF]
FROM @visit a
INNER JOIN @Visit b
ON a.VisitID = b.VisitID + 1
AND a.StudentID = b.StudentID
WHERE b.Score = 5
AND
DATEDIFF(dd,b.visitDate,a.visitDate) <=3
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 27, 2008 at 3:32 am
That's great - Many Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply