UFN_CONSTITUENT_GETPHONECHANGES

            CREATE function dbo.UFN_CONSTITUENT_GETPHONECHANGES()
            returns table
            as 
            return

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

                    select
                        ID,
                        NUMBER DISPLAY,
                        DATEADDED,
                        DATECHANGED,
                        null AUDITID
                    from dbo.PHONE
                    union all
                    -- terminal delete

                    select
                        AUDITRECORDID,
                        NUMBER,
                        DATEADDED,
                        null,
                        AUDITID
                    from dbo.PHONEAUDIT
                    where AUDITTYPECODE = 2
                    -- not re-added

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

                    and not exists(select 'x' from dbo.PHONEAUDIT LATER where LATER.AUDITRECORDID = PHONEAUDIT.AUDITRECORDID and LATER.AUDITDATE > PHONEAUDIT.AUDITDATE)
                )
                select 
                    PHONE.ID RECORDID,
                    cast(null as uniqueidentifier) as AUDITID,
                    cast(null as uniqueidentifier) as AUDITKEY,
                    PHONE.CONSTITUENTID, 
                    0 EDITTYPECODE,
                    PHONE.ADDEDBYID CHANGEAGENTID,
                    PHONE.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.PHONE
                inner join [CURRENT] on [CURRENT].ID = PHONE.ID
                left join dbo.CONSTITUENTDATAREVIEWROLLBACK on CONSTITUENTDATAREVIEWROLLBACK.CONTEXTRECORDID = PHONE.ID and CONSTITUENTDATAREVIEWROLLBACK.ROLLBACKAUDITID is null
                left join dbo.CONSTITUENTDATAREVIEWROLLBACKREASON on CONSTITUENTDATAREVIEWROLLBACKREASON.ID = CONSTITUENTDATAREVIEWROLLBACK.CONSTITUENTDATAREVIEWROLLBACKREASONID
                left join dbo.PHONEAUDIT [ROLLBACK] on [ROLLBACK].AUDITID = CONSTITUENTDATAREVIEWROLLBACK.SOURCEAUDITID
                where PHONE.DATEADDED = PHONE.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.PHONEAUDIT BEFORE
                inner join [CURRENT] on [CURRENT].ID = BEFORE.AUDITRECORDID
                inner join dbo.PHONEAUDIT 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.PHONEAUDIT [ROLLBACK] on [ROLLBACK].AUDITID = CONSTITUENTDATAREVIEWROLLBACK.SOURCEAUDITID
                where BEFORE.AUDITTYPECODE = 0
                union all
                select 
                    PHONEAUDIT.AUDITRECORDID RECORDID,
                    PHONEAUDIT.AUDITID,
                    PHONEAUDIT.AUDITKEY,
                    PHONEAUDIT.CONSTITUENTID, 
                    2 EDITTYPECODE,
                    PHONEAUDIT.AUDITCHANGEAGENTID CHANGEAGENTID,
                    PHONEAUDIT.AUDITDATE CHANGEDATE,
                    [CURRENT].DISPLAY,
                    case when PHONEAUDIT.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.PHONEAUDIT
                inner join [CURRENT] on [CURRENT].ID = PHONEAUDIT.AUDITRECORDID
                left join dbo.CONSTITUENTDATAREVIEWROLLBACK on CONSTITUENTDATAREVIEWROLLBACK.ROLLBACKAUDITID = PHONEAUDIT.AUDITID and CONSTITUENTDATAREVIEWROLLBACK.SOURCEAUDITID is null
                left join dbo.CONSTITUENTDATAREVIEWROLLBACKREASON on CONSTITUENTDATAREVIEWROLLBACKREASON.ID = CONSTITUENTDATAREVIEWROLLBACK.CONSTITUENTDATAREVIEWROLLBACKREASONID
                where AUDITTYPECODE = 2
                union all
                select 
                    PHONEAUDIT.AUDITRECORDID RECORDID,
                    PHONEAUDIT.AUDITID,
                    PHONEAUDIT.AUDITKEY,
                    PHONEAUDIT.CONSTITUENTID, 
                    100 EDITTYPECODE,
                    PHONEAUDIT.ADDEDBYID CHANGEAGENTID,
                    PHONEAUDIT.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.PHONEAUDIT
                inner join [CURRENT] on [CURRENT].ID = PHONEAUDIT.AUDITRECORDID
                left join dbo.CONSTITUENTDATAREVIEWROLLBACK on CONSTITUENTDATAREVIEWROLLBACK.ROLLBACKAUDITID = PHONEAUDIT.AUDITID and CONSTITUENTDATAREVIEWROLLBACK.SOURCEAUDITID is not null
                left join dbo.CONSTITUENTDATAREVIEWROLLBACK [ROLLBACKAUDITIDNULL] on [ROLLBACKAUDITIDNULL].CONTEXTRECORDID = PHONEAUDIT.AUDITRECORDID and [ROLLBACKAUDITIDNULL].ROLLBACKAUDITID is null and PHONEAUDIT.DATEADDED = [CURRENT].DATEADDED
                left join dbo.CONSTITUENTDATAREVIEWROLLBACKREASON on CONSTITUENTDATAREVIEWROLLBACKREASON.ID = isnull(CONSTITUENTDATAREVIEWROLLBACK.CONSTITUENTDATAREVIEWROLLBACKREASONID,[ROLLBACKAUDITIDNULL].CONSTITUENTDATAREVIEWROLLBACKREASONID)
                left join dbo.PHONEAUDIT [ROLLBACK] on [ROLLBACK].AUDITID = isnull(CONSTITUENTDATAREVIEWROLLBACK.SOURCEAUDITID,[ROLLBACKAUDITIDNULL].SOURCEAUDITID)
                where PHONEAUDIT.DATEADDED = PHONEAUDIT.DATECHANGED