How do I return a value using a function within a case statement to choose the MAX of each function?

Question

I have a query where I am calculating the difference between multiple date/times using a function. It returns the time between the 2 dates (imported_date and create_date for the first line and picked_date and packed_on_date on the second line) in minutes.

Examples below: dbo.WorkTime(o.imported_date,pkd.create_date) dbo.WorkTime(pkd.picked_date, hums.packed_on_date)

My desired result would be to return a status for each line based on the process that took the longest time. If I were doing this in excel I would create an IF statement.

Is there a way for me to write a case statement or something else that will return a status based on the MAX of each functions process times?

Answer 1

Try this As query

select   ( case when dbo.WorkTime(o.imported_date,pkd.create_date) >=
dbo.WorkTime(pkd.picked_date, hums.packed_on_date) then 
dbo.WorkTime(o.imported_date,pkd.create_date) else 
dbo.WorkTime(pkd.picked_date, hums.packed_on_date) end )  MaxTime

or declare a variable

 declare @MaxTime float;
 Set  @MaxTime = ( case when dbo.WorkTime(o.imported_date,pkd.create_date) >= 
 dbo.WorkTime(pkd.picked_date, hums.packed_on_date) then
 dbo.WorkTime(o.imported_date,pkd.create_date) else  
 dbo.WorkTime(pkd.picked_date, hums.packed_on_date)
 end )  MaxTime
 return @MaxTime;