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

IF EXISTS(SELECT 1 FROM   INFORMATION_SCHEMA.ROUTINES WHERE  ROUTINE_NAME = '_as_GetPeriodFromDate' AND SPECIFIC_SCHEMA = 'dbo')
      DROP FUNCTION _as_GetPeriodFromDate;
GO
-- =============================================
-- Author:      Asamco BV - Alex
-- Create date: 28-12-2020
-- Description: Get period based on date.
-- =============================================
CREATE FUNCTION [dbo].[_as_GetPeriodFromDate](@Date Date)
RETURNS INT
AS
BEGIN
    DECLARE @Period INT
    DECLARE @CommencementDate DateTime
    set @CommencementDate = (select top 1 dCommencementDate from Entities)
    SET @Period = COALESCE((SELECT MIN(idPeriod) FROM _etblPeriod WHERE dPeriodDate >= @Date AND @Date >= @CommencementDate),0)
  
    RETURN @Period
END