Question: Background: I need to write a function in T-SQL on SQL Server 2008 10.0.5869.

Here's the table I'm working on (for the sake of simplicity - I only put in 3 columns here - but I have 10 columns for the actual work):

```
ID | Column1 | Column2 | Column3
1  | 2014-05 | 2015-02 | 2013-04
2  | 2012-09 | 2011-02 | 2013-03

```

ID is varchar and Column(x) are all datetime.

My end goal is to design a function fn\_CompareDate to do something like this:

```
select fn_CompareDate(ID) from table where ID = 1

```

**The query above should return the latest date from Column(x)s which should be 2015-02.**

I used CASE WHEN but it would be almost impossible to use it for 10 columns. Is there another way to achieve the same result?

Answer: 
One approach is to use `apply`:

```
select d.maxd
from table t cross apply
     (select max(d) as maxd
      from values ((id, column1), (id, column2), (id, column3)) as val(id, d)
      where val.id = t.id
     ) d
where t.id = 1;

```

EDIT:

You can do this without `values()`:

```
select d.maxd
from table t cross apply
     (select max(d) as maxd
      from (select id, column1 as d union all
            select id, column2 union all
            select id, column3 union all
            select id, column4
           ) val
      where t.id = val.id
     ) d
where t.id = 1;

```
I found the 2nd solution from [this question](https://dba.stackexchange.com/questions/21542/what-is-the-most-efficient-way-to-get-the-minimum-of-multiple-columns-on-sql-ser?newreg=dddc7537e3c64fe7a2b9cc65208a9b58) works quite well for me:

Create a function similar to this:

```
select max(col) from 
    (
        select column1 [col] from table where id = @id
        union all
        select column2 from table where id = @id
        union all
        select column3 from table where id = @id
    )

```