Return largest date

Question

I have SQL 2016 stored procedure that I want to pass the highest of 3 date fields to a UDF (dbo.GetBusinessHoursOut). The current code is shown below. Besides InsuranceApproved and ArrivalDate I want to compare ScheduledInDate so that I pass the most recent date value to the UDF.

SELECT CASE WHEN R.JobSize = 'S'   
		AND @Stage = 1   
		THEN dbo.GetBusinessHoursOut (CASE WHEN R.InsuranceApproved > R.ArrivalDate THEN R.InsuranceApproved   
						   ELSE R.ArrivalDate   
						   END, 1)  
FROM dbo.RepairOrder R  
WHERE R.ScheduledInDate > '2021-01-01'  


  

Answer 1

There are several methods: https://www.bing.com/search?q=t-sql+max+of+three+values. For example:

CASE WHEN InsuranceApproved >= ArrivalDate AND InsuranceApproved >= ScheduledInDate THEN InsuranceApproved 
     WHEN ArrivalDate >= ScheduledInDate THEN ArrivalDate
     ELSE ScheduledInDate END

(It assumes that the values are not null).

You will also find this:

(SELECT MAX(d) FROM (VALUES (InsuranceApproved), (ArrivalDate), (ScheduledInDate)) D(d))

Answer 2

As a teaser, on Azure SQL DB, you can say:

greatest(InsuranceApproved, ArrivalDate, ScheduledInDate)

plain and simple.

But this is not yet available in the box product.