USP_SPONSOR_REBUILDSPONSORSHIPCONSTITUENCIES

create procedure dbo.USP_SPONSOR_REBUILDSPONSORSHIPCONSTITUENCIES (
    @CONSTITUENTID uniqueidentifier,
    @SPONSORTYPECODE smallint = -1,        -- -1 means do all types

    @CHANGEAGENTID uniqueidentifier = null
)
as
begin
    declare @CONTEXTCACHE varbinary(128);
    set @CONTEXTCACHE = CONTEXT_INFO();

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

    -- cache current context

    if @CHANGEAGENTID is not null
        set CONTEXT_INFO @CHANGEAGENTID

    -- remove sponsordaterange records from both constituents

    delete from dbo.SPONSORDATERANGE
    where CONSTITUENTID = @CONSTITUENTID
    and (@SPONSORTYPECODE = -1 or SPONSORTYPECODE = @SPONSORTYPECODE);

    -- restore cached context

    if not @CONTEXTCACHE is null
        set CONTEXT_INFO @CONTEXTCACHE

    -- recreate target constituent's constituencies

    declare @SDRID uniqueidentifier;
    declare @CURRENTSPONSORTYPECODE tinyint, @PRIORSPONSORTYPECODE tinyint;
    declare @DATEFROM date, @DATETO date, @PRIORDATEFROM date, @PRIORDATETO date;
    declare SDR_CURSOR cursor local fast_forward for
        select SPONSORTYPECODE, STARTDATE, ENDDATE
        from dbo.V_QUERY_FINANCIALSPONSORSHIPDATERANGE
        where CONSTITUENTID = @CONSTITUENTID
        and (@SPONSORTYPECODE = -1 or SPONSORTYPECODE = @SPONSORTYPECODE)
        order by SPONSORTYPECODE, STARTDATE;

    open SDR_CURSOR;
    fetch next from SDR_CURSOR into @CURRENTSPONSORTYPECODE, @DATEFROM, @DATETO;

    while (@@FETCH_STATUS = 0)
    begin
        if @SDRID is null or    -- first iteration

           @CURRENTSPONSORTYPECODE <> @PRIORSPONSORTYPECODE or        -- new sponsortypecode

           @DATEFROM > @PRIORDATETO        -- new range

        begin
            set @SDRID = newid();

            insert into dbo.SPONSORDATERANGE
            (
                ID,
                CONSTITUENTID,
                SPONSORTYPECODE,
                DATEFROM,
                DATETO,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            values
            (
                @SDRID,
                @CONSTITUENTID,
                @CURRENTSPONSORTYPECODE,
                @DATEFROM,
                @DATETO,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            )

            set @PRIORSPONSORTYPECODE = @CURRENTSPONSORTYPECODE;
            set @PRIORDATETO = @DATETO;
        end
        else
        begin
            if @PRIORDATETO is not null and
               (@DATETO is null or @DATETO > @PRIORDATETO)
            begin
                update dbo.SPONSORDATERANGE
                set DATETO = @DATETO
                where ID = @SDRID;

                set @PRIORDATETO = @DATETO;
            end
        end            

        fetch next from SDR_CURSOR into @CURRENTSPONSORTYPECODE, @DATEFROM, @DATETO;
    end
    close SDR_CURSOR;
    deallocate SDR_CURSOR;
end