USP_SCHEDULEDATEADJUSTMENT_GETDATA

Returns data required when adding or editing an adjustment.

Parameters

Parameter	Parameter Type	Mode
@SESSIONID	uniqueidentifier	IN
@DATE	date	IN
@TIMETABLEDAY	nvarchar(60)	INOUT
@NEXTTIMETABLEDAYID	uniqueidentifier	INOUT
@NEXTTIMETABLEDAY	nvarchar(60)	INOUT
@ADJUSTEDTIMETABLEDAYID	uniqueidentifier	INOUT
@DESCRIPTION	nvarchar(255)	INOUT

Definition

Copy



create procedure dbo.USP_SCHEDULEDATEADJUSTMENT_GETDATA
(
    @SESSIONID uniqueidentifier,
    @DATE date,
    @TIMETABLEDAY nvarchar(60) output,
    @NEXTTIMETABLEDAYID uniqueidentifier output,
    @NEXTTIMETABLEDAY nvarchar(60) output,
    @ADJUSTEDTIMETABLEDAYID uniqueidentifier output,
    @DESCRIPTION nvarchar(255) output
)
as
begin
    select
        @TIMETABLEDAY = CYCLEDAY.DESCRIPTION,
        @NEXTTIMETABLEDAYID =
            coalesce(SCHEDULEDATEADJUSTMENT.NEXTTIMETABLEDAYID, SCHEDULEDATE.TIMETABLEDAYID),
        @NEXTTIMETABLEDAY = 
            (select top(1) CYCLEDAY.DESCRIPTION
            from dbo.SCHEDULEDATE
                inner join dbo.TIMETABLEDAY on SCHEDULEDATE.TIMETABLEDAYID = TIMETABLEDAY.ID
                inner join dbo.CYCLEDAY on TIMETABLEDAY.CYCLEDAYID = CYCLEDAY.ID
            where SCHEDULEDATE.SCHOOLID = ACADEMICYEAR.SCHOOLID
                and SCHEDULEDATE.DATE > @DATE
            order by SCHEDULEDATE.DATE),
        @ADJUSTEDTIMETABLEDAYID = SCHEDULEDATEADJUSTMENT.ADJUSTEDTIMETABLEDAYID,
        @DESCRIPTION = SCHEDULEDATEADJUSTMENT.DESCRIPTION
    from dbo.SESSION
        inner join dbo.ACADEMICYEAR on SESSION.ACADEMICYEARID = ACADEMICYEAR.ID
        inner join dbo.SCHEDULEDATE on ACADEMICYEAR.SCHOOLID = SCHEDULEDATE.SCHOOLID
        left join SCHEDULEDATEADJUSTMENT on
            SCHEDULEDATE.DATE = SCHEDULEDATEADJUSTMENT.DATE and
            SCHEDULEDATE.SCHOOLID = SCHEDULEDATEADJUSTMENT.SCHOOLID
        left join dbo.TIMETABLEDAY on SCHEDULEDATE.TIMETABLEDAYID = TIMETABLEDAY.ID
        left join dbo.CYCLEDAY on TIMETABLEDAY.CYCLEDAYID = CYCLEDAY.ID
    where SCHEDULEDATE.DATE = @DATE
        and SESSION.ID = @SESSIONID
end