| Code Block | ||
|---|---|---|
| ||
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 |
| Code Block | ||||
|---|---|---|---|---|
| ||||
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 |