Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-10-15
2
Medium Priority
?
313 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 750 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 750 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

719 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