Max of 2 columns gets sent to the third column (SQL)-CodePudding

I am trying to combine two columns (Current last receipt) and (Historic last receipt). I basically want to create a third column (MAX Last Process Date) that will be the most current process date from both columns.

So like for this item I need to have this value '20210804' as the value

Here is my code

  SELECT w2.RCWHS# AS DC, w2.RCITM# AS 'Item Number', CONCAT(w2.RCWHS#, ' ', w2.RCITM#) AS 'Key', MAX(w1.RCLDTE) AS 'Current last receipt', MAX(w2.RCLDTE) AS 'Historic last receipt'
  FROM LEVYDTA.RECTRNH w2 
  FULL OUTER JOIN LEVYDTA.RECTRNT w1
  ON w1.RCWHS#=w2.RCWHS# AND w1.RCITM#=w2.RCITM#
  GROUP BY w2.RCWHS#, w2.RCITM#
  ORDER BY 'Key'

If someone may know how to do it, I will greatly appreciate.

Thank you!!

CodePudding user response：

You may try the following with a case expression

 SELECT 
     w2.RCWHS# AS DC, 
     w2.RCITM# AS 'Item Number', 
     CONCAT(w2.RCWHS#, ' ', w2.RCITM#) AS 'Key', 
     MAX(w1.RCLDTE) AS 'Current last receipt', 
     MAX(w2.RCLDTE) AS 'Historic last receipt',
     CASE 
         WHEN MAX(w1.RCLDTE) > MAX(w2.RCLDTE) THEN MAX(w1.RCLDTE)
         ELSE MAX(w2.RCLDTE)
     END as 'MAX Last Process Date'
  FROM LEVYDTA.RECTRNH w2 
  FULL OUTER JOIN LEVYDTA.RECTRNT w1
  ON w1.RCWHS#=w2.RCWHS# AND w1.RCITM#=w2.RCITM#
  GROUP BY w2.RCWHS#, w2.RCITM#
  ORDER BY 'Key'

Let me know if this works for you.