USP_BBDW_FACT_EVENTREGISTRANTTOTAL_COMMUNICATIONS

create procedure dbo.[USP_BBDW_FACT_EVENTREGISTRANTTOTAL_COMMUNICATIONS](
  @OPENWINDOW datetime, 
  @CLOSEWINDOW datetime
) as
  set nocount on;

  select 
    c.[ID] [CONSTITUENTSYSTEMID],       
    e.[ID] [EVENTSYSTEMID],
    count(*) [COMMUNICATIONSSENT],
    sum( fc.[PAGEVISITS] ) [PERSONALPAGEVIEWS]         
  from 
    [FAFCOMMUNICATIONSLOG] fc
      inner join 
    ( 
      select distinct 
        fc.[ClientUsersID] , 
        fc.[EVENTID] 
      from [FAFCOMMUNICATIONSLOG] fc (nolock) 
      where 
        (fc.[DATECHANGED] > @OPENWINDOW and fc.[DATECHANGED] <= @CLOSEWINDOW)
        or 
        (fc.[DATEADDED] > @OPENWINDOW and fc.[DATEADDED] <= @CLOSEWINDOW) 
    ) as ctc on fc.[EVENTID] = ctc.[EVENTID] and fc.[CLIENTUSERSID] = ctc.[CLIENTUSERSID]
    inner join dbo.[CLIENTUSERS] cu(nolock) on cu.[ID] = fc.[CLIENTUSERSID]
    inner join dbo.[BACKOFFICESYSTEMUSERS] bosu(nolock) on cu.[ID] = bosu.[CLIENTUSERSID] and cu.[DELETED] = 0
    inner join dbo.[BACKOFFICESYSTEMPEOPLE] bosp (nolock) on bosu.[BACKOFFICEPEOPLEID] = bosp.[ID] and bosu.[CURRENT] = 1
    inner join dbo.[CONSTITUENT] c (nolock) on bosp.[BACKOFFICERECORDID] = c.[SEQUENCEID] and bosp.[BACKOFFICESYSTEMID] = 0
    inner join dbo.[EVENT] e (nolock) on fc.[EVENTID] = e.[ID]
  group by c.[ID], e.[id];