USP_BBDW_FACT_EVENTREGISTRANT_REGISTRANTFEES

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

  with RegistrantMembers as
  (
      select R.[ID] [ID]
      from dbo.[REGISTRANT] r (nolock)
      left join [EVENTREGISTRANTPAYMENT] evp (nolock) on r.[ID] = evp.[REGISTRANTID] 
      left join [REGISTRANTREGISTRATION] rr (nolock) on r.[ID] = rr.[ID]
      left join [EVENTPRICE]  ep     (nolock) on ep.ID = rr.ID 
      where  (r.[DATECHANGED] > @OPENWINDOW and r.[DATECHANGED] <= @CLOSEWINDOW  )  or
      (rr.[DATECHANGED] > @OPENWINDOW and rr.[DATECHANGED] <= @CLOSEWINDOW  )  or
      (ep.[DATECHANGED] > @OPENWINDOW and ep.[DATECHANGED] <= @CLOSEWINDOW  )  or
      (evp.[DATECHANGED] > @OPENWINDOW and evp.[DATECHANGED] <= @CLOSEWINDOW  )  
  ),
   RegPayment as
  (
      select  sum(evp.AMOUNT) [REGISTRATIONFEERECEIVED], evp.[REGISTRANTID] 
      from [EVENTREGISTRANTPAYMENT]  evp (nolock)    
      where 
        evp.[REGISTRANTID] in 
        (
            select [ID] FROM RegistrantMembers 
        )
      group by evp.[REGISTRANTID]  
  )
  ,
  RegFee  as
  (
      select  sum(rr.AMOUNT) [REGISTRATIONFEE], min(cast(ep.id as binary(16)))[EVENTPRICESYSTEMID], rr.[REGISTRANTID]
      from [REGISTRANTREGISTRATION]  rr
      inner join dbo.[EVENTPRICE] ep (nolock) on rr.[EVENTPRICEID] = ep.[ID]
      where  rr.REGISTRANTID in 
        (
            select [ID] FROM RegistrantMembers 
        )
      group by rr.[REGISTRANTID]  
  ) 

  select 
      rf.[REGISTRANTID] [EVENTREGISTRANTSYSTEMID], 
      c.[ID] [CONSTITUENTSYTEMID],
      r.[EVENTID] [EVENTSYSTEMID],
      isnull( rf.[REGISTRATIONFEE] ,0)[REGISTRATIONFEE],
      isnull( rp.[REGISTRATIONFEERECEIVED],0) [REGISTRATIONFEERECEIVED],
      rf.[EVENTPRICESYSTEMID] 
  from RegFee rf left join RegPayment rp 
  on rp.Registrantid = rf.[REGISTRANTID]
  inner join [REGISTRANT] r on r.ID = rf.[REGISTRANTID]
  inner join [CONSTITUENT] c (nolock)    on c.ID = r.[CONSTITUENTID];