vwEmailJobSignupStats

CREATE VIEW [dbo].[vwEmailJobSignupStats]
AS
    SELECT
        ej.[ID] As [EmailJobID],
        Count(st.[SignupTransactionsID]) As [SignupTransactionsCount]
    FROM 
        [dbo].[EmailJob] ej
    INNER JOIN [dbo].[EmailJob_Recipient] ejr
        ON ej.[ID] = ejr.[EmailJobID]
    INNER JOIN [dbo].[SignupTransactions] st
        ON ejr.[ID] = st.[EmailJobRecipientID]
    where st.isdeleted = 0
    GROUP BY ej.[ID]