consecutive Scores - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

consecutive Scores

Author

Topic

ucal
Yak Posting Veteran

72 Posts

Posted - 2010-03-09 : 06:41:11

I have data with the following structure :

StudID,ExamID ,Score
1,1,20
1,2,40
1,3,50
1,4,10
1,5,30
1,6,25

2,1,50
2,2,40
2,3,45
2,4,67
2,5,70
2,6,72
2,7,65

3,1,60
3,2,70
3,3,60

4,1,45
4,2,30
4,3,65

I want to select only records with consecutive increase in Score for each StudID. Increase should be consecutive for 3 or more Score for each StudID

Desired Results:

StudID,ExamID ,Score
1,1,20
1,2,40
1,3,50

2,3,45
2,4,67
2,5,70
2,6,72

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-03-09 : 19:00:27

If 1, 1, 20 is included in the output, why is 2, 2, 40 not included?

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

ucal
Yak Posting Veteran

72 Posts

Posted - 2010-03-09 : 19:24:52

Good call Bustaz !

2, 2, 40 should be part of the desired result.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-03-09 : 19:40:04

[CODE];with Sequence
as
select a.StudID, a.ExamID, a.Score StartScore, b.Score MiddleScore, c.Score EndScore
from
MyTable a
inner join
MyTable b
on
a.StudID = b.StudID
and a.ExamID + 1 = b.ExamID
and a.Score < b.Score
inner join
MyTable c
on
a.StudID = c.StudID
and a.ExamID + 2 = c.ExamID
and b.Score < c.Score
)
select StudID, ExamID, StartScore
from Sequence

union all

select StudID, ExamID + 1, MiddleScore
from Sequence

union all

select StudID, ExamID + 2, EndScore
from Sequence[/CODE]

You'll need to eliminate duplicates.

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

ucal
Yak Posting Veteran

72 Posts

Posted - 2010-03-10 : 21:55:53

Thanks Bustaz, your solution worked for me.

Great job !!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources