Solved

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

Posted on 2008-10-15
2
304 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

734 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