USP_DATALIST_ORGANIZATIONMERGERHISTORY

CREATE procedure dbo.USP_DATALIST_ORGANIZATIONMERGERHISTORY(@CONTEXTID uniqueidentifier)
as
    set nocount on;

    with ORGMERGERS (ID, SOURCEORG1ID, SOURCEORG2ID, NEWORGID, MERGERDATE)
    as
    (
        --Anchor member definition

        select 
            ORGANIZATIONMERGERHISTORY.ID,
            ORGANIZATIONMERGERHISTORY.SOURCEORG1ID,
            ORGANIZATIONMERGERHISTORY.SOURCEORG2ID,
            ORGANIZATIONMERGERHISTORY.NEWORGID,
            ORGANIZATIONMERGERHISTORY.MERGERDATE
        from dbo.ORGANIZATIONMERGERHISTORY
        where ORGANIZATIONMERGERHISTORY.SOURCEORG1ID = @CONTEXTID
            or ORGANIZATIONMERGERHISTORY.SOURCEORG2ID = @CONTEXTID
            or ORGANIZATIONMERGERHISTORY.NEWORGID = @CONTEXTID

        union all

        --Recursive member definition

        select 
            ORGANIZATIONMERGERHISTORY.ID,
            ORGANIZATIONMERGERHISTORY.SOURCEORG1ID,
            ORGANIZATIONMERGERHISTORY.SOURCEORG2ID,
            ORGANIZATIONMERGERHISTORY.NEWORGID,
            ORGANIZATIONMERGERHISTORY.MERGERDATE
        from dbo.ORGANIZATIONMERGERHISTORY
            inner join ORGMERGERS om
                on (ORGANIZATIONMERGERHISTORY.NEWORGID = om.SOURCEORG1ID or ORGANIZATIONMERGERHISTORY.NEWORGID = om.SOURCEORG2ID) 
                    and ORGANIZATIONMERGERHISTORY.MERGERDATE < om.MERGERDATE
    )

    --Statement that executes the CTE

    select distinct
        om.ID,
        om.SOURCEORG1ID,
        ORG1.NAME as SOURCEORG1NAME,
        om.SOURCEORG2ID,
        ORG2.NAME as SOURCEORG2NAME,
        om.NEWORGID,
        ORGNEW.NAME as NEWORGNAME,
        om.MERGERDATE,
        (case when @CONTEXTID = om.SOURCEORG1ID then 1 else 0 end) as ORGISSOURCE1,
        (case when @CONTEXTID = om.SOURCEORG2ID then 1 else 0 end) as ORGISSOURCE2,
        (case when @CONTEXTID = om.NEWORGID then 1 else 0 end) as ORGISNEW
    from ORGMERGERS om
        inner join dbo.CONSTITUENT ORG1 on om.SOURCEORG1ID = ORG1.ID
        inner join dbo.CONSTITUENT ORG2 on om.SOURCEORG2ID = ORG2.ID
        inner join dbo.CONSTITUENT ORGNEW on om.NEWORGID = ORGNEW.ID
    order by MERGERDATE desc