October 15, 2008 at 7:32 am
I need to create a query which will identify how many students had a score of 5 or more, on 3 or more consecutive visit to a test centre, and how may did not. I have a student table with student demogrpahics and a visit table which records visits students make to a test centre example of table structure and data below.
VisitIDStudentIDvisitDatescore
1121/09/20085
2122/09/20085
3124/09/20088
4127/09/20089
5128/09/20093
6222/09/20085
7223/09/20086
8224/09/20084
9225/09/20085
From this data student 1 made the target as he had 3 consecutice visits with a score of 5 or more. While Student 2 did not.
Ideally i would be able to produce a list with each students personal infoirmation along with a generated field e.g Met Target / Did not meet target based on the above conditons.
Thanks for any help.
October 15, 2008 at 11:19 am
This solution is a variation of Jeff Moden's Running Total Technique, which can be found here:
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
This is the setup for this solution, please post sample data in this fashion in the future:
[font="Courier New"]
USE SSC
CREATE TABLE Test(
VisitID INT,
StudentID INT,
visitDate DATETIME,
score INT,
PRIMARY KEY CLUSTERED(VisitID, StudentID, visitDate),
Pass INT)
INSERT INTO Test(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/24/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
[/font]
Note that the Primary Key and Pass Column have been added to the test table. These *are* part of the solution. You may need to select your data into a temporary table first if you cannot modify your base table as such.
The rest of the solution:
[font="Courier New"]
DECLARE @PrevStudent INT,
@PassCount INT
SET @PrevStudent = 0
UPDATE Test
SET @PassCount = CASE
WHEN @PrevStudent = StudentID AND score >= 5 THEN @PassCount + 1
WHEN @PrevStudent = StudentID AND Score < 5 THEN 0
WHEN @PrevStudent <> StudentID THEN 0
END,
Pass = CASE WHEN @PassCount >=3 THEN 1 ELSE 0 END,
@PrevStudent = StudentID
FROM Test WITH (INDEX(0))
SELECT StudentID, MAX(Pass)
FROM Test
GROUP BY StudentID[/font]
If someone could confirm that the composite clustered index does what I'm thinking it does here (ie. orders by id, then student, then date), I'd appreciate it.
October 15, 2008 at 11:46 am
Wow, that's sweet! I hadn't seen that before.
_________________________________
seth delconte
http://sqlkeys.com
October 15, 2008 at 11:47 am
Garadin (10/15/2008)
If someone could confirm that the composite clustered index does what I'm thinking it does here (ie. orders by id, then student, then date), I'd appreciate it.
It does in fact do what you'd expect. The problem is - because of that, I don't think it is the "correct" clustered index (OP asked to find 3 consecutive passes PER STUDENT, so the order should probably be:
STUDENTID, VISITDATE, VISITID
since it has to dictate the order for the "running" part to work right.)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 15, 2008 at 12:21 pm
Thanks Matt, and good catch. I guess that's the downside of having perfectly ordered test data =).
October 15, 2008 at 7:15 pm
Garadin (10/15/2008)
This solution is a variation of Jeff Moden's Running Total Technique, which can be found here:http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
Thanks for the "pass back", Seth. After Matt's index suggestion, looks like you've got the "quirky" update down pat for the most part. Nicely done.
Matt... great catch.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 15, 2008 at 7:42 pm
Jeff Moden (10/15/2008)
Garadin (10/15/2008)
This solution is a variation of Jeff Moden's Running Total Technique, which can be found here:http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
Thanks for the "pass back", Seth. After Matt's index suggestion, looks like you've got the "quirky" update down pat for the most part. Nicely done.
Matt... great catch.
Happy to be of service! It's good to stay in the swing of things even though my time on SSC squashed quite a bit in the last few months....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 16, 2008 at 6:57 am
Great everyone thanks!!
December 4, 2008 at 8:18 am
Once again everyone thanks - but i have just been asked to do more reports relating to this DB, based on the query and tables in this post. I need to be able to identify the 3rd consecutive visit - i.e the visit that determined if the student met the target or not . and then retrieve the score and date of that visit.
The solution in the post worked great but I cannot think if this can be altered to retrieve the data I need or even if i need to go down a new route completely.
Thank you for any help
December 4, 2008 at 8:32 am
Can you provide new sample data / results to illustrate what you're looking for now? (Please post it in the fashion I did in my original post, with INSERT statements. An article on how to easily do this is in my signature)
December 4, 2008 at 8:58 am
Seth my apologies for my rubbish posting
Here is a small set of the data based on the original post
CREATE TABLE visit(
VisitID INT,
StudentID INT,
visitDate DATETIME,
score INT,
PRIMARY KEY (VisitID)
)
SET DATEFORMAT mdy
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/24/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' , 5 UNION ALL
SELECT 9 , 2, '09/25/2008' , 5
what i need is to be able to identify visits records of which students had 3 consecutive visits with a score of 5 or more. So from this dataset i would need to get back visitID3 as this is the 3rd visit where student1 had a score of 5 or more. I would also need visitID8 as this is where studentID2 made there 3rd visit with a score of 5 or more. Hope this makes some more sense - thanks for any help.
December 4, 2008 at 9:14 am
No problem Stu, thanks for the inserts. This also allows me to fix the numerous issues my last method had.
[font="Courier New"]CREATE TABLE visit(
VisitID INT,
StudentID INT,
visitDate DATETIME,
score INT,
PRIMARY KEY CLUSTERED (StudentID, VisitDate, VisitID),
Pass INT
)
SET DATEFORMAT mdy
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/24/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' , 5 UNION ALL
SELECT 9 , 2, '09/25/2008' , 5
DECLARE @PrevStudent INT,
@PassCount INT,
@VisitID INT
SET @PrevStudent = 0
UPDATE Visit
SET @PassCount = CASE
WHEN @PrevStudent = StudentID AND score >= 5 THEN @PassCount + 1
WHEN @PrevStudent = StudentID AND Score < 5 THEN 0
WHEN @PrevStudent <> StudentID AND Score >= 5 THEN 1
WHEN @PrevStudent <> StudentID AND Score < 5 THEN 0
END,
Pass = CASE WHEN @PassCount >=3 THEN 1 ELSE 0 END,
@PrevStudent = StudentID,
@VisitID = VisitID
FROM Visit WITH (INDEX(0))
SELECT
V.StudentID,
CASE WHEN MAX(Pass) = 1 THEN 'Passed' ELSE 'Failed' END [Pass/Fail],
MV.PassDate
FROM visit V
LEFT JOIN (SELECT StudentID, MIN(VisitID) PassDate FROM visit WHERE Pass = 1 GROUP BY visit.StudentID) MV ON V.StudentID = MV.StudentID
GROUP BY V.StudentID, MV.PassDate[/font]
December 5, 2008 at 4:37 am
Seth - A thousand thanks!!!
December 5, 2008 at 9:39 am
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply