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

Question

Post reply

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

JeremyU

SSChasing Mays

Points: 640
More actions
October 7, 2021 at 10:14 pm

#3938643
Hello everyone,
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!!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

You'd have to use IIF([Date1]>=[Date2],[Date1],[Date2]) Then maybe do an aggregate on that expression.

kaj SSCommitted Points: 1605 More actions · Answer 2

In SQL Server 2019 you can also utilize the GREATEST function.

https://docs.microsoft.com/en-us/sql/t-sql/functions/logical-functions-greatest-transact-sql?view=sql-server-ver15

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

kaj wrote:

In SQL Server 2019 you can also utilize the GREATEST function.
https://docs.microsoft.com/en-us/sql/t-sql/functions/logical-functions-greatest-transact-sql?view=sql-server-ver15

That documentation seems to suggest that it's only available on Azure instances and not "standalone" instances. Can someone with a "standalone" instance of 2019 please verify if this and the "LEAST" functions are available?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

Jeff Moden wrote:

kaj wrote:
In SQL Server 2019 you can also utilize the GREATEST function.
https://docs.microsoft.com/en-us/sql/t-sql/functions/logical-functions-greatest-transact-sql?view=sql-server-ver15
That documentation seems to suggest that it's only available on Azure instances and not "standalone" instances. Can someone with a "standalone" instance of 2019 please verify if this and the "LEAST" functions are available?

I get this error

Msg 195, Level 15, State 10, Line 3
'GREATEST' is not a recognized built-in function name.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

I kinda figured. Thanks, for the confirmation.

After the performance issues included with the FORMAT function, I also worry about performance anytime MS comes out with a new function. I won't be able to test GREATEST/LEAST because I don't use Azure at all.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kaj SSCommitted Points: 1605 More actions · Answer 6

I'm sorry about the misinformation. I didn't pay attention to the list of "Applies to" services.

I remembered some excited chatter some months ago about the functions now being available, but I overlooked that little disclaimer.

https://techcommunity.microsoft.com/t5/azure-sql/introducing-the-greatest-and-least-t-sql-functions/ba-p/2281726

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 7

This should work

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 ISNULL(MAX(w1.RCLDTE),'19000101') > ISNULL(MAX(w2.RCLDTE),'19000101')
                THEN MAX(w1.RCLDTE)
            ELSE MAX(w2.RCLDTE)
       END                               AS 'Last Process Date'
  FROM LEVYDTA.RECTRNH w2 
  FULL JOIN LEVYDTA.RECTRNT w1
         ON w1.RCWHS#=w2.RCWHS# AND w1.RCITM#=w2.RCITM#
 GROUP BY w2.RCWHS#, w2.RCITM#
 ORDER BY 'Key'

Steve Collins SSCrazy Points: 2157 More actions · Answer 8

[Edit] I posted some code that didn't look right so I updated. Maybe something like this

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],
       MAX(mx.RCLDTE_one_col) as [Last Process Date]
from LEVYDTA.RECTRNH w2 
     full join LEVYDTA.RECTRNT w1 on w1.RCWHS#=w2.RCWHS#
                                     and w1.RCITM#=w2.RCITM#
     outer apply (select max(unpvt.RCLDTE_one_col)
                  from (values (ISNULL(w1.RCLDTE,'19000101')),
                               (ISNULL(w2.RCLDTE,'19000101')))
                               unpvt(RCLDTE_one_col)) mx(RCLDTE_one_col)
group by w2.RCWHS#, w2.RCITM#;

This reply was modified 3 years, 1 month ago by Steve Collins.
This reply was modified 3 years, 1 month ago by Steve Collins.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

kaj wrote:

I'm sorry about the misinformation. I didn't pay attention to the list of "Applies to" services.
I remembered some excited chatter some months ago about the functions now being available, but I overlooked that little disclaimer.
https://techcommunity.microsoft.com/t5/azure-sql/introducing-the-greatest-and-least-t-sql-functions/ba-p/2281726

You're definitely one of the "good guys" for posting that kind of feedback. And, totally understood. Heh... I remember that same kind of excited chatter but couldn't remember the scope and had to look it up myself. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)