USP_BBDW_FACT_EVENTREGISTRANT_USERVANITYURL

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

  with [PAGEIDS]
  as
  (  
    select 
        S1.Value [PAGEID],   
        EX.[EVENTID] 
    from 
      [EVENTEXTENSION] EX 
        left join [CMSSITESETTING] S1 on EX.[CLIENTSITESID] = S1.[CLIENTSITESID] and S1.[ENUMID] = 30      
        left join [VanityURL] v1 on v1.[RealmID] = cast(S1.[VALUE] as integer) and v1.[RealmTypeID] = 4
   ), VanityURLData as (
    select 
      r.[ID] [REGISTRANTSYSTEMID],
      r.[EVENTID] [EVENTSYSTEMID],
      case when VURL.[VanityURL] is null and V1.[VanityURL]  is null   
      then S3.[VALUE] + '?pid=' + pg.[PAGEID] + '&pt=' + cast(0 as nvarchar(2)) + '&aid=' +  cast(R.[ID] as nvarchar(36))   
      when VURL.[VanityURL] is null and V1.[VanityURL]  is not null  
      then S2.[VALUE] + V1.[VanityURL]  + '/?pt=' + cast(0 as nvarchar(2)) + '&aid=' +  cast(R.[ID] as nvarchar(36))   
      else S2.[VALUE] + VURL.[VanityURL] + '/' end as [PERSONALPAGEURL]  
      from [VanityURL] VURL (nolock)
      left join [FAFFRIENDLYURLPARAMS] FAFURL
         on  VURL.[RealmID] = FAFURL.[SEQUENCEID] and VURL.[RealmTypeID] = 7
      left join [REGISTRANT] r (nolock)
        on r.[ID] = FAFURL.[PARTICIPANTID] and [PAGETYPE] = 0  
      left join [EVENTEXTENSION] EX on EX.[EVENTID] = R.[EVENTID]  
      left join [PAGEIDS] pg on pg.[EVENTID] = r.[EVENTID]
      left join [CMSSITESETTING] S1 on EX.[CLIENTSITESID] = S1.[CLIENTSITESID] and S1.[ENUMID] = 30          
      left join [VANITYURL] V1 on V1.[RealmID] = CAST(S1.[VALUE] as Integer) and V1.[RealmTypeID] = 4
      left join [CMSSITESETTING] S2 on EX.[CLIENTSITESID] = S2.[CLIENTSITESID] and S2.[ENUMID] = 11  
      left join [CMSSITESETTING] S3 on EX.[CLIENTSITESID] = S3.[CLIENTSITESID] and S3.[ENUMID] = 10  
      where r.[ID] is not null
     and (vurl.[DATECHANGED] > @OPENWINDOW and vurl.[DATECHANGED] <= @CLOSEWINDOW  ) 
  ), DefaultURL as (
    select 
      r.[ID] [REGISTRANTSYSTEMID],
      r.[EVENTID] [EVENTSYSTEMID],
      S3.[VALUE] + '?pid=' + pg.[PAGEID] + '&pt=' + cast(0 as nvarchar(2)) + '&aid=' +  cast(R.[ID] as nvarchar(36)) as [PERSONALPAGEURL]
      from 
      [REGISTRANT]  r (nolock)
      left join [EVENTEXTENSION] ex on ex.[EVENTID] = r.[EVENTID]  
      left join [PAGEIDS] pg on pg.[EVENTID] = r.[EVENTID]
      left join [CMSSITESETTING] s3 on ex.[CLIENTSITESID] = s3.[CLIENTSITESID] and s3.[ENUMID] = 10  
      where r.[ID] is not null
      and (r.[DATECHANGED] > @OPENWINDOW and r.[DATECHANGED] <= @CLOSEWINDOW ) 
      and r.[ID] not in ( select [REGISTRANTSYSTEMID] from VanityURLData )
  ) 
  select 
    [REGISTRANTSYSTEMID],
    [EVENTSYSTEMID],
    isnull( [PERSONALPAGEURL] , '') [PERSONALPAGEURL] 
  from VanityURLData 
  union
  select 
    [REGISTRANTSYSTEMID],
    [EVENTSYSTEMID], isnull( 
    [PERSONALPAGEURL],'') [PERSONALPAGEURL]  
  from DefaultURL;