Solved

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

Posted on 2008-10-15
2
281 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:Scott Pletcher
Scott Pletcher 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

770 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