UFN_WEALTHPOINT_CONSTITUENTSEARCHADDRESSES

CREATE function [dbo].[UFN_WEALTHPOINT_CONSTITUENTSEARCHADDRESSES](
  @CONSTITUENTID uniqueidentifier
)
returns table
as
return
(
    with 
    CONFIG_CTE as
    (
      select 
            coalesce((select top 1 NUMADDRESSES from dbo.WEALTHPOINTCONFIGURATION), 5) as NUMADDRESSES
    ),
    CONSTITADDRESSLIST_CTE as
    (
    select 
          ID, 
          CONSTITUENTID,
          ISPRIMARY,
          HISTORICALENDDATE,
          DATEADDED
    from dbo.ADDRESS
    where 
      ADDRESS.CONSTITUENTID = @CONSTITUENTID
    ),
    CONSTITADDRESS_CTE as
    (
    select 
          ID, 
          CONSTITUENTID,
          row_number() over(partition by CONSTITUENTID order by ISPRIMARY desc, DATEADDED desc) as SEQUENCE
    from CONSTITADDRESSLIST_CTE
    where 
      HISTORICALENDDATE is null or HISTORICALENDDATE > getdate()
    )
    select ID, 
           SEQUENCE 
    from CONSTITADDRESS_CTE
    cross apply CONFIG_CTE
    where
    SEQUENCE <= CONFIG_CTE.NUMADDRESSES
)