CODEWITHSUNDEEP
t-sqlsql-server-2012-express
tsqln00b
tsqln00b

Reputation: 355

How to pull most current date from two fields

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.

Upvotes: 0

Views: 63

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280645

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

Upvotes: 0

ssarabando
ssarabando

Reputation: 3517

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

Upvotes: 1

Related Questions