UFN_CONSTITUENT_GETEMAILCHANGES

            CREATE function dbo.UFN_CONSTITUENT_GETEMAILCHANGES()
            returns table
            as 
            return

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

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

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

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

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