I have a SQL Table that houses the date that a student was assigned and completes one of three exams. I am in need of a way to query the table and return the column name of the exam the student needs to take and the date it was assigned.
For example, these are my desired query results:
Joe - exam1date - 01-30-2018
James - exam2date - 03-02-2018
Javier - exam3date - 04-01-2018
And this is DDL for my table:
Create Table Information
(
fname varchar(100)
,exam1date date
,exam1complete date
,exam2date date
,exam2complete date
,exam3date date
,exam3complete date
)
Insert Into Information (fname, exam1date)
Values ('joe', '2018-01-30')
INSERT INTO Information (fname, exam1date, exam1complete, exam2date)
Values ('james', '2018-02-14', '2018-02-21', '2018-03-02')
INSERT INTO Information (fname, exam1date, exam1complete, exam2date, exam2complete, exam3date)
VALUES ('javier', '2018-01-01', '2018-01-14', '2018-03-01', '2018-03-12', '2018-04-01')
What would a query be that can returned my desired result set from the above table schema?
Best Answer
I guessing that you need the examdate of the last exam that does not have a complete date?
Query
Result