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

You could look into using UNPIVOT to return all those date columns as a single column and then get the MAX value of that column. — Tab Alleman, Sep 02 '15 at 15:37
This post may do what you want: [Create Compare Function](http://stackoverflow.com/questions/124417/is-there-a-max-function-in-sql-server-that-takes-two-values-like-math-max-in-ne) — Jesse Potter, Sep 02 '15 at 15:42
Sounds like a broken scema design. If you have that many date columns, this data should have been placed in a separate table where you could use a GROUP BY clause and MAX() aggregate function. — Joel Coehoorn, Sep 02 '15 at 16:18
You should be able to transform each "date" field in to the same data type in your view (Or inner most query), then the pivot won't complain. If you're getting other errors then we need to see ***exactly*** what they are. Perhaps the problem field is stored as a Unix epoch? In which case use `DATEADD(seconds, AUDTDATE, 0)`? — MatBailie, Sep 03 '15 at 06:38
@Joelcoehoorn: at risk of giving my self foot-in-mouth disease... From what I see here, I disagree. If I understand, you're suggesting EAV? That alone would make me hesitate. But here the dates are from multiple tables and even at least two databases, where the dates could be parts of Primary (or Natural) Keys. — MatBailie, Sep 03 '15 at 06:49

MatBailie · Answer 1 · 2015-09-02T16:16:10.817

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.)

edited Sep 02 '15 at 16:16

answered Sep 02 '15 at 16:10

MatBailie

83,401
18
103
137

ouch!!! this many union all(s) will hurt the performance , wont they? as far as eliminating nulls is concerned unpivot also eliminate nulls. Also the max value returned from CROSS APPLY how it will be joined to the results returned by the outer select ?? – M.Ali Sep 02 '15 at 16:20
@MAli : No, it won't hurt performance in my experience. CROSS APPLY applies the query as a function to each row, so the MAX is only for the 50 values from that row. The optimiser will also see that there is only one row of results per row of inputs, and no joins, and so you effectively get a horizontal MAX (like GREATEST) rather than a vertical MAX. Still, best advice is to try the alternatives yourself, and see. – MatBailie Sep 02 '15 at 16:46
yes cross apply is good but I am concerned about these union all also whats the relation between the row being returned from cross apply and the row returned by the outer select ? – M.Ali Sep 02 '15 at 16:48
@M.Ali : Each row is treated as the input to the CROSS APPLY separately from each other row. The results from that row's inputs are joined on to the input row. In this case one field is added to the input. That's why the UNIONs don't need to have a `FROM clause`, all the references *(in this case)* are to the row currently being processed in the outer query. – MatBailie Sep 03 '15 at 13:25

M.Ali · Accepted Answer · 2015-09-02T16:05:33.127

;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

Msg 102, Level 15, State 1, Line 13 Incorrect syntax near 't'. — Creamore, Sep 02 '15 at 16:01

Getting most recent date from multiple SQL columns

EDIT:

2 Answers2