USP_DATALIST_ADDRESSES_3

create procedure dbo.USP_DATALIST_ADDRESSES_3
(
    @CONSTITUENTID uniqueidentifier, 
    @INCLUDEFORMER bit    = 0
)
as
    set nocount on;
    declare @CURRENTDATE datetime = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

    with [CONTACTINFO_CTE] as
    (
        select 
            ADDRESS.ID,
            ADDRESS.DESCRIPTION,
            ADDRESS.CITY,
            [STATE].ABBREVIATION as [STATE],
            [COUNTRY].ABBREVIATION as [COUNTRY],
            ADDRESS.POSTCODE,
            dbo.UFN_ADDRESSTYPECODE_GETDESCRIPTION(ADDRESS.ADDRESSTYPECODEID) as ADDRESSTYPE,
            ADDRESS.ISPRIMARY,
            ADDRESS.DONOTMAIL,
            ADDRESS.ISCONFIDENTIAL,
            ADDRESS.HISTORICALSTARTDATE,
            ADDRESS.HISTORICALENDDATE,
            case when ADDRESS.HISTORICALSTARTDATE is null then '0001-01-01' else ADDRESS.HISTORICALSTARTDATE end as [STARTDATE_SORT],
            case when ADDRESS.HISTORICALENDDATE is null then '0001-01-01' else ADDRESS.HISTORICALENDDATE end as [ENDDATE_SORT],
            ADDRESS.DATEADDED,
            ADDRESSCOORDINATES.ID as ADDRESSCOORDINATESID,
            ADDRESSCOORDINATES.PENDINGGEOCODE,
            ADDRESSCOORDINATES.INVALIDGEOCODE
        from dbo.ADDRESS
            left join dbo.ADDRESSCOORDINATES on ADDRESSCOORDINATES.ADDRESSID = ADDRESS.ID 
            left join dbo.[STATE] on STATE.ID=ADDRESS.STATEID
            left join dbo.[COUNTRY] on [COUNTRY].ID=ADDRESS.COUNTRYID
        where ADDRESS.CONSTITUENTID = @CONSTITUENTID
    )

    select
        CONTACTINFO.ID,
        CONTACTINFO.DESCRIPTION as ADDRESS,
        CONTACTINFO.CITY,
        CONTACTINFO.STATE,
        CONTACTINFO.COUNTRY,
        CONTACTINFO.POSTCODE,
        case
        when ((CONTACTINFO.HISTORICALENDDATE is null) or (CONTACTINFO.HISTORICALENDDATE > @CURRENTDATE))
        then coalesce(CONTACTINFO.ADDRESSTYPE, N'') + N' (Current)'
        else
        coalesce(CONTACTINFO.ADDRESSTYPE, N'') + N' (Former)'
        end as TYPE,
        CONTACTINFO.ISPRIMARY,
        CONTACTINFO.DONOTMAIL as DONOTCONTACT,
        CONTACTINFO.ISCONFIDENTIAL,
        case when ((CONTACTINFO.HISTORICALENDDATE is null) or (CONTACTINFO.HISTORICALENDDATE > @CURRENTDATE)) then 0 else 1 end as ISFORMER,
        CONTACTINFO.HISTORICALSTARTDATE,
        CONTACTINFO.HISTORICALENDDATE,
        case when CONTACTINFO.ADDRESSCOORDINATESID is null or CONTACTINFO.PENDINGGEOCODE = 1 or CONTACTINFO.INVALIDGEOCODE = 1 then 0 else 1 end as ISGEOCODED,
        case when CONTACTINFO.ADDRESSCOORDINATESID is null then 0 else CONTACTINFO.PENDINGGEOCODE end as PENDINGGEOCODE,
        case when CONTACTINFO.ADDRESSCOORDINATESID is null then 0 else CONTACTINFO.INVALIDGEOCODE end as INVALIDGEOCODE
    from
        [CONTACTINFO_CTE] CONTACTINFO
    where
        @INCLUDEFORMER = 1 or ((CONTACTINFO.HISTORICALENDDATE is null) or (CONTACTINFO.HISTORICALENDDATE > @CURRENTDATE))
    order by
        ISPRIMARY desc, ISFORMER asc, ENDDATE_SORT desc, STARTDATE_SORT desc, DATEADDED desc