UFN_GETWITHDRAWNDETAILS

            CREATE function dbo.UFN_GETWITHDRAWNDETAILS
            (
                @CLASSID uniqueidentifier,
                @STUDENTID uniqueidentifier
            )
                returns @RESULTS table
                (
                    WITHDRAWN_TERMID uniqueidentifier,
                    WITHDRAWN_TERMNAME nvarchar(100),
                    WITHDRAWN_DATE date,
                    WITHDRAWN_STATUS bit
                )
                as
                begin

                WITH WithdrawnTerm(TERMID,STARTDATE,ENDDATE,DESCRIPTION)
                as
                (
                    select
                    TM.ID,
                    T.STARTDATE,
                    T.ENDDATE,
                    TMN.DESCRIPTION
                    from
                    (select
                        MIN(CLASSMEETINGGROUP.STARTDATE) STARTDATE,
                        MIN(CLASSMEETINGGROUP.ENDDATE) ENDDATE
                        from dbo.CLASS
                            inner join dbo.COURSE on CLASS.COURSEID = COURSE.ID
                            inner join dbo.ACADEMICYEAR on COURSE.SCHOOLID = ACADEMICYEAR.SCHOOLID
                            and ACADEMICYEAR.STARTDATE <= CLASS.STARTDATE
                            and ACADEMICYEAR.ENDDATE >= CLASS.ENDDATE
                            inner join dbo.ACADEMICYEARNAMECODE on ACADEMICYEAR.ACADEMICYEARNAMECODEID = ACADEMICYEARNAMECODE.ID
                            inner join dbo.CLASSMEETINGGROUP on CLASS.ID = CLASSMEETINGGROUP.CLASSID
                            inner join dbo.STUDENTCLASSMEETINGGROUP on CLASSMEETINGGROUP.ID = STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID
                                and dbo.STUDENTCLASSMEETINGGROUP.STATUSCODE = 2
                            inner join dbo.STUDENTCOURSE on STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID = STUDENTCOURSE.ID
                            inner join dbo.CONSTITUENT on STUDENTCOURSE.STUDENTID = CONSTITUENT.ID
                            inner join dbo.SESSION on ACADEMICYEAR.ID = SESSION.ACADEMICYEARID
                            inner join dbo.TERM on SESSION.ID = TERM.SESSIONID
                                and CLASSMEETINGGROUP.STARTDATE >= TERM.STARTDATE
                                and CLASSMEETINGGROUP.ENDDATE <= TERM.ENDDATE
                            inner join dbo.TERMNAMECODE on TERM.TERMNAMECODEID = TERMNAMECODE.ID
                        where CLASS.ID = @CLASSID
                            and STUDENTCOURSE.STUDENTID = @STUDENTID) t
                    inner join TERM TM on TM.STARTDATE = t.STARTDATE and TM.ENDDATE = t.ENDDATE    
                    inner join TERMNAMECODE TMN ON TMN.ID = TM.TERMNAMECODEID
                )
                insert into @RESULTS
                    select 
                        WithdrawnTerm.TERMID,
                        WithdrawnTerm.DESCRIPTION,
                        STUDENTCLASSMEETINGGROUP.STATUSDATE WITHDRAWALDATE,
                        (CASE STUDENTCLASSMEETINGGROUP.STATUSCODE WHEN 2 THEN 1 END) WITHDRAWNSTATUS
                    from dbo.CLASS
                        inner join dbo.CLASSMEETINGGROUP on CLASS.ID = CLASSMEETINGGROUP.CLASSID
                        inner join dbo.STUDENTCLASSMEETINGGROUP on CLASSMEETINGGROUP.ID = STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID
                            and dbo.STUDENTCLASSMEETINGGROUP.STATUSCODE = 2
                        inner join dbo.STUDENTCOURSE on STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID = STUDENTCOURSE.ID
                        inner join dbo.CONSTITUENT on STUDENTCOURSE.STUDENTID = CONSTITUENT.ID
                        inner join dbo.TERM on (CLASSMEETINGGROUP.STARTDATE >= TERM.STARTDATE
                                and CLASSMEETINGGROUP.ENDDATE <= TERM.ENDDATE)
                        inner join WithdrawnTerm on WithdrawnTerm.TERMID = TERM.ID                 
                        where CLASS.ID = @CLASSID
                            and STUDENTCOURSE.STUDENTID = @STUDENTID;

                return
            end