USP_SPONSORSHIPDELETE_UPDATESPONSORDATERANGE

create procedure dbo.USP_SPONSORSHIPDELETE_UPDATESPONSORDATERANGE (
    @CONSTITUENTID uniqueidentifier,
    @SPONSORTYPECODE tinyint,
    @STARTDATE date,
    @ENDDATE date,
    @CHANGEAGENTID uniqueidentifier,
    @CURRENTDATE datetime
)
as
begin
    declare @SDR_ID uniqueidentifier;
    declare @SDR_DATEFROM datetime;
    declare @SDR_DATETO datetime;
    declare @ACTIVE_COUNT smallint;
    declare @COUNT smallint;
    declare @FIRSTSTARTDATE datetime;
    declare @LASTENDDATE datetime;

    select @SDR_ID = ID,
           @SDR_DATEFROM = DATEFROM,
           @SDR_DATETO = DATETO
    from dbo.SPONSORDATERANGE
    where CONSTITUENTID = @CONSTITUENTID
    and @STARTDATE >= DATEFROM
    and (DATETO is null or @ENDDATE <= DATETO)
    and SPONSORTYPECODE = @SPONSORTYPECODE;

    select
        @ACTIVE_COUNT = sum(case STATUSCODE when 1 then 1 else 0 end),
        @COUNT = count(*),
        @FIRSTSTARTDATE = min(STARTDATE),
        @LASTENDDATE = max(ENDDATE)
    from dbo.V_QUERY_FINANCIALSPONSORSHIPDATERANGE
    where CONSTITUENTID = @CONSTITUENTID
    and SPONSORTYPECODE = @SPONSORTYPECODE
    and STARTDATE >= @SDR_DATEFROM
    and (@SDR_DATETO is null or ENDDATE <= @SDR_DATETO)

    if @COUNT = 0

        -- no more sponsorships in range, delete constituency

        exec dbo.USP_SPONSORDATERANGE_DELETEBYID_WITHCHANGEAGENTID @SDR_ID, @CHANGEAGENTID

    else
    begin
        -- if the deleted one was the first sponsorship in the range, fix the start date

        if @SDR_DATEFROM <> @FIRSTSTARTDATE
            update dbo.SPONSORDATERANGE
            set DATEFROM = @FIRSTSTARTDATE,
                DATECHANGED = @CURRENTDATE,
                CHANGEDBYID = @CHANGEAGENTID
            where ID = @SDR_ID

        declare @NEW_SDR_STARTDATE datetime

        declare @WORKING_SDR_ID uniqueidentifier;
        set @WORKING_SDR_ID = @SDR_ID;

        -- this cursor will find sponsorships which do not overlap w/ a prior

        -- sponsorship in the date range

        -- these necessitate closing the current date range and starting a new one

        declare SPONSORSHIP_CURSOR cursor local fast_forward for
            select NEWSDR.STARTDATE
            from dbo.V_QUERY_FINANCIALSPONSORSHIPDATERANGE NEWSDR
            where NEWSDR.CONSTITUENTID = @CONSTITUENTID
            and NEWSDR.SPONSORTYPECODE = @SPONSORTYPECODE
            and NEWSDR.STARTDATE > @FIRSTSTARTDATE
            and (@SDR_DATETO is null or NEWSDR.STARTDATE <= @SDR_DATETO)
            and not exists(select 'x'
                           from dbo.V_QUERY_FINANCIALSPONSORSHIPDATERANGE PRIOR
                           where PRIOR.CONSTITUENTID = @CONSTITUENTID
                           and PRIOR.SPONSORTYPECODE = @SPONSORTYPECODE
                           and PRIOR.STARTDATE < NEWSDR.STARTDATE
                           and (PRIOR.ENDDATE >= NEWSDR.STARTDATE or PRIOR.ENDDATE is null))
            order by NEWSDR.STARTDATE;

        open SPONSORSHIP_CURSOR;
        fetch next from SPONSORSHIP_CURSOR into @NEW_SDR_STARTDATE;

        while (@@FETCH_STATUS = 0)
        begin
            declare @NEW_SDR_ENDDATE datetime;

            -- find the end date for the prior date range

            select @NEW_SDR_ENDDATE = max(ENDDATE)
            from dbo.V_QUERY_FINANCIALSPONSORSHIPDATERANGE
            where CONSTITUENTID = @CONSTITUENTID
            and SPONSORTYPECODE = @SPONSORTYPECODE
            and STARTDATE < @NEW_SDR_STARTDATE;

            -- this error is not expected, and likely indicates a logic error

            if @NEW_SDR_ENDDATE is null
            begin
              raiserror('BBERR_NONEWENDDATEFOUND', 13, 1);
              return 1
            end

            update dbo.SPONSORDATERANGE
            set DATETO = @NEW_SDR_ENDDATE,
                DATECHANGED = @CURRENTDATE,
                CHANGEDBYID = @CHANGEAGENTID
            where ID = @WORKING_SDR_ID;

            set @WORKING_SDR_ID = newid();

            -- create a new date range, the next iteration will put an end date on it

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

            fetch next from SPONSORSHIP_CURSOR into @NEW_SDR_STARTDATE;
        end
        close SPONSORSHIP_CURSOR;
        deallocate SPONSORSHIP_CURSOR;

        -- if there are other active sponsorships, then the last SDR is fine w/ no end date

        -- otherwise must set the enddate for that one too

        if @ACTIVE_COUNT = 0
        begin
            update dbo.SPONSORDATERANGE
            set DATETO = @LASTENDDATE,
                DATECHANGED = @CURRENTDATE,
                CHANGEDBYID = @CHANGEAGENTID
            where ID = @WORKING_SDR_ID;
        end
    end
end