0

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?

2

I guessing that you need the examdate of the last exam that does not have a complete date?

Query

 SELECT fname,  
        CASE 
        WHEN exam1complete IS NULL THEN 'exam1date'
        WHEN exam2complete IS NULL THEN 'exam2date '
        ELSE 'exam3date' END AS columnname,
        CASE
        WHEN exam1complete IS NULL then exam1date 
        WHEN exam2complete IS NULL then exam2date 
        ELSE exam3date END AS examdate
FROM Information;

Result

fname   columnname  examdate
joe exam1date   2018-01-30
james   exam2date   2018-03-02
javier  exam3date   2018-04-01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.