set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[GetClassDates3015] 
@TimetableDetailID int 
AS 
BEGIN 
DECLARE @Day int, 
  @LecturerID int, 
  @RoomID int, 
  @Start datetime, 
  @End datetime, 
  @AcademicPeriod int, 
  @StreamUnitID int 
SELECT @Day = [Day], @RoomID = RoomID, @LecturerID = td.LecturerID, 
@Start = [Start], @End = [End], @AcademicPeriod = S.Period, @StreamUnitID = [StreamUnitID] 
    FROM TimetableDetail td INNER JOIN Stream S ON S.ID = td.StreamID 
    WHERE td.ID = @TimetableDetailID 
SELECT c.ClassDate, CONVERT (Char(11), c.ClassDate , 103) as 'Date', dbo.DayName(c.ClassDay,0) as 'Day', 
        IsNull(cd.Description,'') as Description 
    FROM dbo.GetClassDates(@TimetableDetailID) c 
        LEFT JOIN CalendarDate cd ON c.ClassDate = cd.Date 
        LEFT JOIN AttendanceClass ac ON c.ClassDate = CAST(FLOOR(CAST(ac.ClassDateTime AS float))AS datetime) 
            AND AttendanceItemID IN 
                (SELECT ID 
                    FROM AttendanceItem 
                    WHERE TimetableDetailID in 
                        (SELECT td.ID 
                            FROM TimetableDetail td 
                                JOIN TimetableLink tl ON td.ID = tl.TimetableDetailID 
                                INNER JOIN Stream S ON S.ID = td.StreamID 
                            WHERE [Day] = @Day AND RoomID = @RoomID 
                            AND td.LecturerID = @LecturerID AND [start] = @Start 
                            AND [End] = @End AND S.Period = @AcademicPeriod 
                            AND td.StreamUnitID = @StreamUnitID 
                        ) 
                ) 
    WHERE ac.ID IS NULL 
    ORDER BY c.ClassDate 
END