Getting most recent date from multiple SQL columns

Question

The suggested answer, in this post, works great for two columns. I have about 50 different date columns, where I need to be able to report on the most recent interaction, regardless of table.

In this case, I am bringing the columns in to a view, since they are coming from different tables in two different databases...

    CREATE VIEW vMyView
    AS
    SELECT 
    comp_name AS Customer
    , Comp_UpdatedDate AS Last_Change
    , CmLi_UpdatedDate AS Last_Communication
    , Case_UpdatedDate AS Last_Case
    , AdLi_UpdatedDate AS Address_Change
    FROM Company
    LEFT JOIN Comm_Link on Comp_CompanyId = CmLi_Comm_CompanyId
    LEFT JOIN Cases ON Comp_CompanyId = Case_PrimaryCompanyId 
    LEFT JOIN Address_Link on Comp_CompanyId = AdLi_CompanyID
...

My question is, how I would easily account for the many possibilities of one column being greater than the others?

Using only the two first columns, as per the example above, works great. But considering that one row could have column 3 as the highest value, another row could have column 14 etc...

SELECT Customer, MAX(CASE WHEN (Last_Change IS NULL OR Last_Communication> Last_Change)
                THEN Last_Communication ELSE Last_Change 
                END) AS MaxDate
FROM vMyView
GROUP BY Customer

So, how can I easily grab the highest value for each row in any of the 50(ish) columns?

I am using SQL Server 2008 R2, but I also need this to work in versions 2012 and 2014.

Any help would be greatly appreciated.

EDIT:

I just discovered that the second database is storing the dates in NUMERIC fields, rather than DATETIME. (Stupid! I know!)

So I get the error: The type of column "ARCUS" conflicts with the type of other columns specified in the UNPIVOT list.

I tried to resolve this with a CAST to make it DATETIME, but that only resulted in more errors.

    ;WITH X AS 
(
    SELECT Customer
          ,Value      [Date]
          ,ColumnName [Entity]
          ,BusinessEmail 
          ,ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY Value DESC) rn
    FROM (
    SELECT comp_name AS Customer
        , Pers_EmailAddress AS BusinessEmail
        , Comp_UpdatedDate AS Company
        , CmLi_UpdatedDate AS Communication
        , Case_UpdatedDate AS [Case]
        , AdLi_UpdatedDate AS [Address]
        , PLink_UpdatedDate AS Phone
        , ELink_UpdatedDate AS Email
        , Pers_UpdatedDate AS Person
        , oppo_updateddate as Opportunity
        , samdat.dbo.ARCUS.AUDTDATE AS ARCUS 
         

        FROM vCompanyPE
        
        LEFT JOIN Comm_Link on Comp_CompanyId = CmLi_Comm_CompanyId
        LEFT JOIN Cases ON Comp_CompanyId = Case_PrimaryCompanyId 
        LEFT JOIN Address_Link on Comp_CompanyId = AdLi_CompanyID
        LEFT JOIN PhoneLink on Comp_CompanyId = PLink_RecordID 
        LEFT JOIN EmailLink on Comp_CompanyId = ELink_RecordID
        LEFT JOIN vPersonPE on Comp_CompanyId = Pers_CompanyId
        LEFT JOIN Opportunity on Comp_CompanyId = Oppo_PrimaryCompanyId
        LEFT JOIN Orders on Oppo_OpportunityId = Orde_opportunityid
        
        LEFT JOIN SAMDAT.DBO.ARCUS on IDCUST = Comp_IdCust
        
        COLLATE Latin1_General_CI_AS 
        WHERE Comp_IdCust IS NOT NULL
        AND Comp_deleted IS NULL
         ) t
         
     UNPIVOT (Value FOR ColumnName IN 
                (
        Company
        ,Communication
        ,[Case]
        ,[Address]
        ,Phone
        ,Email
        ,Person     
        ,Opportunity
        ,ARCUS

                )
             )up
)


 SELECT Customer
      , BusinessEmail
      ,[Date]
      ,[Entity]
FROM X 
WHERE rn = 1 AND [DATE] >= DATEADD(year,-2,GETDATE()) and BusinessEmail is not null

Answer 1

You could use CROSS APPLY to manually pivot your fields, then use MAX()

SELECT
    vMyView.*,
    greatest.val
FROM
    vMyView
CROSS APPLY
(
    SELECT
        MAX(val) AS val
    FROM
    (
              SELECT vMyView.field01 AS val
    UNION ALL SELECT vMyView.field02 AS val
    ...
    UNION ALL SELECT vMyView.field50 AS val
    )
        AS manual_pivot
)
    AS greatest

The inner most query will pivot each field in to a new row, then the MAX() re-aggregate them back in to a single row. (Also skipping NULLs, so you don't need to explicitly cater for them.)

Answer 2

;WITH X AS 
(
    SELECT Customer
          ,Value      [Date]
          ,ColumnName [CommunicationType]
          ,ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY Value DESC) rn
    FROM (
    SELECT comp_name AS Customer
        , Comp_UpdatedDate AS Last_Change
        , CmLi_UpdatedDate AS Last_Communication
        , Case_UpdatedDate AS Last_Case
        , AdLi_UpdatedDate AS Address_Change
        FROM Company
        LEFT JOIN Comm_Link on Comp_CompanyId = CmLi_Comm_CompanyId
        LEFT JOIN Cases ON Comp_CompanyId = Case_PrimaryCompanyId 
        LEFT JOIN Address_Link on Comp_CompanyId = AdLi_CompanyID
    ) t
     UNPIVOT (Value FOR ColumnName IN (Last_Change,Last_Communication,
                                   Last_Case,Address_Change))up
 )
 SELECT Customer
      ,[Date]
      ,[CommunicationType]
FROM X 
WHERE rn = 1