How to pull most current date from two fields

Question

I have a table that contains two Due Date fields. Sometimes they both can match, sometimes one of them is blank, sometimes one of them is greater than the other. I need how I can pull the most current Due Date into just one field from both fields. The fields are PBDUE and XDCURDT and they are both datetime in the table.

Do you want a single value in the output, or do you want the most recent date from *each row* in the output? Showing sample data and desired results (as I did in my answer) is much more useful in describing the scenario than a word problem. — Aaron Bertrand, Aug 16 '13 at 18:10

score 1 · Accepted Answer · answered Aug 16 '13 at 18:03

Assuming both columns are dates (date/datetime/etc.) and you mean NULL when you say blank:

SELECT CASE WHEN PBDUE >= XDCURDT THEN PBDUE ELSE XDCURDT END DUEDT
FROM (
    SELECT MAX(PBDUE) PBDUE, MAX(XDCURDT) XDCURDT FROM someTable
) t

The inner query computes the maximum (most recent) value of each column and the outer one returns the greatest (or PBDUE if both are equal).

Aaron Bertrand · Answer 2 · 2013-08-16T18:19:14.230

DECLARE @x TABLE(ID INT,PBDUE DATE, XDCURDT DATE);

INSERT @x VALUES
(1,'2013-01-01','2012-01-01'), -- PBDUE greater
(2,'2010-01-01','2011-01-01'), -- XDCURDT greater
(3,NULL,'2009-01-01'), -- PBDUE "blank"
(4,'2008-01-01',NULL); -- XDCURDT "blank"

-- if you want one max date per row:

SELECT ID, MostCurrentDate = CASE
  WHEN PBDUE >= COALESCE(XDCURDT, '1900-01-01') THEN PBDUE
  WHEN XDCURDT >= COALESCE(PBDUE, '1900-01-01') THEN XDCURDT
  -- might want an ELSE if you don't want NULL
  -- when both values are NULL
  END
FROM @x
ORDER BY ID;

-- if you want one max date for the whole table:

SELECT MostCurrentDate = MAX(d) FROM (SELECT CASE
  WHEN PBDUE >= COALESCE(XDCURDT, '1900-01-01') THEN PBDUE
  WHEN XDCURDT >= COALESCE(PBDUE, '1900-01-01') THEN XDCURDT
  END
FROM @x) AS c(d);

Results:

ID   MostCurrentDate
--   ---------------
1    2013-01-01
2    2011-01-01
3    2009-01-01
4    2008-01-01

MostCurrentDate
---------------
2013-01-01

How to pull most current date from two fields

2 Answers2