USP_DATALIST_MEMBERSHIP_NEWMEMBERSHIPSBYDATE

            CREATE procedure dbo.USP_DATALIST_MEMBERSHIP_NEWMEMBERSHIPSBYDATE
            as
                set nocount on;

                with MEMBERSHIPS_CTE as (
                    select 
                        count(M.ID) NEWMEMBERSHIPS,
                        null as RENEWALS,
                        MP.NAME PROGRAM        
                    from dbo.SALESORDERITEMMEMBERSHIP SOIM
                    inner join dbo.MEMBERSHIP M on M.ID = SOIM.MEMBERSHIPID
                    inner join dbo.MEMBERSHIPPROGRAM MP on M.MEMBERSHIPPROGRAMID = MP.ID
                    inner join dbo.MEMBERSHIPTRANSACTION MT on MT.ID = SOIM.MEMBERSHIPTRANSACTIONID
                    inner join dbo.SALESORDERITEM SOI on SOI.ID = SOIM.ID
                    inner join dbo.SALESORDER SO on SO.ID = SOI.SALESORDERID
                    where MT.ACTIONCODE = 0
                    group by MP.NAME

                    union all

                    select 
                        null as NEWMEMBERSHIPS,
                        count(M.ID) RENEWALS,
                        MP.NAME PROGRAM        
                    from dbo.SALESORDERITEMMEMBERSHIP SOIM
                    inner join dbo.MEMBERSHIP M on M.ID = SOIM.MEMBERSHIPID
                    inner join dbo.MEMBERSHIPPROGRAM MP on M.MEMBERSHIPPROGRAMID = MP.ID
                    inner join dbo.MEMBERSHIPTRANSACTION MT on MT.ID = SOIM.MEMBERSHIPTRANSACTIONID
                    inner join dbo.SALESORDERITEM SOI on SOI.ID = SOIM.ID
                    inner join dbo.SALESORDER SO on SO.ID = SOI.SALESORDERID
                    where MT.ACTIONCODE in (1,2,3)
                    group by MP.NAME
                )
                select 
                    coalesce(sum(NEWMEMBERSHIPS),0) NEWMEMBERSHIPS,
                    coalesce(sum(RENEWALS),0) RENEWALS,
                    PROGRAM
                from MEMBERSHIPS_CTE
                group by PROGRAM
                order by PROGRAM