RAR RAR - 27 days ago 10
SQL Question

Comparing data in same table - SQL

I'm working with a course catalog table in which I have catalog codes and course codes for when the courses were offered. What I need to do is to determine when a course isn't being offered any longer and mark it as an archived course.

CREATE TABLE [dbo].[COURSECATALOG](
[catalog_code] [char](6) NOT NULL,
[course_code] [char](7) NOT NULL,
[title] [char](40) NOT NULL,
[credits] [decimal](7, 4) NULL,

)

insert into coursecatalog
values
('200810', 'BIOL101', 'Biology', '3'),
('200810', 'CHEM201', 'Advanced Chemistry', '3'),
('200810', 'ACCT101', 'Beginning Accounting', '3'),
('201012', 'ACCT101', 'Beginning Accounting', '3'),
('201214', 'ACCT101', 'Beginning Accounting', '3'),
('201214', 'ENGL101', 'English Composition', '3'),
('201416', 'PSYC101', 'Psychology', '3'),
('201618', 'PSYC101', 'Psychology', '3'),
('201618', 'BIOL101', 'Biology', '3'),
('201618', 'CHEM201', 'Advanced Chemistry', '3'),
('201618', 'ENGL101', 'English Composition', '3'),
('201618', 'PSYC101', 'Psychology', '3')


In this case, I need to return ACCT101 - Beginning Accounting since this isn't being offered anymore and should be considered an archived course.

My code so far:

SELECT
catalog_code, course_code
FROM COURSECATALOG t1
WHERE NOT EXISTS (SELECT 1
FROM COURSECATALOG t2
WHERE t2.catalog_code <> t1.catalog_code
AND t2.course_code = t1.course_code)
order by
course_code, catalog_code


But this only returns courses that were only ever offered one time (in one catalog). I need to figure out how I can get courses that might have been offered in multiple catalogs but isn't offered any longer.

Any assistance that can be provide is appreciated!

Thank you!

Answer

I think the catalog_code is a date with YYYYMM format

SELECT course_code FROM (
SELECT CONVERT(char, catalog_code,112) AS catalog_code, course_code FROM COURSECATALOG
) AS Q
GROUP BY course_code
HAVING MAX(catalog_code) < '20160101'

Example: http://sqlfiddle.com/#!6/32adfb/14/1

Comments