SQL - Get Period From Date (_as_GetPeriodFromDate)

create or ALTER FUNCTION [dbo].[_as_GetPeriodFromDate](@TxDate DATE)
RETURNS INT 
AS
BEGIN
/* Usage:
 SELECT dbo.[_as_GetPeriodFromDate]('2021-12-05')

*/

--declare @TxDate date =  '2021-12-05' -- getdate()
DECLARE @ret int

;WITH x as (
SELECT P1.idPeriod
			,COALESCE(DATEADD(DAY,1,P2.dPeriodDate)
			,(SELECT TOP 1 dCommencementDate FROM Entities E WHERE dCommencementDate IS NOT NULL ORDER BY idEntities DESC)) as FromDate
			,P1.dPeriodDate as EndDate
			,P1.bBlocked
		FROM _etblPeriod P1 LEFT JOIN _etblPeriod P2 ON P1.idPeriod -1 = P2.idPeriod
		
	) --P ON CAST(@TxDate as date) BETWEEN P.FromDate AND P.EndDate
SELECT @ret = COALESCE((select idPeriod from x P where @TxDate BETWEEN P.FromDate AND P.EndDate),0)

RETURN @ret

END