Trying to obtain the earlier datetime record between two datetimes

This one has me perplexed

I have a view I built  the following is an example of the results:
Results
I have built an sql function that I want to use to return the ProtocolResponse that is earlier datetime record between two datetimes

so in essence if the @ActionDate is 2018-2-16-18:43:00  I want to return V.Fib/Tach
if @ActionDate  is 2018-02-16 18:55:00 I want to return PEA

for some reason,  I guess I am just stupid I can't figure a way to do do it

here is the function
ALTER FUNCTION [dbo].[ProtocolResponseFind](@ActionDate datetime , @PRID_Unique varchar(30)) 
RETURNS varchar(50)
BEGIN
	
	 declare @Return Varchar(50);
	 set @Return= (select top 1 ProtocolResponse from  dbo.Criteria_Map_Protocol_Response where ( ????????????) and @PRID_Unique=PRID_Unique
	 )
	Return @Return;
END

Open in new window



Thank you
LVL 5
WillOwnerAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
ALTER FUNCTION [dbo].[ProtocolResponseFind]
(
    @ActionDate datetime ,
    @PRID_Unique varchar(30)
)
RETURNS varchar(50)
AS
BEGIN
RETURN (
        SELECT TOP (1) ProtocolResponse
        FROM dbo.Criteria_Map_Protocol_Response
        WHERE PRID_Unique = @PRID_Unique AND
            ActionDate <= @ActionDate
        ORDER BY ActionDate DESC
)
END
0
 
WillOwnerAuthor Commented:
That worked with a little tweaking.  The sort needed to be on ActionDate.  Thank you for help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.