UFN_CONSTITUENT_GETADDRESSCHANGES

            CREATE function dbo.UFN_CONSTITUENT_GETADDRESSCHANGES()
            returns table
            as 
            return

            /* EDITTYPECODE  
                0 = Add
                1 = Edit
                2 = Delete
                100 = Original Add (after edited)
            */
                with [CURRENT] as
                (
                    -- current row

                    select
                        ID,
                        replace(replace(DESCRIPTION, char(10), ' '), char(13), '') DISPLAY,
                        DATEADDED,
                        DATECHANGED,
                        null AUDITID
                    from dbo.ADDRESS
                    union all
                    -- terminal delete

                    select
                        AUDITRECORDID,
                        replace(replace(dbo.UFN_BUILDFULLADDRESS(null, ADDRESSBLOCK, CITY, STATEID, POSTCODE, COUNTRYID), char(10), ' '), char(13), ''),
                        DATEADDED,
                        null,
                        AUDITID
                    from dbo.ADDRESSAUDIT
                    where AUDITTYPECODE = 2
                    -- not re-added

                    and not exists(select 'x' from dbo.ADDRESS where ADDRESS.ID = ADDRESSAUDIT.AUDITRECORDID)
                    -- not deleted again after re-added

                    and not exists(select 'x' from dbo.ADDRESSAUDIT LATER where LATER.AUDITRECORDID = ADDRESSAUDIT.AUDITRECORDID and LATER.AUDITDATE > ADDRESSAUDIT.AUDITDATE)
                )
                select 
                    ADDRESS.ID RECORDID,
                    cast(null as uniqueidentifier) as AUDITID,
                    cast(null as uniqueidentifier) as AUDITKEY,
                    ADDRESS.CONSTITUENTID, 
                    0 EDITTYPECODE,
                    ADDRESS.ADDEDBYID CHANGEAGENTID,
                    ADDRESS.DATEADDED CHANGEDATE,
                    [CURRENT].DISPLAY,
                    1 LASTCHANGE,
                    1 ROWEXISTS,
                    case when [ROLLBACK].DATEADDED = [ROLLBACK].DATECHANGED then [ROLLBACK].DATEADDED else [ROLLBACK].AUDITDATE end ROLLBACKTODATE,
                    case when CONSTITUENTDATAREVIEWROLLBACK.ID is not null then CONSTITUENTDATAREVIEWROLLBACKREASON.CODE + ' - ' + CONSTITUENTDATAREVIEWROLLBACKREASON.DESCRIPTION end ROLLBACKREASON
                from dbo.ADDRESS
                inner join [CURRENT] on [CURRENT].ID = ADDRESS.ID
                left join dbo.CONSTITUENTDATAREVIEWROLLBACK on CONSTITUENTDATAREVIEWROLLBACK.CONTEXTRECORDID = ADDRESS.ID and CONSTITUENTDATAREVIEWROLLBACK.ROLLBACKAUDITID is null
                left join dbo.CONSTITUENTDATAREVIEWROLLBACKREASON on CONSTITUENTDATAREVIEWROLLBACKREASON.ID = CONSTITUENTDATAREVIEWROLLBACK.CONSTITUENTDATAREVIEWROLLBACKREASONID
                left join dbo.ADDRESSAUDIT [ROLLBACK] on [ROLLBACK].AUDITID = CONSTITUENTDATAREVIEWROLLBACK.SOURCEAUDITID
                where ADDRESS.DATEADDED = ADDRESS.DATECHANGED
                union all
                select 
                    BEFORE.AUDITRECORDID RECORDID,
                    BEFORE.AUDITID,
                    BEFORE.AUDITKEY,
                    BEFORE.CONSTITUENTID, 
                    1 EDITTYPECODE,
                    BEFORE.AUDITCHANGEAGENTID CHANGEAGENTID,
                    BEFORE.AUDITDATE CHANGEDATE,
                    [CURRENT].DISPLAY,
                    case when AFTER.DATECHANGED = [CURRENT].DATECHANGED then 1 else 0 end LASTCHANGE,
                    case when [CURRENT].DATECHANGED is not null then 1 else 0 end ROWEXISTS,
                    case when [ROLLBACK].DATEADDED = [ROLLBACK].DATECHANGED then [ROLLBACK].DATEADDED else [ROLLBACK].AUDITDATE end ROLLBACKTODATE,
                    case when CONSTITUENTDATAREVIEWROLLBACK.ID is not null then CONSTITUENTDATAREVIEWROLLBACKREASON.CODE + ' - ' + CONSTITUENTDATAREVIEWROLLBACKREASON.DESCRIPTION end ROLLBACKREASON
                from dbo.ADDRESSAUDIT BEFORE
                inner join [CURRENT] on [CURRENT].ID = BEFORE.AUDITRECORDID
                inner join dbo.ADDRESSAUDIT AFTER on AFTER.AUDITKEY = BEFORE.AUDITKEY and AFTER.AUDITRECORDID = BEFORE.AUDITRECORDID and AFTER.AUDITTYPECODE = 1
                left join dbo.CONSTITUENTDATAREVIEWROLLBACK on CONSTITUENTDATAREVIEWROLLBACK.ROLLBACKAUDITID = AFTER.AUDITID
                left join dbo.CONSTITUENTDATAREVIEWROLLBACKREASON on CONSTITUENTDATAREVIEWROLLBACKREASON.ID = CONSTITUENTDATAREVIEWROLLBACK.CONSTITUENTDATAREVIEWROLLBACKREASONID
                left join dbo.ADDRESSAUDIT [ROLLBACK] on [ROLLBACK].AUDITID = CONSTITUENTDATAREVIEWROLLBACK.SOURCEAUDITID
                where BEFORE.AUDITTYPECODE = 0
                union all
                select 
                    ADDRESSAUDIT.AUDITRECORDID RECORDID,
                    ADDRESSAUDIT.AUDITID,
                    ADDRESSAUDIT.AUDITKEY,
                    ADDRESSAUDIT.CONSTITUENTID, 
                    2 EDITTYPECODE,
                    ADDRESSAUDIT.AUDITCHANGEAGENTID CHANGEAGENTID,
                    ADDRESSAUDIT.AUDITDATE CHANGEDATE,
                    [CURRENT].DISPLAY,
                    case when ADDRESSAUDIT.AUDITID = [CURRENT].AUDITID then 1 else 0 end LASTCHANGE,
                    case when [CURRENT].DATECHANGED is not null then 1 else 0 end ROWEXISTS,
                    null,
                    case when CONSTITUENTDATAREVIEWROLLBACK.ID is not null then CONSTITUENTDATAREVIEWROLLBACKREASON.CODE + ' - ' + CONSTITUENTDATAREVIEWROLLBACKREASON.DESCRIPTION end ROLLBACKREASON
                from dbo.ADDRESSAUDIT
                inner join [CURRENT] on [CURRENT].ID = ADDRESSAUDIT.AUDITRECORDID
                left join dbo.CONSTITUENTDATAREVIEWROLLBACK on CONSTITUENTDATAREVIEWROLLBACK.ROLLBACKAUDITID = ADDRESSAUDIT.AUDITID and CONSTITUENTDATAREVIEWROLLBACK.SOURCEAUDITID is null
                left join dbo.CONSTITUENTDATAREVIEWROLLBACKREASON on CONSTITUENTDATAREVIEWROLLBACKREASON.ID = CONSTITUENTDATAREVIEWROLLBACK.CONSTITUENTDATAREVIEWROLLBACKREASONID
                where AUDITTYPECODE = 2
                union all
                select 
                    ADDRESSAUDIT.AUDITRECORDID RECORDID,
                    ADDRESSAUDIT.AUDITID,
                    ADDRESSAUDIT.AUDITKEY,
                    ADDRESSAUDIT.CONSTITUENTID, 
                    100 EDITTYPECODE,
                    ADDRESSAUDIT.ADDEDBYID CHANGEAGENTID,
                    ADDRESSAUDIT.DATEADDED CHANGEDATE,
                    [CURRENT].DISPLAY,
                    0 LASTCHANGE,
                    case when [CURRENT].DATECHANGED is not null then 1 else 0 end ROWEXISTS,
                    case when [ROLLBACK].DATEADDED = [ROLLBACK].DATECHANGED then [ROLLBACK].DATEADDED else [ROLLBACK].AUDITDATE end ROLLBACKTODATE,
                    case when isnull(CONSTITUENTDATAREVIEWROLLBACK.ID,[ROLLBACKAUDITIDNULL].ID) is not null then CONSTITUENTDATAREVIEWROLLBACKREASON.CODE + ' - ' + CONSTITUENTDATAREVIEWROLLBACKREASON.DESCRIPTION end ROLLBACKREASON
                from dbo.ADDRESSAUDIT
                inner join [CURRENT] on [CURRENT].ID = ADDRESSAUDIT.AUDITRECORDID
                left join dbo.CONSTITUENTDATAREVIEWROLLBACK on CONSTITUENTDATAREVIEWROLLBACK.ROLLBACKAUDITID = ADDRESSAUDIT.AUDITID and CONSTITUENTDATAREVIEWROLLBACK.SOURCEAUDITID is not null
                left join dbo.CONSTITUENTDATAREVIEWROLLBACK [ROLLBACKAUDITIDNULL] on [ROLLBACKAUDITIDNULL].CONTEXTRECORDID = ADDRESSAUDIT.AUDITRECORDID and [ROLLBACKAUDITIDNULL].ROLLBACKAUDITID is null and ADDRESSAUDIT.DATEADDED = [CURRENT].DATEADDED
                left join dbo.CONSTITUENTDATAREVIEWROLLBACKREASON on CONSTITUENTDATAREVIEWROLLBACKREASON.ID = isnull(CONSTITUENTDATAREVIEWROLLBACK.CONSTITUENTDATAREVIEWROLLBACKREASONID,[ROLLBACKAUDITIDNULL].CONSTITUENTDATAREVIEWROLLBACKREASONID)
                left join dbo.ADDRESSAUDIT [ROLLBACK] on [ROLLBACK].AUDITID = isnull(CONSTITUENTDATAREVIEWROLLBACK.SOURCEAUDITID,[ROLLBACKAUDITIDNULL].SOURCEAUDITID)
                where ADDRESSAUDIT.DATEADDED = ADDRESSAUDIT.DATECHANGED