UFN_EXCHANGECONTACTSYNC_GETCONSTITUENTADDRESSES

            CREATE function dbo.UFN_EXCHANGECONTACTSYNC_GETCONSTITUENTADDRESSES
            (
                @CONSTITUENTID uniqueidentifier
            )
            returns table
            as 
            return
            (
                SELECT 
                    coalesce(RANKEDADDRESS.ADDRESSBLOCK,'') as ADDRESSBLOCK,
                    coalesce(RANKEDADDRESS.CITY,'') as CITY,
                    coalesce(dbo.UFN_STATE_GETABBREVIATION(RANKEDADDRESS.STATEID),'') as STATE,
                    coalesce(RANKEDADDRESS.POSTCODE,'') as POSTCODE,
                    coalesce(dbo.UFN_COUNTRY_GETDESCRIPTION(RANKEDADDRESS.COUNTRYID),'') as COUNTRY,
                    dbo.UFN_ADDRESSTYPECODE_GETDESCRIPTION(RANKEDADDRESS.ADDRESSTYPECODEID) as ADDRESSTYPE,
                    EXCHANGEADDRESSMAPPING.OUTLOOKADDRESSTYPECODE,
                    RANKEDADDRESS.ID,
                    RANKEDADDRESS.ISPRIMARY
                from 
                    (select 
                        RANK() OVER (PARTITION BY ADDRESSTYPECODEID order by DATEADDED) as SEQUENCE, 
                        ADDRESSBLOCK, 
                        CITY, 
                        STATEID, 
                        POSTCODE, 
                        COUNTRYID, 
                        ADDRESSTYPECODEID,
                        ID,
                        ISPRIMARY
                    from 
                        dbo.ADDRESS 
                    where 
                        CONSTITUENTID = @CONSTITUENTID and
                        ADDRESS.HISTORICALENDDATE is null --Ignore all former addresses


                    ) as RANKEDADDRESS
                    inner join dbo.EXCHANGEADDRESSMAPPING on EXCHANGEADDRESSMAPPING.ADDRESSTYPECODEID = RANKEDADDRESS.ADDRESSTYPECODEID
                where 
                    RANKEDADDRESS.SEQUENCE = 1
            );