Hi I am trying to translate this SQL to a Built formula in Access.
can you help me?
CREATE FUNCTION: fnEarnedDays
---
CREATE FUNCTION [dbo].[fnEarnedDays]
(
@TranEffDt Date,
@TranTypeCd varchar(25),
@TermExpDt Date,
@EarningDt Date, --EOM
@BookedDt Date = null,
@CreateDt Date = null
)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @inForce int = 0
DECLARE @FOM DATE = DATEADD(mm, DATEDIFF(mm, 0, @EarningDt), 0)
SELECT @inForce =
CASE
When not @CreateDt is null and @CreateDt >= @TermExpDt
and datepart(month, @EarningDt) = datepart(month, @TermExpDt)
and datepart(year, @EarningDt) = datepart(year, @TermExpDt)
Then CONVERT(real, datediff(day, @TranEffDt, @TermExpDt))
When not @CreateDt is null and @CreateDt >= @TermExpDt
Then 0 --CONVERT(real, datediff(day, @TranEffDt, @TermExpDt))
When not @BookedDt is null and @EarningDt < @BookedDt
Then 0
--When @TranTypeCd = 'CN' and @TranEffDt > @FOM and @TranEffDt < @EarningDt
Then CONVERT(real, datediff(day, @FOM, dateadd(day, -1, @TranEffDt))) --(Removed this because of 2691969)
When not @BookedDt is null and @EarningDt = @BookedDt
Then CONVERT(real, datediff(day, @TranEffDt, dateadd(day, 1, @EarningDt)))
When @TranEffDt >= @FOM
Then CONVERT(real, datediff(day, @TranEffDt, dateadd(day, 1, @EarningDt)))
When @TermExpDt <= @EarningDt
Then CONVERT(real, datediff(day, @FOM, @TermExpDt))
else
CONVERT(real, datediff(day, @FOM, DATEADD(day, 1, @EarningDt)))
END
RETURN(@inForce)
END
GO