UFN_MEMBERSHIP_WASLAPSED_BETWEENTHESEDATES

CREATE FUNCTION dbo.[UFN_MEMBERSHIP_WASLAPSED_BETWEENTHESEDATES]
 (@MEMBERSHIPID uniqueidentifier, @ORIGINALMEMBERSHIPTRANSACTIONID uniqueidentifier, @FROMDATE datetime, @TODATE datetime, @ONLYCOUNTCHANGEDTOLAPSED bit)
RETURNS bit
WITH EXECUTE AS CALLER
AS
BEGIN

    declare @ORIGINALTRANSACTIONEXPIRATIONDATE datetime
    declare @TOTALGRACEPERIODINMONTHS tinyint = 0
    declare @ORIGINALTRANSACTIONEXTENDEDEXPIRATIONDATE datetime
    declare @STATUSCODEAFTERRENEWALWINDOW tinyint

select        @TOTALGRACEPERIODINMONTHS = RR.AFTEREXPIRATION,
            @STATUSCODEAFTERRENEWALWINDOW = RR.NONRENEWALACTIONTYPECODE
    from dbo.MEMBERSHIP M
    join dbo.MEMBERSHIPLEVEL ML
      on M.MEMBERSHIPLEVELID = ML.ID
    join dbo.MEMBERSHIPLEVELRENEWALRULES RR
      on ML.ID = RR.MEMBERSHIPLEVELID
    where M.ID = @MEMBERSHIPID

    select @ORIGINALTRANSACTIONEXPIRATIONDATE = EXPIRATIONDATE
    from dbo.[MEMBERSHIPTRANSACTION]
    where ID = @ORIGINALMEMBERSHIPTRANSACTIONID

    set @ORIGINALTRANSACTIONEXTENDEDEXPIRATIONDATE = dbo.[UFN_MEMBERSHIP_WITHRENEWALWINDOW_GETEXPIRATIONDATE](@MEMBERSHIPID, @ORIGINALTRANSACTIONEXPIRATIONDATE)
    set @TODATE =  dbo.UFN_DATE_GETLATESTTIME(@TODATE)

    declare @ORIGINALTRANSACTIONLASTRENEWALWINDOWDATE datetime
    set @ORIGINALTRANSACTIONLASTRENEWALWINDOWDATE = DATEADD(month, @TOTALGRACEPERIODINMONTHS, @ORIGINALTRANSACTIONEXPIRATIONDATE)
    set @ORIGINALTRANSACTIONLASTRENEWALWINDOWDATE = dbo.UFN_DATE_GETEARLIESTTIME(@ORIGINALTRANSACTIONLASTRENEWALWINDOWDATE)

    declare @RENEWTRANSACTIONDATE datetime = null
    set @RENEWTRANSACTIONDATE = dbo.[UFN_MEMBERSHIP_RENEWTRANSACTIONEXISTS_BEFORETHISDATE](@MEMBERSHIPID, @ORIGINALMEMBERSHIPTRANSACTIONID, @ORIGINALTRANSACTIONEXPIRATIONDATE, @ORIGINALTRANSACTIONEXTENDEDEXPIRATIONDATE, @FROMDATE, @TODATE)    

    declare @AFTERRENEWALWINDOWANDSTATUSNOTLAPSED bit = 0
    if (@RENEWTRANSACTIONDATE is null and @ORIGINALTRANSACTIONLASTRENEWALWINDOWDATE <= @FROMDATE and @STATUSCODEAFTERRENEWALWINDOW != 4)
    begin
        set @AFTERRENEWALWINDOWANDSTATUSNOTLAPSED = 1
    end

    declare @NORENEWALWINDOWANDSTATUSNOTLAPSED bit = 0
    if @TOTALGRACEPERIODINMONTHS = 0 and @STATUSCODEAFTERRENEWALWINDOW != 4
    begin
        set @NORENEWALWINDOWANDSTATUSNOTLAPSED = 1
    end

    --Only counts a lapsed membership if it switches to lapsed during the time frame, not if it switched to lapsed in a previous time frame and is now lapsed

    if @ONLYCOUNTCHANGEDTOLAPSED = 1
    begin
        declare @CURRENTDATE datetime = getdate()
        if @ORIGINALTRANSACTIONEXTENDEDEXPIRATIONDATE between @FROMDATE and @TODATE
        and @CURRENTDATE > @ORIGINALTRANSACTIONEXTENDEDEXPIRATIONDATE
        and (@RENEWTRANSACTIONDATE is null or not (@RENEWTRANSACTIONDATE between @FROMDATE and @TODATE))
        begin
            return 1
        end
    end
    else
    begin
        if @ORIGINALTRANSACTIONEXTENDEDEXPIRATIONDATE < @TODATE
        and @RENEWTRANSACTIONDATE is null
        and @AFTERRENEWALWINDOWANDSTATUSNOTLAPSED = 0
        and @NORENEWALWINDOWANDSTATUSNOTLAPSED = 0
        begin
            return 1
        end
    end

    return 0
END