Database

where (SaleDateFrom <= ( select max(your date column) from your table with date value ) and (SaleDateTo '20010101' or SaleDateTo is null ))

create FUNCTION udf_GetLegalEntityAddress(@leId int)  
	returns @T table(PKAddressId int,
	StreetAddress1 varchar(75),
	StreetAddress2 varchar(75),
	City varchar(75),
	FK_County int,
	FK_State int,
	Zip1 int,
	Zip2 int,
	CreatedDate datetime,
	CreatedBy varchar(500),
	ModifiedDate datetime,
	ModifiedBy varchar(500),
	MigrationNotes varchar(50),
	FKProgramTypeLKPId int,
	OldSystemId int,
	ValidFlag bit)  
AS
begin
	DECLARE @AdminAddressTypeId int=0, @AddressId int=0, @effectivedate datetime, @PKLegalEntityAddressId int
	SET @AdminAddressTypeId = (select top 1 AddType.PKAddressTypeLKPId from AddressTypeLKP AddType where AddType.Description = 'Administrative')

	SET @AddressId = 
	(select top 1 FKAddressId from LegalEntityAddress where FKAddressTypeLKPId= @AdminAddressTypeId and FKLegalEntityId=@leId and IsValid = 1)

	if ((@AddressId is null) or (@AddressId=0))
	begin
		SET @AddressId = 
		(select top 1 FKAddressId from LegalEntityAddress where (EffectiveDateTo is null) and FKLegalEntityId=@leId and IsValid = 1)		
	end

	if ((@AddressId is null) or (@AddressId=0))
	begin
		set @effectivedate = (select max(EffectiveDateTo) from LegalEntityAddress where FKLegalEntityId=@leId and (IsValid = 1))
		if ((@effectivedate is null) or (@effectivedate<='1900-01-01'))
		begin
		 set @PKLegalEntityAddressId = (select max(PKLegalEntityAddressId) from LegalEntityAddress where (FKLegalEntityId=@leId) and (IsValid = 1))
		 SET @AddressId = 
		 (select top 1 FKAddressId from LegalEntityAddress where PKLegalEntityAddressId=@PKLegalEntityAddressId) 
		end
		else
		begin
		 SET @AddressId = 
		 (select top 1 FKAddressId from LegalEntityAddress where FKLegalEntityId=@leId and EffectiveDateTo=@effectivedate) 		 
		end
	end
	if ((@AddressId is null) or (@AddressId=0))
	begin
		SET @AddressId = 
		(select top 1 FKAddressId from LegalEntityAddress where FKLegalEntityId=@leId and IsValid = 1)
	end

		insert into @T (
		PKAddressId,
		StreetAddress1,
		StreetAddress2,
		City,
		FK_County,
		FK_State,
		Zip1,
		Zip2,
		CreatedDate,
		CreatedBy,
		ModifiedDate,
		ModifiedBy,
		MigrationNotes,
		FKProgramTypeLKPId,
		OldSystemId,
		ValidFlag)
		select top 1 
		PKAddressId,
		StreetAddress1,
		StreetAddress2,
		City,
		FK_County,
		FK_State,
		Zip1,
		Zip2,
		CreatedDate,
		CreatedBy,
		ModifiedDate,
		ModifiedBy,
		MigrationNotes,
		FKProgramTypeLKPId,
		OldSystemId,
		ValidFlag from [Address] where PKAddressId=@AddressId

		RETURN
end
go

SELECT LE.LegalEntityName
         ,LE.PKLegalEntityId
         ,LE.FederalTaxId
         ,LE.LegalEntityNbr
         ,PRG.Description as ProgramType
         ,LE.FKProgramTypeLKPId
         ,C.PK_Geographic_Location_Code + '-' + C.County_Name as CountyName
         ,LE.FKCountyLKPId
         ,AA.StreetAddress1
         , AA.City
         ,Admin_S.State_Name
         ,AA.Zip1
  FROM [dbo].[LegalEntity] LE
  inner join  [dbo].[ProgramTypeLKP] PRG on LE.FKProgramTypeLKPId = PRG.PKProgramTypeLKPId and PRG.Description = 'Mental Health'
  left outer Join [dbo].County C on LE.FKCountyLKPId = C.PKCountyId
  outer apply dbo.udf_GetLegalEntityAddress(LE.PKLegalEntityId) AS LEA
  left Outer JOIN  Address AA ON  LEA.PKAddressId = AA.PKAddressId
  Left outer JOIN County Admin_CY on Admin_CY.PKCountyId = AA.FK_County
  Left outer join State Admin_S on Admin_S.PKStateId = AA.FK_State
  where LE.IsCompleted = 1