Max Date in a row with multiple dates

• Uncle Boris

  SSC Enthusiast

  Points: 191

  More actions

  May 13, 2008 at 10:26 pm

  #190519

  Hi, maybe somebody will have a solution.

  SQL 2005

  I have a query than joins 5 tables.

  Each table has a created_date.

  So as an example something like:

  select

  a.created_date,

  b.created_date,

  c.created_date,

  d.created_date,

  e.created_date,

  .

  .

  from

  table1

  join table2 on....

  join table3 on...

  etc

  Based on this result set I want to get MAX created_date per row i.e the max created date of the 5 fields per row (a.created_date, b.created_date,c.created_date etc)

  I need this date for further querying.

  The created_date fields are datetime type

  Does anybody know the best way to do this.

  I can do it in a function but thought it might be possible to do it some other more efficient way.

  Or use a nested case statements and filter until only one value is left!!

  Just let me know if I have not made myself clear.

  thanks

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  May 14, 2008 at 2:37 am

  #814982

  SELECT... ,

  (SELECTMAX(Created_Date)

  FROM(

  SELECT a.created_date UNION ALL

  SELECT b.created_date UNION ALL

  SELECT c.created_date UNION ALL

  SELECT d.created_date UNION ALL

  SELECT e.created_date

  ) AS d

  ) AS MaxCreatedDate

  FROMTable1 AS a

  INNER JOINTable2 AS b ...

  INNER JOIN...

  ---

  N 56°04'39.16"
  E 12°55'05.25"

• raja_saminathan

  SSCommitted

  Points: 1607

  More actions

  May 15, 2008 at 2:37 am

  #815702

  Hi,

  Hope this link will help you

  http://msdn.microsoft.com/en-us/library/ms177410.aspx

  Rajesh