USP_DATALIST_FAFDASHBOARDCOMMUNICATIONS

            CREATE procedure dbo.USP_DATALIST_FAFDASHBOARDCOMMUNICATIONS
             (           
                @CLIENTUSERSID int,
                  @EVENTID uniqueidentifier,
                @TYPECODE Tinyint = 0, -- 0: Individual, 1:Team, 2:Company, 3:Household 4: Household and team member

                @DASHBOARDTYPE Tinyint = 0  -- 0:Donor, 1:Participant

              )
            as
                set nocount on;

              DECLARE @MYADDRESSBOOK table (
                          GROUPNAME nvarchar(100),
                CATEGORYSTATUS nvarchar(100),
                          ADDRESSBOOKID uniqueidentifier null,
                          NAME varchar(256) null,
                          CONSTITUENTID uniqueidentifier null,
                          EMAILADDRESS varchar(200) null,
                REGISTRANTID uniqueidentifier null
                          )

                DECLARE @MYLOG table (
                          GROUPNAME nvarchar(100),
                          ADDRESSBOOKID uniqueidentifier null,
                          CONSTITUENTID uniqueidentifier null,
                          PAGEVISITS int,
                NAME varchar(256) null
                          )

              insert into @MYADDRESSBOOK
              exec usp_datalist_fafaddressbookcontactsearch @CLIENTUSERSID,@EVENTID


              INSERT INTO @MYLOG
              SELECT distinct 
                AB.GROUPNAME,
                AB.ADDRESSBOOKID,
                AB.CONSTITUENTID,
                (CASE WHEN CL.PAGEVISITS IS NULL THEN -1 ELSE CL.PAGEVISITS END) as PAGEVISITS,
                AB.NAME
              from @MYADDRESSBOOK AB 
              LEFT OUTER JOIN dbo.FAFCOMMUNICATIONSLOG CL (NOLOCK)
              ON AB.EMAILADDRESS = CL.EMAILADDRESS
              AND CL.MESSAGETYPECODE = 1 AND CL.EVENTID = @EVENTID
              AND AB.ADDRESSBOOKID = CL.ADDRESSBOOKID
              WHERE AB.GROUPNAME = 'Contacts'

              IF @DASHBOARDTYPE = 0
              INSERT INTO @MYLOG
              SELECT distinct 
                AB.GROUPNAME,
                AB.ADDRESSBOOKID,
                AB.CONSTITUENTID,
                (CASE WHEN CL.PAGEVISITS IS NULL THEN -1 ELSE CL.PAGEVISITS END) as PAGEVISITS,
                AB.NAME
              from @MYADDRESSBOOK AB 
              LEFT OUTER JOIN dbo.FAFCOMMUNICATIONSLOG CL (NOLOCK)
              ON AB.EMAILADDRESS = CL.EMAILADDRESS
              AND CL.MESSAGETYPECODE = 1 AND CL.EVENTID = @EVENTID
              AND AB.CONSTITUENTID = CL.CONSTITUENTID
              WHERE AB.GROUPNAME = 'Donors - previous'

              IF @DASHBOARDTYPE = 1
              BEGIN

                IF @TYPECODE IN (1,4)
                INSERT INTO @MYLOG
                SELECT distinct 
                  AB.GROUPNAME,
                  AB.ADDRESSBOOKID,
                  AB.CONSTITUENTID,
                  (CASE WHEN CL.PAGEVISITS IS NULL THEN -1 ELSE CL.PAGEVISITS END) as PAGEVISITS,
                  AB.NAME
                from @MYADDRESSBOOK AB 
                LEFT OUTER JOIN dbo.FAFCOMMUNICATIONSLOG CL (NOLOCK)
                ON AB.EMAILADDRESS = CL.EMAILADDRESS
                AND CL.MESSAGETYPECODE = 1 AND CL.EVENTID = @EVENTID
                AND AB.CONSTITUENTID = CL.CONSTITUENTID
                WHERE AB.GROUPNAME = 'Team members - Previous'

                IF @TYPECODE IN (3,4)
                INSERT INTO @MYLOG
                SELECT distinct 
                  AB.GROUPNAME,
                  AB.ADDRESSBOOKID,
                  AB.CONSTITUENTID,
                  (CASE WHEN CL.PAGEVISITS IS NULL THEN -1 ELSE CL.PAGEVISITS END) as PAGEVISITS,
                  AB.NAME
                from @MYADDRESSBOOK AB 
                LEFT OUTER JOIN dbo.FAFCOMMUNICATIONSLOG CL (NOLOCK)
                ON AB.EMAILADDRESS = CL.EMAILADDRESS
                AND CL.MESSAGETYPECODE = 1 AND CL.EVENTID = @EVENTID
                AND AB.CONSTITUENTID = CL.CONSTITUENTID
                WHERE AB.GROUPNAME = 'Household members - Previous'

                IF @TYPECODE = 2
                INSERT INTO @MYLOG
                SELECT distinct 
                  AB.GROUPNAME,
                  AB.ADDRESSBOOKID,
                  AB.CONSTITUENTID,
                  (CASE WHEN CL.PAGEVISITS IS NULL THEN -1 ELSE CL.PAGEVISITS END) as PAGEVISITS,
                  AB.NAME
                from @MYADDRESSBOOK AB 
                LEFT OUTER JOIN dbo.FAFCOMMUNICATIONSLOG CL (NOLOCK)
                ON AB.EMAILADDRESS = CL.EMAILADDRESS
                AND CL.MESSAGETYPECODE = 1 AND CL.EVENTID = @EVENTID
                AND AB.CONSTITUENTID = CL.CONSTITUENTID
                WHERE AB.GROUPNAME IN ('Team Leaders - Previous','Household Leaders - Previous','Individuals - Previous')

              END

              SELECT 
                GROUPNAME,
                ADDRESSBOOKID,
                CONSTITUENTID,
                SUM(PAGEVISITS) as PAGEVISITS,
                NAME
              FROM  @MYLOG
              GROUP BY GROUPNAME,ADDRESSBOOKID,CONSTITUENTID,NAME