Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

How do i identify records with a specific value on cosecutive visits?

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 attatched

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.
VisitID	StudentID	visitDate	score
1	1	21/09/2008	5
2	1	22/09/2008	5
3	1	24/09/2008	8
4	1	27/09/2008	9
5	1	28/09/2009	3
6	2	22/09/2008	5
7	2	23/09/2008	6
8	2	24/09/2008	4
9	2	25/09/2008	5

Open in new window

0
DarthCook
Asked:
DarthCook
2 Solutions
 
momi_sabagCommented:
this will only work with sql server 2005 or 2008
with t as (
select visitId, studentId, sum(score) over(partition by studentId order by visitDate) as ssum
from your_table
)
select studentId
from t t1 inner join t t2 on t1.studentId = t2.studentId
 and t1.visitId + 2 = t2.visitId
where t2.ssum >= t1.ssum+15
0
 
Scott PletcherSenior DBACommented:
Or, for SQL 2000:


IF OBJECT_ID('tempdb.dbo.#studentScores') IS NOT NULL
    DROP TABLE #studentScores
CREATE TABLE #studentScores (
    id INT IDENTITY(1, 1) NOT NULL,
    studentId INT NOT NULL,
    score INT NULL,
    UNIQUE CLUSTERED (studentId, id)
    )

INSERT INTO #studentScores (studentId, score)
SELECT StudentId, score
FROM visitTable  --<<-- replace with your table containing visits info
ORDER BY StudentId, visitId --or visitDate


SELECT st.StudentId, st.Name, --, st....,
    CASE WHEN EXISTS(
        SELECT 1
        FROM #studentScores sc1
        INNER JOIN #studentScores sc2 ON
            sc2.studentId = sc1.studentId AND
            sc2.id = sc1.id + 1 AND
            sc2.score >= 5
        INNER JOIN #studentScores sc3 ON
            sc3.studentId = sc1.studentId AND
            sc3.id = sc1.id + 2 AND
            sc3.score >= 5
        WHERE sc1.studentId = st.studentId
        AND sc1.score >= 5
    ) THEN 'Met' ELSE 'Did not meet' END AS [Target?]
FROM studentTable st  --<<-- replace with your table containing main student info
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now