I have the following function in place but it doesn't seem to be working in all cases. The function is supposed to return the Students effective primary insurance at the time of a therapy transaction. The business rules in place are only one primary insurance can be effective at a time. In this case the Therapy Transaction Date is 2/28/2014 and there is one insurance policy that has an effective date of 1/1/2008 and a termination date of 2/28/2014 and the type is primary. Any suggestions on what I'm missing here?

TIA
Jeff

USE [SMS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Jeff Hibbs
-- Create date: March 23, 2010
-- Description:	Returns Current Current Primary Insurance
-- =============================================
CREATE FUNCTION [dbo].[GetPrimaryInsurance] 

(-- Student Id to be passed into function, as Student
 -- can only have one primary at any given time.
 @tStudentID Char(36),
 @tDate datetime
)

RETURNS varchar(50)
AS

BEGIN
	
	DECLARE  @tcCompany_Name varchar(50)		

	-- Get Current Term
	SET @tcCompany_Name = (select cCompany_Name from student_insurance where cStudent_cId = @tStudentID AND
 deffective <= @tDate and (dtermination > @tDate or dtermination is NULL) and ctype = 'Primary')  
	
    RETURN @tcCompany_Name

END