vwEmailJobMembershipStats

CREATE VIEW [dbo].[vwEmailJobMembershipStats]
AS
    SELECT
        ej.[ID] As [EmailJobID],
        Count(mt.[ID]) As [MembershipTransactionsCount]
    FROM 
        [dbo].[EmailJob] ej
    INNER JOIN [dbo].[EmailJob_Recipient] ejr
        ON ej.[ID] = ejr.[EmailJobID]
    INNER JOIN [dbo].[MembershipTransactions] mt
        ON ejr.[ID] = mt.[EmailJobRecipientID]
    where mt.isdeleted = 0
    and mt.[Status] = 1
    GROUP BY ej.[ID]