UFN_SCHEDULEDATEADJUSTMENT_GETDATA

Returns data required when adding or editing an adjustment.

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode	Description
@SESSIONID	uniqueidentifier	IN
@DATE	date	IN

Definition

Copy



            CREATE function dbo.UFN_SCHEDULEDATEADJUSTMENT_GETDATA(
                @SESSIONID uniqueidentifier,
                @DATE date
            )
            returns table
            as return
                (select
                    (select CYCLEDAY.DESCRIPTION 
                     from CYCLEDAY 
                     inner join TIMETABLEDAY 
                        on TIMETABLEDAY.CYCLEDAYID = CYCLEDAY.ID 
                     where TIMETABLEDAY.ID = SCHEDULEDATE.TIMETABLEDAYID) AS TIMETABLEDAY,
                    case when ADJUSTEDTIMETABLEDAYID is null then coalesce(NEXTTIMETABLEDAYID, TIMETABLEDAYID) end AS NEXTTIMETABLEDAYID,
                    ADJUSTEDTIMETABLEDAYID,
                    DESCRIPTION
                from 
                    dbo.SCHEDULEDATE
                left join SCHEDULEDATEADJUSTMENT 
                    on SCHEDULEDATE.DATE = SCHEDULEDATEADJUSTMENT.DATE AND SCHEDULEDATE.SCHOOLID = SCHEDULEDATEADJUSTMENT.SCHOOLID
                where SCHEDULEDATE.DATE = @DATE AND 
                    SCHEDULEDATE.SCHOOLID = 
                        (select SCHOOLID 
                         from 
                            SESSION 
                         inner join ACADEMICYEAR 
                            on SESSION.ACADEMICYEARID = ACADEMICYEAR.ID 
                         where SESSION.ID = @SESSIONID)
            )