Find MIN value from multiple columns in a SQL Server table - Return sys.columns ColumnName

Question

Specs: SSMS 2012

Table: 7 columns, one ID column (NOT NULL) & 6 date columns (NULL)

There are thousands of rows in the table:

ID|Date1|Date2|Date3|Date4|Date5|Date6

.

I am trying to find the MIN(DATE) across all 6 Date columns; however, I can't lose track of the Date Column Name (the sys.columns value). This is needed for reporting which column has the lowest Date.

All of the Date columns can have a NULL value.

I'm open to either creating another column to report the MIN(sys.columns) name or adding a T/F column for each Date Column where 'T' corresponds to the lowest date column. Obviously NULLs should be avoided when calculating the MIN(DATE).

I was thinking an UNPIVOT would probably be needed. Any help would be appreciated. Thank you for reviewing in advance.

Answer 1

You might also consider using the VALUES close instead of UNPIVOT. Seems cleaner and "more standard" for me. Check it out.

Table variable (Thanks, Andrew):

DECLARE @dates TABLE (
id INT,
date1 DATE,
date2 DATE,
date3 DATE,
date4 DATE,
date5 DATE,
date6 DATE)

INSERT @dates VALUES
(1,'2014-12-31',NULL,'2014-03-12','2014-04-20','2014-02-10','2014-01-06'),
(2,'2015-11-08','2014-03-18','2014-07-14',NULL,'2014-04-15','2014-01-17'),
(3,'2015-12-13','2014-11-11','2014-09-18','2014-09-01','2014-06-24','2014-01-28'),
(4,'2016-04-24','2014-12-20','2015-04-14','2015-12-27','2015-05-14',NULL),
(5,'2016-08-22','2015-11-16','2016-03-26','2016-08-31','2015-09-25','2015-02-20'),
(6,NULL,'2016-01-13','2016-08-02','2016-10-08',NULL,'2016-05-28'),
(7,'2016-09-22','2016-01-25','2017-03-06','2016-10-19','2017-02-03','2016-06-14'),
(8,'2017-05-21','2017-01-14','2017-11-07','2017-01-22','2017-02-15','2017-10-30'),
(9,'2017-12-15','2017-05-06',NULL,'2017-12-26','2017-11-07','2017-11-01');

The query:

WITH Unpivoted as (
SELECT [rowId], [date], [colId]
  FROM @dates
CROSS APPLY ( VALUES
              (id, date1, 1)
             ,(id, date2, 2)
             ,(id, date3, 3)
             ,(id, date4, 4)
             ,(id, date5, 5)
             ,(id, date6, 6)
             ) 
          AS T([rowId], [date], [colId])
)
, MinDate as (
SELECT [rowId], MIN([date]) as [date]
  FROM Unpivoted
  GROUP BY [rowId]
  )

  SELECT M.[rowId]
        ,M.[date]
        ,U.[colId]
  FROM [MinDate] as M
  JOIN Unpivoted as U
    ON M.[rowId] = u.[rowId]
   AND M.[date]  = u.[date]