GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE GetUserCourseCompletedPercent
@UserEmail nchar(100),
@CourseId int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @TotalCount REAL;
DECLARE @CompletedCount REAL;
(select @TotalCount =
count(ua.id)
from
[MSInvolveOD].[dbo].[msinvolveCourseModule] m
join [MSInvolveOD].[dbo].[msinvolveCourse] c
on m.courseid = c.id
join [MSInvolveOD].[dbo].[msinvolveModuleActivity] ma
on
ma.moduleid = m.id
join [MSInvolveOD].[dbo].[msinvolveUserActivity] ua
on ma.id = ua.activityid
where c.id = @CourseId
and ua.userid = @UserEmail )
select @CompletedCount =
count(ma.id)
from
[MSInvolveOD].[dbo].[msinvolveCourseModule] m
join [MSInvolveOD].[dbo].[msinvolveCourse] c
on m.courseid = c.id
join [MSInvolveOD].[dbo].[msinvolveModuleActivity] ma
on
ma.moduleid = m.id
where c.id = @CourseId
RETURN ROUND (( (@TotalCount / @CompletedCount) * 100 ), 0)
END
GO
No comments:
Post a Comment