Monday, November 28, 2011

T-SQL JOIN 4 tables, divide count and calculate percentage

declare @cc as REAL;
declare @dd as REAL;
(select @cc =
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 = 12
 and ua.userid = 'xxx@yyy.com' )


select @dd =
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 = 12  
 select @dd, @cc;
 select ROUND (( (@cc / @dd) * 100 ), 0)

No comments:

Post a Comment