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
Coco BeansDesignerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

awking00Information Technology SpecialistCommented:
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 BeansDesignerAuthor Commented:
Lecturer D would be "Multi-Grade Year"

Thanks
awking00Information Technology SpecialistCommented:
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?"
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Coco BeansDesignerAuthor Commented:
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
awking00Information Technology SpecialistCommented:
Are you looking for an update statement or a select statement to modify of retrieve the grade value?
Coco BeansDesignerAuthor Commented:
I'm looking to create the column Grade that you see in the top example
slightwv (䄆 Netminder) Commented:
I believe this gives you the results you want but I'm not sure it will work in ALL situations.

select course_id, lecturer_name, course_year_code,
	first_value(case when total_count=2 and next_year-course_year_code=1 then 'Consecutive Grade Year'
		when total_count=2 and next_year-course_year_code != 1 then 'Multi-Grade Year'
		when total_count>2 then 'Multi-Grade Year'
	end) over(partition by course_id,lecturer_name order by course_id,lecturer_name) grade
from (
	select course_id, lecturer_name, course_year_code,
		count(*) over(partition by course_id,lecturer_name order by course_id,lecturer_name) total_count
		,lead(course_year_code) over(partition by course_id,lecturer_name order by course_id,lecturer_name) next_year
	from tab1
)
/

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
If grade doesn't exist as a column in your existing table (I'm calling this table courses), then ALTER table courses ADD (grade VARCHAR2(30);
You can then use the merge function to update the column -
MERGE INTO courses c
USING
(select courseid, lecturer, yearcode, yearname,
 case when maxyears = 2 and (diff = 1 or nextdiff = 1) then 'Consecutive Grade Year'
      else 'Multi-grade Year' end as grade from
  (select courseid, lecturer, yearcode, yearname, diff,
   lead(diff) over (partition by courseid, lecturer order by courseid) nextdiff,
   last_value(yearcodenum) over (partition by courseid, lecturer order by courseid) maxyears  from
    (select courseid, lecturer, yearcode, yearname,
     lag(yearcode) over (partition by courseid, lecturer order by yearcode) prevyearcode,
     yearcode - lag(yearcode) over (partition by courseid, lecturer order by yearcode) diff,
     count(yearcode) over (partition by courseid, lecturer order by yearcode) yearcodenum
     from courses)
    )
  )
) x
ON (c.courseid = x.courseid and c.lecturer = x.lecturer and c.yearcode = x.yearcode)
WHEN MATCHED THEN
UPDATE SET c.grade = x.grade;
Coco BeansDesignerAuthor Commented:
Brilliant - thank you both.  Both solutions work.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.