Menu
ALTER
function [dbo].[GetLastAttandenceLectureID]
(
@courseID
int =11111)
returns
int
as
begin
declare @res int
set @res= (select top 1 LectureID from dbo.Lectures where
Lectures
.LectureID=
(SELECT top 1 LectureAttendancy.LectureID from dbo.LectureAttendancy where LectureID in
(select LectureID from Lectures where CourseID= @courseID )
order by LectureID desc
)
order by Lectures.LectureDate asc)
return @res
end
-----------
select
Students.Name + ' ' + Students.Surname, dbo.GetLastAttandenceLectureID(Courses.CourseID) 'Last Lecture ID',
*
from
Coursesjoin Lectures on Lectures.CourseID = Courses.CourseIDjoin LectureAttendancy ON Lectures.LectureID = LectureAttendancy.LectureID JOIN Students ON LectureAttendancy.StudentID = Students.StudentIDwhere dbo.GetLastAttandenceLectureID(Courses.CourseID) = Lectures.LectureIDand AttendacyStatus=2
