USP_DATALIST_ADDRESSES_2

            create procedure dbo.USP_DATALIST_ADDRESSES_2 
            (
                @CONSTITUENTID uniqueidentifier, 
                @INCLUDEFORMER bit    = 0 
            )
            as
                set nocount on;

                with [CTE] as
                (
                    select 
                        ADDRESS.ID,
                        case when HISTORICALENDDATE is null
                            then coalesce(ADDRESSTYPECODE.DESCRIPTION, N'') + N' (Current) '
                            else coalesce(ADDRESSTYPECODE.DESCRIPTION, N'') + N' (Former) '
                        end as [DESCRIPTION],
                        case when ADDRESSCOORDINATES.ID is null or ADDRESSCOORDINATES.PENDINGGEOCODE = 1 or ADDRESSCOORDINATES.INVALIDGEOCODE = 1 then 0 else 1 end ISGEOCODED,
                        case when ADDRESSCOORDINATES.ID is null then 0 else ADDRESSCOORDINATES.PENDINGGEOCODE end PENDINGGEOCODE,
                        case when ADDRESSCOORDINATES.ID is null then 0 else ADDRESSCOORDINATES.INVALIDGEOCODE end INVALIDGEOCODE,
                        dbo.UFN_MAPPING_BUILDCONTEXTRECORDID(cast('645E6BE7-459C-402b-A03C-67587CA72B94' as uniqueidentifier),@CONSTITUENTID,ADDRESS.ID) as [MAPCONTEXTID],
                        ADDRESS.ISPRIMARY,
                        case when ADDRESS.HISTORICALENDDATE is null then 0 else 1 end as ISFORMER,
                        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
                    from dbo.ADDRESS
                    left join
                        dbo.ADDRESSTYPECODE
                    on
                        ADDRESS.ADDRESSTYPECODEID = ADDRESSTYPECODE.ID
                    left join
                        dbo.ADDRESSCOORDINATES
                    on
                        ADDRESSCOORDINATES.ADDRESSID = ADDRESS.ID
                    where
                        ADDRESS.CONSTITUENTID = @CONSTITUENTID
                        and (@INCLUDEFORMER = 1 or HISTORICALENDDATE is null)
                )
                select ID, DESCRIPTION, ISGEOCODED, PENDINGGEOCODE, INVALIDGEOCODE, MAPCONTEXTID, ISFORMER from [CTE]
                order by ISPRIMARY desc, ISFORMER asc, ENDDATE_SORT desc, STARTDATE_SORT desc, DATEADDED desc