Get the earliest of a date-value pair in T-SQL

Question

I am trying to build a query in t-sql to add a custom date-value pair column. My data contains the project ID, L Date and L Status, N Date and N Status. I know how to add the earliest date using min function. How do I capture the earliest date and it's corresponding status in my new column? In my example below, I need to find the earliest pair. My supposed query should bring the results in Earliest Dt and Status based on the data in the N and L date value pairs.

Answer 1

You could do this with a lateral join. The idea is to unpivot the column to columns, then sort:

select t.*, x.*
from mytable t
cross apply (
    select top (1) x.*
    from (values (t.l_date, t.l_status), (t.n_date, t.n_status)) x(earlierst_dt, status)
    order by x.earlierst_dt desc
) x

This works because SQL Server puts null values last when sorting in descending order.

You could also use a case expression, but the logic is a bit cumbersome to type:

select t.*,
    case when l_date > n_date or (l_date is not null and n_date is null) then l_date   else n_date   end as earlierst_date,
    case when l_date > n_date or (l_date is not null and n_date is null) then l_status else n_status end as status
from mytable t