Function 5 Dates- need to know what date category was greatest -return char (string)

[Rx_]

This is a working function called in a field of a View that supplies the ID_Wells.
It puts 5 dates into each associated variable.
If all dates are null, the function returns a string (char) of (empty string).

At this stage, it returns the most recent date of the 5 variables.
The goal:
Instead of returning the last date, the name of the variable needs to be returned. Then for each variable - a specific string can be returned.
example:
-- Example Case @StAPDApproved was the max - function will return string 'St Approved'
-- Example Case @stAPDExpiredDt was the max - function will return string 'St Expired'
-- (same for the other three)

What I think would work would be a CASE statement to match the @max with each of the 5 variables.

Q: I am new to TSQL, is the CASE the best option or would there be other suggestions?

CREATE FUNCTION [dbo].[RE_2100](@ID_Wells  int) 
RETURNS varchar(11)  --int
AS 
BEGIN; 
  DECLARE @CharResult as varchar(11)
  DECLARE @CharReturnValue as varchar(11)
  DECLARE @StAPDSubmittedDt as DATETIME
  DECLARE @StAPDApproved as DATETIME
  DECLARE @StAPDExpired	as DATETIME	
  DECLARE @StAPDWithDrawn	as DATETIME	
  DECLARE @StAPDDenied	as DATETIME	
  DECLARE @StAPDReturned	as DATETIME	
  DECLARE @Max  as datetime
  	
			-- Save State Submitted Date for Evaluation
			Set @StAPDSubmittedDt =	(SELECT Top 1 [Dt_APD_Sub]
				FROM tblAPD_Fed_ST_CO
				WHERE     (ID_Wells = @ID_Wells ) AND (txtFedStCo = 'St') AND (txt_APD_Apv_Status = 'Submitted') 
				AND (NOT (Dt_APD_Sub IS NULL)) AND ( (Dt_APD_WithDrawn_Sub IS NULL)) AND lngID_APD_Status  In(1,2,3,4)
				)
			-- Save State Approved (not expired still active) Date for Evaluation  - Date APD Submitted for the last Approved APD
			Set @StAPDApproved =	(SELECT Top 1 [Dt_APD_Sub] 
			FROM         tblAPD_Fed_ST_CO
			WHERE     (txt_APD_Apv_Status = N'Approved') AND (txtFedStCo = N'St') AND (Dt_APD_WithDrawn_Sub IS NULL) AND (Dt_ApprovalCancled IS NULL) AND 
                      (Dt_APD_Exp > GETDATE()) AND (ID_Wells = @ID_Wells) AND lngID_APD_Status  In(1,2,3,4)
                      			)
			-- Save State Expired Date for Evaluation (the date submitted for the most recent Expired APD)
			Set @StAPDExpired =	(SELECT Top 1 [Dt_APD_Sub] 
			FROM         tblAPD_Fed_ST_CO
			WHERE     (txt_APD_Apv_Status = N'Approved') AND (txtFedStCo = N'St') AND (Dt_APD_WithDrawn_Sub IS NULL) AND (Dt_ApprovalCancled IS NULL) AND 
                      (Dt_APD_Exp < GETDATE()) AND (ID_Wells = @ID_Wells) AND lngID_APD_Status  In(1,2,3,4)
                      			)
			-- Save State APD WithDrawn - Regardless of other status - a Withdrawn determines the status is withdrawn
			Set @StAPDWithDrawn =	(SELECT Top 1 [Dt_APD_WithDrawn_Sub] 
			FROM         tblAPD_Fed_ST_CO
			WHERE     (txtFedStCo = N'St') AND (NOT (Dt_APD_WithDrawn_Sub IS NULL)) AND (ID_Wells = @ID_Wells) AND lngID_APD_Status  In(1,2,3,4)
                      			)
			-- Save State APD Denied
			Set @StAPDDenied =	(SELECT Top 1 [Dt_APD_Sub] 
			FROM         tblAPD_Fed_ST_CO
			WHERE     (txtFedStCo = N'St') AND (txt_APD_Apv_Status IN (N'Denied')) AND (Dt_APD_WithDrawn_Sub IS NULL) AND (ID_Wells = @ID_Wells) AND lngID_APD_Status  In(1,2,3,4)
                      			)
			-- Save State APD Returned -- Bonus value added 1/12/2015
			Set @StAPDReturned =	(SELECT Top 1 [Dt_APD_Sub] 
			FROM         tblAPD_Fed_ST_CO
			WHERE     (txtFedStCo = N'St') AND (txt_APD_Apv_Status IN (N'Returned')) AND (Dt_APD_WithDrawn_Sub IS NULL) AND (ID_Wells = @ID_Wells) AND lngID_APD_Status  In(1,2,3,4)
                      			)
-- First check to see if all of the dates are null - return an empty string - the Max() won't do this
IF (NULLIF(@StAPDApproved, '') Is Null AND NULLIF(@StAPDExpired, '') 
		Is Null AND NULLIF(@StAPDWithDrawn, '') Is Null AND NULLIF(@StAPDDenied, '') 
		Is Null AND NULLIF(@StAPDReturned, '') Is Null)
	begin	
		Set @CharResult = '';   --All dates are Null function returns empty string for Permit Status
	end
ELSE 
   BEGIN
		--set ansi_warnings off -- removes harmless warning about null -- can't use in a function
		set @Max=(Select max(X)
		FROM (VALUES (@StAPDApproved), (@StAPDExpired), (@StAPDWithDrawn), (@StAPDDenied), (@StAPDReturned)) as value(X))
		--print @max
		Set @CharResult = @max
	End 
-- Example Case @StAPDApproved was the max - function will return string 'St Approved'
-- Example Case @stAPDExpiredDt was the max - function will return string 'St Expired'

-- Business Logic
-- Check to see if all @Dates are Null - to return a null (no APD's)
-- Do CASE or other logic to determine what @Date was latest
-- For the case, return the value "St APD Submitted", or "St APD Approved", or "State APD WithDrawn", or ....

Set @CharReturnValue = @CharResult
 return @CharReturnValue		-- Text value returned by function
END;

 

[kevlray]

Off the top of my head. I think some some sort of loop (basically a sort) to determine the max date (not sure what you do with a tie). I also think you would need to associate the variable names with the values (two dimension array). Do not have any code to assist you with.

 

[Rx_]

Should have mentioned, out of the 10,000 records there were no ties. Lucky for me the Access front-end form warns users about that.

 

[Rx_]

This has the Case statement added to the end. Works fast and great
Always interested in any comments on how something can be improved.
Just a beginner at TSQL

This is being called in the field of a SQL Server View - MS Access uses linked table to the view. This and a dozen other formulas in the view run in under 1 second against 100,000 records returning 10,000 business rule calculated results.

CREATE FUNCTION [dbo].[RE_2100](@ID_Wells  int) 
RETURNS varchar(14)  --int
AS 
BEGIN; 
  DECLARE @CharResult as varchar(14)
  DECLARE @CharReturnValue as varchar(14)
  DECLARE @StAPDSubmittedDt as DATETIME
  DECLARE @StAPDApproved as DATETIME
  DECLARE @StAPDExpired	as DATETIME	
  DECLARE @StAPDWithDrawn	as DATETIME	
  DECLARE @StAPDDenied	as DATETIME	
  DECLARE @StAPDReturned	as DATETIME	
  DECLARE @Max  as datetime
  	
			-- Save State Submitted Date for Evaluation
			Set @StAPDSubmittedDt =	(SELECT Top 1 [Dt_APD_Sub]
				FROM tblAPD_Fed_ST_CO
				WHERE     (ID_Wells = @ID_Wells ) AND (txtFedStCo = 'St') AND (txt_APD_Apv_Status = 'Submitted') 
				AND (NOT (Dt_APD_Sub IS NULL)) AND ( (Dt_APD_WithDrawn_Sub IS NULL)) AND lngID_APD_Status  In(1,2,3,4)
				)
			-- Save State Approved (not expired still active) Date for Evaluation  - Date APD Submitted for the last Approved APD
			Set @StAPDApproved =	(SELECT Top 1 [Dt_APD_Sub] 
			FROM         tblAPD_Fed_ST_CO
			WHERE     (txt_APD_Apv_Status = N'Approved') AND (txtFedStCo = N'St') AND (Dt_APD_WithDrawn_Sub IS NULL) AND (Dt_ApprovalCancled IS NULL) AND 
                      (Dt_APD_Exp > GETDATE()) AND (ID_Wells = @ID_Wells) AND lngID_APD_Status  In(1,2,3,4)
                      			)
			-- Save State Expired Date for Evaluation (the date submitted for the most recent Expired APD)
			Set @StAPDExpired =	(SELECT Top 1 [Dt_APD_Sub] 
			FROM         tblAPD_Fed_ST_CO
			WHERE     (txt_APD_Apv_Status = N'Approved') AND (txtFedStCo = N'St') AND (Dt_APD_WithDrawn_Sub IS NULL) AND (Dt_ApprovalCancled IS NULL) AND 
                      (Dt_APD_Exp < GETDATE()) AND (ID_Wells = @ID_Wells) AND lngID_APD_Status  In(1,2,3,4)
                      			)
			-- Save State APD WithDrawn - Regardless of other status - a Withdrawn determines the status is withdrawn
			Set @StAPDWithDrawn =	(SELECT Top 1 [Dt_APD_WithDrawn_Sub] 
			FROM         tblAPD_Fed_ST_CO
			WHERE     (txtFedStCo = N'St') AND (NOT (Dt_APD_WithDrawn_Sub IS NULL)) AND (ID_Wells = @ID_Wells) AND lngID_APD_Status  In(1,2,3,4)
                      			)
			-- Save State APD Denied
			Set @StAPDDenied =	(SELECT Top 1 [Dt_APD_Sub] 
			FROM         tblAPD_Fed_ST_CO
			WHERE     (txtFedStCo = N'St') AND (txt_APD_Apv_Status IN (N'Denied')) AND (Dt_APD_WithDrawn_Sub IS NULL) AND (ID_Wells = @ID_Wells) AND lngID_APD_Status  In(1,2,3,4)
                      			)
			-- Save State APD Returned -- Bonus value added 1/12/2015
			Set @StAPDReturned =	(SELECT Top 1 [Dt_APD_Sub] 
			FROM         tblAPD_Fed_ST_CO
			WHERE     (txtFedStCo = N'St') AND (txt_APD_Apv_Status IN (N'Returned')) AND (Dt_APD_WithDrawn_Sub IS NULL) AND (ID_Wells = @ID_Wells) AND lngID_APD_Status  In(1,2,3,4)
                      			)
-- First check to see if all of the dates are null - return an empty string - the Max() won't do this
IF (NULLIF(@StAPDApproved, '') Is Null AND NULLIF(@StAPDExpired, '') 
		Is Null AND NULLIF(@StAPDWithDrawn, '') Is Null AND NULLIF(@StAPDDenied, '') 
		Is Null AND NULLIF(@StAPDReturned, '') Is Null)
	begin	
		Set @CharResult = char(10);   --All dates are Null function returns empty string for Permit Status
	end
ELSE 
   BEGIN
		--set ansi_warnings off -- removes harmless warning about null -- can't use in a function
		set @Max=(Select max(X)
		FROM (VALUES (@StAPDApproved), (@StAPDExpired), (@StAPDWithDrawn), (@StAPDDenied), (@StAPDReturned)) as value(X))
		--print @max
		Set @CharResult = @max
	 
-- Example Case @StAPDApproved was the max - function will return string 'St Approved'
-- Example Case @stAPDExpiredDt was the max - function will return string 'St Expired'
SET @CharResult = CASE 
WHEN @max = @StAPDApproved THEN 'ST Approved' 
WHEN @max = @StAPDExpired THEN 'St Expired' 
WHEN @max = @StAPDWithDrawn THEN 'St Withdrawn' 
WHEN @max = @StAPDDenied THEN 'St Withdrawn' 
WHEN @max = @StAPDReturned THEN 'St Withdrawn' 
ELSE 'St Error' 
END  -- End Case
	End -- end Else statement
-- Business Logic
-- Check to see if all @Dates are Null - to return a null (no APD's)
-- Do CASE or other logic to determine what @Date was latest
-- For the case, return the value "St APD Submitted", or "St APD Approved", or "State APD WithDrawn", or ....

Set @CharReturnValue = @CharResult
 return @CharReturnValue		-- Text value returned by function
END; 
GO

 

[kevlray]

For SQL questions I normally go to SQLServerCentral forum. Even though I do not have enough knowledge to assist most people that post there. I have learned a lot from their newsletters.

 

[WayneRyan]

Rx,

Sorry, I had trouble communication via the phone interface.

CREATE FUNCTION [dbo].[RE_2100](@StAPDSubmittedDt DATETIME,
                                @StAPDApproved    DATETIME,
                                @StAPDExpired	  DATETIME,	
                                @StAPDWithDrawn	  DATETIME,	
                                @StAPDDenied	  DATETIME	
                                @StAPDReturned	  DATETIME) 
RETURNS varchar(11)
AS
BEGIN
Declare @ColumnName As Varchar(20);
  WITH Dates_CTE (TheDate, TheName) As
     ( Select @StAPDSubmittedDt As TheDate, 'StAPDSubmittedDt' As TheName Union
       Select @StAPDApproved    As TheDate, 'StAPDApproved'    As TheName Union
       Select @StAPDExpired     As TheDate, 'StAPDExpired'     As TheName Union
       Select @StAPDWithDrawn   As TheDate, 'StAPDWithDrawn'   As TheName Union
       Select @StAPDDenied      As TheDate, 'StAPDDenied'      As TheName Union	
       Select @StAPDReturned    As TheDate, 'StAPDReturned'    As TheName
     )

  SELECT @ColumnName = TheName
  FROM Dates_CTE
  WHERE TheDate In (Select Max(TheDate)
                    From   Dates_CTE);
  Return(@ColumnName);

END;

hth,
Wayne