CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlreporting
user3583912
user3583912

Reputation: 1322

MAX Date of multiple columns?

How do you return 1 value per row of the max of several columns:

TableName [RefNumber, FirstVisitedDate, SecondVisitedDate, RecoveryDate, ActionDate]

I want MaxDate of (FirstVisitedDate, SecondVisitedDate, RecoveryDate, ActionDate) these dates for all rows in single column and I want another new column(Acion) depends on Max date column for ex: if Max date is from FirstVisitedDate then it will be 'FirstVisited' or if Max date is from SecondVisitedDate then it will be 'SecondVisited'...

The Total result Like:

Select RefNumber, Maxdate, Action From Table group by RefNumber

Upvotes: 1

Views: 4458

Answers (4)

EarlOfEnnui
EarlOfEnnui

Reputation: 555

SELECT RecordID, MaxDate
FROM SourceTable
    CROSS APPLY (SELECT MAX(d) MaxDate 
                FROM (VALUES (date1), (date2), (date3), 
                             (date4), (date5), (date6), 
                             (date7)) AS dates(d)) md

Upvotes: 1

Malk
Malk

Reputation: 11983

;WITH cte AS (
  -- Build table of date 
  SELECT [RefNumber], 
         [ActionDate]= [FirstVisitedDate], 
         [Action] = 'First Visited' 
  FROM [Table1] 
  UNION ALL
  SELECT [RefNumber], 
         [SecondVisitedDate],  
         'Second Visited' 
  FROM [Table1] 
  UNION ALL
  SELECT [RefNumber], 
         [RecoveryDate], 
         'Recover'
  FROM [Table1]
), cte2 AS (
  -- Add row_number to pull most recent to top
  SELECT  [RefNumber],
          [Action],
          [ActionDate],
          [DateRank] = 
              ROW_NUMBER() OVER (PARTITION BY RefNumber 
                                 ORDER BY [ActionDate] DESC)
  FROM [cte]
)
-- select only the most recent
SELECT * 
FROM cte2 
WHERE [DateRank] = 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269823

The brute force approach isn't so bad with four columns:

select (case when FirstVisitedDate >= SecondVisitedDate and
                  FirstVisitedDate >= RecoveryDate and
                  FirstVisitedDate >= ActionDate
             then FirstVisitedDate
             when SecondVisitedDate >= RecoveryDate and
                  SecondVisitedDate >= ActionDate
             then SecondVisitedDate 
             when RecoveryDate >= ActionDate
             then RecoveryDate
             else ActionDate
        end),
       (case when FirstVisitedDate >= SecondVisitedDate and
                  FirstVisitedDate >= RecoveryDate and
                  FirstVisitedDate >= ActionDate
             then 'FirstVisitedDate'
             when SecondVisitedDate >= RecoveryDate and
                  SecondVisitedDate >= ActionDate
             then 'SecondVisitedDate'
             when RecoveryDate >= ActionDate
             then 'RecoveryDate'
             else 'ActionDate'
        end)     
from table t;

EDIT:

Doing this in a group by is just a question of adding aggregation functions:

select RefNumber,
       (case when max(FirstVisitedDate) >= max(SecondVisitedDate) and
                  max(FirstVisitedDate) >= max(RecoveryDate) and
                  max(FirstVisitedDate) >= max(ActionDate)
             then max(FirstVisitedDate
             when max(SecondVisitedDate) >= max(RecoveryDate) and
                  max(SecondVisitedDate) >= max(ActionDate)
             then max(SecondVisitedDate) 
             when max(RecoveryDate) >= max(ActionDate)
             then max(RecoveryDate)
             else max(ActionDate)
        end),
       (case when max(FirstVisitedDate) >= max(SecondVisitedDate) and
                  max(FirstVisitedDate) >= max(RecoveryDate) and
                  max(FirstVisitedDate) >= max(ActionDate)
             then 'FirstVisitedDate'
             when max(SecondVisitedDate) >= max(RecoveryDate) and
                  max(SecondVisitedDate) >= max(ActionDate)
             then 'SecondVisitedDate'
             when max(RecoveryDate) >= max(ActionDate)
             then 'RecoveryDate'
             else 'ActionDate'
        end)     
from table t
group by RefNumber;

Upvotes: 1

David
David

Reputation: 34563

I wrote a custom function to do this:

CREATE FUNCTION [dbo].[MaxOf5]
(
    @D1 DateTime,
    @D2 DateTime,
    @D3 DateTime,
    @D4 DateTime,
    @D5 DateTime
)
RETURNS DateTime
AS
BEGIN
    DECLARE @Result DateTime

    SET @Result = COALESCE(@D1, @D2, @D3, @D4, @D5)

    IF @D2 IS NOT NULL AND @D2 > @Result SET @Result = @D2
    IF @D3 IS NOT NULL AND @D3 > @Result SET @Result = @D3
    IF @D4 IS NOT NULL AND @D4 > @Result SET @Result = @D4
    IF @D5 IS NOT NULL AND @D5 > @Result SET @Result = @D5

    RETURN @Result
END

To call this, and calculated your Action column, this should work:

SELECT
    MaxDate,
    CASE WHEN MaxDate = FirstVisitedDate THEN 'FirstVisited'
       WHEN MaxDate = SecondVisitedDate THEN 'SecondVisited'
       WHEN MaxDate = RecoveryDate THEN 'Recovery'
       WHEN MaxDate = ActionDate THEN 'Action'
    END AS [Action]
FROM (
    SELECT
       RefNumber,
       dbo.MaxOf5(FirstVisitedDate, SecondVisitedDate, RecoveryDate, ActionDate) AS MaxDate,
       FirstVisitedDate, SecondVisitedDate, RecoveryDate, ActionDate
    FROM table
) AS data

Note that is is possible for more than one of your dates to be tied for the max date. In this case, the order of your WHEN clauses determines which one wins.

Upvotes: 0

Related Questions