USP_SCHEDULEDATE_CLEANUP

            CREATE PROCEDURE [dbo].[USP_SCHEDULEDATE_CLEANUP] 
                (
                    @CHANGEAGENTID uniqueidentifier = null
                )
            as
                set nocount on

                declare @contextCache varbinary(128)

                -- cache current context information 

                set @contextCache = CONTEXT_INFO()

                -- set CONTEXT_INFO to @CHANGEAGENTID 

                if not @CHANGEAGENTID is null
                    set CONTEXT_INFO @CHANGEAGENTID

                delete 
                    SCHEDULEDATE
                where ID not in  
                    (select SCHEDULEDATE.ID 
                     from SCHEDULEDATE 
                     inner join 
                        (select TERM.STARTDATE, TERM.ENDDATE, ACADEMICYEAR.SCHOOLID from TERM 
                        inner join SESSION on SESSION.id = TERM.SESSIONID 
                        inner join ACADEMICYEAR on ACADEMICYEAR.ID = SESSION.ACADEMICYEARID 
                        ) as TERMDATES 
                            on TERMDATES.SCHOOLID = SCHEDULEDATE.SCHOOLID and 
                            (SCHEDULEDATE.DATE between TERMDATES.STARTDATE and TERMDATES.ENDDATE))

                delete 
                    SCHEDULEDATEADJUSTMENT 
                where ID not in  
                    (select SCHEDULEDATEADJUSTMENT.ID 
                     from SCHEDULEDATEADJUSTMENT 
                     inner join 
                        (select TERM.STARTDATE, TERM.ENDDATE, ACADEMICYEAR.SCHOOLID from TERM 
                        inner join SESSION on SESSION.id = TERM.SESSIONID 
                        inner join ACADEMICYEAR on ACADEMICYEAR.ID = SESSION.ACADEMICYEARID 
                        ) as TERMDATES 
                            on TERMDATES.SCHOOLID = SCHEDULEDATEADJUSTMENT.SCHOOLID and 
                            (SCHEDULEDATEADJUSTMENT.DATE between TERMDATES.STARTDATE and TERMDATES.ENDDATE))

                delete 
                    SCHEDULEDATEADJUSTMENT
                from
                    SCHEDULEDATEADJUSTMENT  
                inner join SCHEDULEDATE 
                    on SCHEDULEDATE.DATE = SCHEDULEDATEADJUSTMENT.DATE and SCHEDULEDATE.SCHOOLID = SCHEDULEDATEADJUSTMENT.SCHOOLID
                where 
                    INSESSION = 0 and 
                    not ADJUSTEDTIMETABLEDAYID is null

                 delete 
                     SCHEDULEDATEADJUSTMENT
                from
                    SCHEDULEDATEADJUSTMENT  
                inner join SCHEDULEDATE 
                    on SCHEDULEDATE.DATE = SCHEDULEDATEADJUSTMENT.DATE and SCHEDULEDATE.SCHOOLID = SCHEDULEDATEADJUSTMENT.SCHOOLID
                where 
                    INSESSION = 1 and 
                    not NEXTTIMETABLEDAYID is null   

                -- reset CONTEXT_INFO to previous value 

                if not @contextCache is null
                    set CONTEXT_INFO @contextCache

                return 0