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

Rx_

Rx_

Nothing In Moderation
Local time
Today, 05:32
Joined
Oct 22, 2009
Messages
2,803
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?

Code: 
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

kevlray

Registered User.
Local time
Today, 04:32
Joined
Apr 5, 2010
Messages
1,046
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_

Rx_

Nothing In Moderation
Local time
Today, 05:32
Joined
Oct 22, 2009
Messages
2,803
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_

Rx_

Nothing In Moderation
Local time
Today, 05:32
Joined
Oct 22, 2009
Messages
2,803
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.
Code: 
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

kevlray

Registered User.
Local time
Today, 04:32
Joined
Apr 5, 2010
Messages
1,046
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.
 
W

WayneRyan

AWF VIP
Local time
Today, 12:32
Joined
Nov 19, 2002
Messages
7,122
Rx,

Sorry, I had trouble communication via the phone interface.

Code: 
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
 
You must log in or register to reply here.

Similar threads

Rx_
TSQL - case - Latest date for 5 Date variables - Identifgy what variable had latest
Replies
6
Views
1,289
Rx_
Rx_

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom