spGetAppealListRecordCounts

    CREATE PROCEDURE [dbo].[spGetAppealListRecordCounts]
@AppealListID int,
@AppealListOrigin tinyint

as

DECLARE @TargetCount int
DECLARE @ExcludedCount int
DECLARE @InvalidAddressCount int
DECLARE @TotalUploaded float
DECLARE @EmailListID int
DECLARE @ListOrigin int
DECLARE @AppealID int

SELECT @EmailListID = el.[ID], @ListOrigin = el.Origin, @AppealID = epael.EmailProjectAppealID, @TotalUploaded = el.AcquiredRecordCount
FROM EmailProjectAppeal_EmailList epael inner join EmailList el on epael.[EmailListID] = el.[ID]
WHERE epael.[ID] = @AppealListID

IF @AppealListOrigin = 1
    BEGIN
        SET @ExcludedCount = (SELECT COUNT(ml.BB_XListRecordID) FROM dbo.fnGetExclusionEmailAddresses(@AppealID) ex inner join emaillistUploadMasterList ml
        on ex.EmailAddress = ml.EmailAddress WHERE ml.EmailListID = @EmailListID)

        SET @InvalidAddressCount = (SELECT COUNT(BB_XListRecordID) FROM EmailListUploadMasterList WHERE ISNULL(EmailAddress,'') = '' AND EmailListID = @EmailListID)
        SET @TargetCount = ISNULL(@TotalUploaded,0) - (IsNull(@ExcludedCount,0) + IsNull(@InvalidAddressCount,0))

        SELECT [ID], [Name], DataSourceID as DataSource, ISNULL(@TotalUploaded,'0') as TotalRecords, ISNULL(@TargetCount,'0') as Eligible, ISNULL(@InvalidAddressCount,'0') as MissingAddress, ISNULL(@ExcludedCount,'0') as Excluded  FROM EmailList WHERE [ID] = @EmailListID
        ORDER BY [ID], [Name], DataSourceID, TotalRecords, Eligible, MissingAddress, Excluded
    END
ELSE
    BEGIN
        SET @ExcludedCount = (SELECT COUNT(elp.ID) FROM dbo.fnGetExclusionEmailAddresses(@AppealID) ex inner join EmailList_People elp
        on ex.EmailAddress = elp.EmailAddress WHERE elp.EmailListID = @EmailListID)

        SET @InvalidAddressCount = (SELECT COUNT(ID) FROM EmailList_People WHERE ISNULL(EmailAddress,'') = '' AND EmailListID = @EmailListID)
        SET @TargetCount = ISNULL(@TotalUploaded,0) - (IsNull(@ExcludedCount,0) + IsNull(@InvalidAddressCount,0))

        SELECT [ID], [Name], DataSourceID as DataSource, ISNULL(@TotalUploaded,'0') as TotalRecords, ISNULL(@TargetCount,'0') as Eligible, ISNULL(@InvalidAddressCount,'0') as MissingAddress, ISNULL(@ExcludedCount,'0') as Excluded  FROM EmailList WHERE [ID] = @EmailListID
        ORDER BY [ID], [Name], DataSourceID, TotalRecords, Eligible, MissingAddress, Excluded
    END