vwEmailJobEventStats

CREATE VIEW [dbo].[vwEmailJobEventStats]
AS
    SELECT
        ej.[ID] As [EmailJobID],
        Count(et.[EventTransactionsID]) As [EventTransactionsCount]
    FROM 
        [dbo].[EmailJob] ej
    INNER JOIN [dbo].[EmailJob_Recipient] ejr
        ON ej.[ID] = ejr.[EmailJobID]
    INNER JOIN [dbo].[EventTransactions] et
        ON ejr.[ID] = et.[EmailJobRecipientID]
    where et.isdeleted = 0
    and et.[Status] = 1
    GROUP BY ej.[ID]