USP_REPORT_DASHBOARD_ACTIVEMEMBERSHIPS_BYDATE

        create procedure dbo.USP_REPORT_DASHBOARD_ACTIVEMEMBERSHIPS_BYDATE
        (
            @FROMDATE datetime = null,
            @TODATE datetime = null,
            @INTERVALTYPE tinyint = 1
        )
        as
          set nocount on;

          begin        
            declare @MINDATE datetime
            select @MINDATE = min(MEMBERSHIPTRANSACTION.TRANSACTIONDATE) from dbo.MEMBERSHIPTRANSACTION

            if @FROMDATE < @MINDATE
                set @FROMDATE = @MINDATE

            if @TODATE > getdate()
                set @TODATE = getdate()

            set @FROMDATE = dbo.UFN_DATE_GETEARLIESTTIME(@FROMDATE)
            set @TODATE = dbo.UFN_DATE_GETLATESTTIME(@TODATE)

            select
                DATES.[DATE] as [Date],
                ACTIVEMEMBERSHIPSASOF.MembershipCount
            from dbo.UFN_CALENDARDATES(@FROMDATE, @TODATE, @INTERVALTYPE) DATES
            outer apply (
                select count(M.ID) as MembershipCount
                from dbo.MEMBERSHIP M
                    inner join dbo.UFN_MEMBERSHIP_LATESTTRANSACTIONASOF_BULK(DATES.[DATE]) MT on M.ID = MT.MEMBERSHIPID
                    inner join dbo.MEMBERSHIPPROGRAM MP on M.MEMBERSHIPPROGRAMID = MP.ID
                    inner join dbo.MEMBERSHIPLEVEL ML on MT.MEMBERSHIPLEVELID = ML.ID
                    left join dbo.UFN_MEMBERSHIP_WITHRENEWALWINDOW_GETNOLONGERACTIVEDATES() NOLONGERACTIVEDATES
                        on MT.ID = NOLONGERACTIVEDATES.MEMBERSHIPTRANSACTIONID
                where MT.ACTIONCODE <> 4
                    and (MP.PROGRAMTYPECODE in (1, 2) or NOLONGERACTIVEDATES.NOLONGERACTIVEDATE >= [DATES].[DATE])
                    and M.STATUSCODE <> 2
            ) ACTIVEMEMBERSHIPSASOF
                order by DATES.[DATE]

            end