USP_SCHEDULEDATE_ADJUSTENTRIES

            CREATE PROCEDURE [dbo].[USP_SCHEDULEDATE_ADJUSTENTRIES] 
            (
                @TERMID uniqueidentifier,
                @STARTDATE date = null, -- date to generate from

                @ENDDATE date = null, -- date to generate to

                @TIMETABLEDAYID uniqueidentifier = null, -- used to adjust the cycle day

                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null
            )
            as
                set nocount on

                if @CHANGEAGENTID is null
                    exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

                if @CHANGEDATE is null
                    set @CHANGEDATE = getdate()

                if @STARTDATE is null
                    select @STARTDATE = STARTDATE from TERM where TERM.ID = @TERMID

                if @ENDDATE is null
                    select @ENDDATE = ENDDATE from TERM where TERM.ID = @TERMID 

                declare @SCHOOLID uniqueidentifier       
                select 
                    @SCHOOLID = ACADEMICYEAR.SCHOOLID
                from 
                    TERM
                inner join SESSION 
                    on SESSION.ID = TERM.SESSIONID
                inner join ACADEMICYEAR 
                    on ACADEMICYEAR.ID = SESSION.ACADEMICYEARID 
                where TERM.ID = @TERMID

                -- remove invalid dates and adjustments

                exec dbo.USP_SCHEDULEDATE_CLEANUP @CHANGEAGENTID

                -- generate calendar entries

                -- recreate all base date entries forward from start date and adjust below

                -- this creates some overhead by recreating dates multiple times for each adjustment

                -- but greatly simplifies the process 

                exec dbo.USP_SCHEDULEDATE_GENERATEENTRIES @TERMID, @STARTDATE, null, null, @CHANGEAGENTID

                declare @TempTbl table (
                   ROWID int identity(1,1),
                   DATE date, 
                   ADJUSTEDTIMETABLEDAYID uniqueidentifier, 
                   NEXTTIMETABLEDAYID uniqueidentifier) 

                insert into @TempTbl (DATE, ADJUSTEDTIMETABLEDAYID, NEXTTIMETABLEDAYID) 
                select DATE, ADJUSTEDTIMETABLEDAYID, NEXTTIMETABLEDAYID   
                from 
                    SCHEDULEDATEADJUSTMENT
                where SCHOOLID = @SCHOOLID
                    AND DATE >= @STARTDATE and DATE <= @ENDDATE
                order by DATE

                declare @rowId int, @maxRowId int   
                declare @DATE date 
                declare @ADJUSTEDTIMETABLEDAYID uniqueidentifier
                declare @NEXTTIMETABLEDAYID uniqueidentifier      
                declare @NEXTDATE date

                select
                   @rowId = min(ROWID),
                   @MaxRowId = max(ROWID)
                from @TempTbl

                while @rowId <= @maxRowId
                begin
                    select
                       @DATE = DATE,
                       @ADJUSTEDTIMETABLEDAYID = ADJUSTEDTIMETABLEDAYID,
                       @NEXTTIMETABLEDAYID = NEXTTIMETABLEDAYID
                    from
                       @TempTbl
                    where 
                       ROWID = @rowId

                    if @ADJUSTEDTIMETABLEDAYID is null
                        begin
                            update SCHEDULEDATE set INSESSION = 0, TIMETABLEDAYID = null where DATE = @DATE and SCHOOLID = @SCHOOLID
                            set @NEXTDATE = dbo.UFN_SCHEDULEDATE_GETNEXTDATEINSESSION (@DATE, @SCHOOLID)
                            if not @NEXTDATE is null
                                exec dbo.USP_SCHEDULEDATE_GENERATEENTRIES @TERMID, @NEXTDATE, null, @NEXTTIMETABLEDAYID, @CHANGEAGENTID
                        end
                    else
                        exec dbo.USP_SCHEDULEDATE_GENERATEENTRIES @TERMID, @DATE, null, @ADJUSTEDTIMETABLEDAYID, @CHANGEAGENTID

                    set @rowId = @rowId + 1
                end

                return 0