asked on

Comparing rows in a database to see if values are consecutive.

Hi,

I have a list of college courses. I would like to know if

a)      Lecturer teaches two courses that consecutive (in the below example – that would be Course ID - 1) then
b)      Lecturer teaches two or More course that ARE not necessarily consecutive (in the below example – that would be Course ID – 2 & 3)

Course Table. I'm trying to work out grade...

Course ID       Lecturer Name        Course Year Code        Course Year name        Grade
1        Mr Lecturer A        10        Sub Year One        Consecutive Grade Year
1        Mr Lecturer A        11 Sub Year Two        Consecutive Grade Year
2        Mr Lecturer B        20        Year Two        Multi-Grade Year
2        Mr Lecturer B        21        Year Three        Multi-Grade Year
2        Mr Lecturer B        22        Year Four        Multi-Grade Year
2       Mr Lecturer B        23        Year Five        Multi-Grade Year
3        Mr Lecturer C        20        Year Two        Multi-Grade Year
3        Mr Lecturer C        23        Year Five        Multi-Grade Year

Years and Codes

Year Code      Year Name
10        Sub Year One
11        Sub Year Two
12        Sub Year Three
20        Year Two
21        Year Three
22        Year Four
23        Year Five
24        Year Six

Any ideas please

awking00

Are you saying you want to identify any lecturer who has taught specifically two courses in consecutive years (identified by grade) versus lecturers who have either taught more than two courses in consecutive years or have taught two courses in non-consecutive years?
What would you want for grade in the following case?
4 Mr Lecturer D 21 Year Three
4 Mr Lecturer D 22 Year Four
4 Mr Lecturer D 24 Year Six
Where two courses were taught consecutively and two were not.

Coco Beans

ASKER

Lecturer D would be "Multi-Grade Year"

Thanks

awking00

I suspected so, but you still didn't answer the first question. Why is Mr. Lecturer B multi-grade year (simply because there are more than two years, consecutive or otherwise)? A new question is, "What should be the grade value for a lecturer who only teaches one year?"

Coco Beans

ASKER

Yes, I'm sorry I wasn't clear

"Are you saying you want to identify any lecturer who has taught specifically two courses in consecutive years (identified by grade) versus lecturers who have either taught more than two courses in consecutive years or have taught two courses in non-consecutive years? "

Yes exactly this

awking00

Are you looking for an update statement or a select statement to modify of retrieve the grade value?

Coco Beans

ASKER

I'm looking to create the column Grade that you see in the top example

ASKER CERTIFIED SOLUTION