USP_SCHEDULEDATE_GENERATEENTRIES

            CREATE PROCEDURE [dbo].[USP_SCHEDULEDATE_GENERATEENTRIES] 
            (
                @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       
                declare @SESSIONID uniqueidentifier
                declare @STARTDAYSEQUENCE integer --term starts with selection; used when setting rownumber offset

                declare @DAYCOUNT integer --used for modulus to repeat day entries for all dates

                declare @TIMETABLEID uniqueidentifier 
                select 
                    @SCHOOLID = ACADEMICYEAR.SCHOOLID,
                    @SESSIONID = SESSION.ID,
                    @STARTDAYSEQUENCE = CYCLEDAY.SEQUENCE - 1, -- bring SEQUENCE to zero base

                    @DAYCOUNT = 
                        (select count(CYCLEDAY.ID) 
                         from CYCLEDAY 
                         inner join TIMETABLE 
                            on TIMETABLE.CYCLEID = CYCLEDAY.CYCLEID
                         inner join SESSION S
                            on S.TIMETABLEID = TIMETABLE.ID
                         where S.ID = SESSION.ID),
                    @TIMETABLEID = SESSION.TIMETABLEID
                from 
                    TERM
                inner join SESSION 
                    on SESSION.ID = TERM.SESSIONID
                inner join ACADEMICYEAR 
                    on ACADEMICYEAR.ID = SESSION.ACADEMICYEARID 
                inner join TIMETABLEDAY
                    on TIMETABLEDAY.ID = Coalesce(@TIMETABLEDAYID, TERM.TIMETABLEDAYID)
                inner join CYCLEDAY 
                    on CYCLEDAY.ID = TIMETABLEDAY.CYCLEDAYID
                where TERM.ID = @TERMID

                declare @TempTbl table (
                   [ID] uniqueidentifier,
                   [DATE] date,
                   [INSESSION] bit,
                   [TIMETABLEDAYID] uniqueidentifier,
                   [ROWOFFSET] integer)

                insert into @TempTbl (ID, DATE) 
                select 
                    newid(),
                    DATEADD(d,NUM,@STARTDATE)
                from 
                    NUMBERS 
                where 
                    DATEADD(d,NUM,@STARTDATE) <= @ENDDATE

                update @TempTbl 
                    set INSESSION = 
                        (select case 
                        when (DATENAME(WEEKDAY,DATE) = 'Sunday' and (select SUNDAY from SESSION where ID = @SESSIONID) = 1) then 1 
                        when (DATENAME(WEEKDAY,DATE) = 'Monday' and (select MONDAY from SESSION where ID = @SESSIONID) = 1) then 1 
                        when (DATENAME(WEEKDAY,DATE) = 'Tuesday' and (select TUESDAY from SESSION where ID = @SESSIONID) = 1) then 1 
                        when (DATENAME(WEEKDAY,DATE) = 'Wednesday' and (select WEDNESDAY from SESSION where ID = @SESSIONID) = 1) then 1 
                        when (DATENAME(WEEKDAY,DATE) = 'Thursday' and (select THURSDAY from SESSION where ID = @SESSIONID) = 1) then 1 
                        when (DATENAME(WEEKDAY,DATE) = 'Friday' and (select FRIDAY from SESSION where ID = @SESSIONID) = 1) then 1 
                  when (DATENAME(WEEKDAY,DATE) = 'Saturday' and (select SATURDAY from SESSION where ID = @SESSIONID) = 1) then 1 
                        else 0 end)

                declare @offset integer
                -- subtract 1 to start ROWOFFSET offset at zero base 

                select @offset = (@STARTDAYSEQUENCE - 1) 

                -- set row number starting from offset

                update  @TempTbl
                set @offset = 
                    ROWOFFSET = @offset + 1
                where INSESSION = 1

                update @TempTbl
                set TIMETABLEDAYID =
                    (select TIMETABLEDAY.ID 
                    from 
                        TIMETABLEDAY 
                    inner join CYCLEDAY 
                        on CYCLEDAY.ID = TIMETABLEDAY.CYCLEDAYID
                    where (TIMETABLEDAY.TIMETABLEID = @TIMETABLEID) and
                            (SEQUENCE = (ROWOFFSET % @DAYCOUNT) + 1)) -- add 1 to find one based sequence

                from 
                    @TempTbl
                where INSESSION = 1

                if @@Error <> 0
                    return 1

                -- delete existing entries

                delete 
                    SCHEDULEDATE
                where SCHOOLID = @SCHOOLID and 
                    DATE >= @STARTDATE and DATE <=@ENDDATE

                -- insert new items

                insert into [SCHEDULEDATE] 
                    ([SCHOOLID], 
                    [DATE],
                    [ID],
                    [INSESSION],
                    [TIMETABLEDAYID],                
                    ADDEDBYID, 
                    CHANGEDBYID, 
                    DATEADDED, 
                    DATECHANGED)
                select @SCHOOLID, 
                    [DATE],
                    [ID],
                    [INSESSION],
                    [TIMETABLEDAYID], 
                    @CHANGEAGENTID, 
                    @CHANGEAGENTID, 
                    @CHANGEDATE, 
                    @CHANGEDATE
                from @TempTbl as [temp]

                --reset all daycounts for the given term

                declare @SESSIONSTARTDATE date
                declare @SESSIONENDDATE date
                select @SESSIONSTARTDATE = STARTDATE from dbo.UFN_SESSION_GETDATES(@SESSIONID)               
                select @SESSIONENDDATE = ENDDATE from dbo.UFN_SESSION_GETDATES(@SESSIONID)  

                update SCHEDULEDATE set SCHEDULEDATE.DAYCOUNT = SD.ROWNUM 
                from 
                (
                    select 
                        SCHEDULEDATE.DATE, SCHEDULEDATE.SCHOOLID, 
                        ROW_NUMBER() OVER(ORDER BY DATE) as ROWNUM 
                    from 
                        SCHEDULEDATE 
                    where 
                        SCHEDULEDATE.DATE >= @SESSIONSTARTDATE and 
                        SCHEDULEDATE.DATE <= @SESSIONENDDATE and 
                        SCHEDULEDATE.SCHOOLID = @SCHOOLID AND INSESSION = 1
                ) SD
                where SD.DATE = SCHEDULEDATE.DATE and SD.SCHOOLID = SCHEDULEDATE.SCHOOLID 

                if @@Error <> 0
                    return 2

                return 0