Solved

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

Posted on 2008-10-15
2
259 Views
Last Modified: 2012-05-05
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
Comment
Question by:DarthCook
2 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 250 total points
ID: 22720771
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
ID: 22723352
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now