USP_DATALIST_SCHEDULEDATE_BYSESSION

All of the schedule date entries for a given session.
Parameters

Parameter	Parameter Type	Mode	Description
@SESSIONID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@STARTDATE	datetime	IN	Start date
@ENDDATE	datetime	IN	End date
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_SCHEDULEDATE_BYSESSION
                    (
                        @SESSIONID uniqueidentifier,
                        @STARTDATE datetime = null,
                        @ENDDATE datetime = null
                    )
                as
                    set nocount on

                    select SCHEDULEDATE.ID,
                           SCHEDULEDATE.DATE,
                           CYCLEDAY.DESCRIPTION + ' - ' + CAST (SCHEDULEDATE.DAYCOUNT as nvarchar(4)) as NAME,                           
                           SCHEDULEDATEADJUSTMENT.DESCRIPTION,
                           SCHEDULEDATE.INSESSION,
                           CYCLEDAY.COLORSTYLECODE
                    from dbo.SCHEDULEDATE 
                    left join SCHEDULEDATEADJUSTMENT
                        on SCHEDULEDATEADJUSTMENT.DATE = SCHEDULEDATE.DATE and SCHEDULEDATEADJUSTMENT.SCHOOLID = SCHEDULEDATE.SCHOOLID
                    left join TIMETABLEDAY
                        on TIMETABLEDAY.ID = SCHEDULEDATE.TIMETABLEDAYID
                    left join CYCLEDAY 
                        on CYCLEDAY.ID = TIMETABLEDAY.CYCLEDAYID
                    where SCHEDULEDATE.SCHOOLID = 
                        (select SCHOOLID 
                         from 
                            SESSION 
                         inner join ACADEMICYEAR 
                            on SESSION.ACADEMICYEARID = ACADEMICYEAR.ID 
                         where SESSION.ID = @SESSIONID) AND
                         SCHEDULEDATE.DATE >= (select min(STARTDATE) from TERM where SESSIONID = @SESSIONID) AND
                         SCHEDULEDATE.DATE <= (select max(ENDDATE) from TERM where SESSIONID = @SESSIONID) AND
                         (
                            ( (SCHEDULEDATE.DATE between @STARTDATE and @ENDDATE) or (@STARTDATE is null and @ENDDATE is null) )
                            or ( (SCHEDULEDATE.DATE >= @STARTDATE) and (@ENDDATE is null and @STARTDATE is not null) )
                            or ( (SCHEDULEDATE.DATE <= @ENDDATE) and (@STARTDATE is null and @ENDDATE is not null) )
                         ) AND
                         INSESSION <> 0
                    order by SCHEDULEDATE.DATE