TSQL - case - Latest date for 5 Date variables - Identifgy what variable had latest (1 Viewer)

[Rx_]

There are 5 Variables with any combination of Null or a valid date.
The objective is to identify the variable with the latest (most recent) date.
Note: I don't want the actual date returned
Instead, I want to know what variable the latest date was in.

If @StAPDApprovedDt and @StAPDExpired were the only two with dates, and
@StAPDExpired was the most recent date then return the string 'StAPDExpired'



Code properly assigns these 5 variables with either Null or a valid date
--DECLARE @StAPDApprovedDt as DATETIME
--DECLARE @StAPDExpired as DATETIME
--DECLARE @StAPDWithDrawn as DATETIME
--DECLARE @StAPDDenied as DATETIME
--DECLARE @StAPDReturned as DATETIME

If all of them are null, return an empty string e.g.
IF (NULLIF(@StAPDApprovedDt, '') Is Null AND NULLIF(StAPDExpired, '')
Is Null AND NULLIF(StAPDWithDrawn, '') Is Null AND NULLIF(StAPDDenied, '')
Is Null AND NULLIF(StAPDReturned, '') Is Null)
Set @CharResult = ''; --All dates are Null, return empty string

 

[Rx_]

Paste this code in a New Query on SQL Server
run with StAPDExpired = to null then replace it with '01/01/2010'
The Null shows to be later. It just adds another complexity to the equation.

DECLARE @StAPDApprovedDt as DATETIME 
DECLARE @StAPDExpired	as DATETIME	
DECLARE @StAPDWithDrawn	as DATETIME	
DECLARE @StAPDDenied	as DATETIME	
DECLARE @StAPDReturned	as DATETIME	
set @StAPDApprovedDt = '01/01/2014'
set @StAPDExpired =  Null        --'01/01/2010'

IF @StAPDApprovedDt > @StAPDExpired
      begin
      PRINT 'StAPDApprovedDt is larger'
      end 
   ELSE
      begin
      PRINT 'StAPDExpired is larger'
      end 
 -- Null ends up being latest

 

[pbaldy]

This works for me:

IF Coalesce(@StAPDApprovedDt,0) > Coalesce(@StAPDExpired,0)

 

[Rx_]

Put this in a New Query

Go
DECLARE @StAPDApprovedDt as DATETIME 
DECLARE @StAPDExpired	as DATETIME	
DECLARE @StAPDWithDrawn	as DATETIME	
DECLARE @StAPDDenied	as DATETIME	
DECLARE @StAPDReturned	as DATETIME	
DECLARE @Max  as datetime
-- a complex function assigns a Null or Date to each variable (not shown)
-- This allows dates and nulls to be tested in a New Query window
set @StAPDApprovedDt = '01/01/2014' -- change from null to other date
set @StAPDExpired =  '01/01/2010'
set @StAPDWithDrawn = null
set @StAPDDenied = '01/01/2007'
set @StAPDReturned = null
set ansi_warnings off -- removes harmless warning about null
 set @Max=(Select max(X)
 FROM (VALUES (@StAPDApprovedDt), (@StAPDExpired), (@StAPDWithDrawn), (@StAPDDenied), (@StAPDReturned)) as value(X))
print @max

Took me a while. Just one ( paren) in the wrong place...

Of course, the goal was not to get the date, but the variable where the date came from. However, this allows (in the next step not shown) a Case Statement to match the date and assign the text.

I think this will allow for growth should another variable get added to the mix.
Can you see any downside to this?

 

[pbaldy]

That's new syntax on me and returns an error on a 2005 box:

Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'VALUES'.

It could certainly be a newer feature that I'm not aware of; that list is extremely long.

 

[WayneRyan]

Rx,

on a cellphone so this is a little rough.

how about doing this with a query ...

With mydates as
(Select @date1 as thedate, 'name1' as thename Union
Select @date2 as thedate, 'name2' as thename Union ...)
Select top1 thename
From mydates
Order by thedate desc

should be a lot more concise and flexible.

If your Sql server doesn't support With, use a #temp table.

Hth,
Wayne

 

[Rx_]

You could be right about the 2005.
The code searches on the internet, didn't have too many examples. That might account for the new in 2008.
I am using SQL Server 2008 R2

Wayne, Don't know if I mentioned this is running in a function.
I am on a deadline. Will post this in new question.
The last date isn't my final answer. I need to know what variable provided the final answer. Thinking a case statement after the fact.

Thanks for the input. Please watch for my next post.