Hello Team,
I am using MSSQL 2014 I have these tables:
CREATE TABLE [dbo].[carry_over_course](
[carry_over_course_id] [int] IDENTITY(1,1) NOT NULL,
[course_master_id] [int] NOT NULL,
[student_profile_id] [int] NOT NULL,
[student_profile_no]nvarchar NOT NULL,
[academic_year_id] [int] NOT NULL,
[academic_semester_id] [int] NOT NULL,
[academic_level_id] [int] NOT NULL,
[state_id] [int] NOT NULL,
[study_centre_id] [int] NOT NULL,
[department_id] [int] NULL
)
CREATE TABLE [dbo].[academic_semester](
[academic_semester_id] [int] IDENTITY(1,1) NOT NULL,
[academic_semester_name] nvarchar NOT NULL
)
CREATE TABLE [dbo].[course_master](
[course_master_id] [int] IDENTITY(1,1) NOT NULL,
[course_master_code] nvarchar NOT NULL,
[course_master_credit_unit] [int] NULL,
[academic_level_id] [int] NOT NULL,
[academic_semester_id] [int] NOT NULL
)
CREATE TABLE [dbo].[department](
[department_id] [int] IDENTITY(1,1) NOT NULL,
[department_code] nvarchar NOT NULL
)
CREATE TABLE [dbo].[departmental_course](
[departmental_course_id] [int] IDENTITY(1,1) NOT NULL,
[course_master_id] [int] NOT NULL,
[department_id] [int] NOT NULL
)
CREATE TABLE [dbo].[student_profile](
[student_profile_id] [int] IDENTITY(1,1) NOT NULL,
[student_profile_reg_no] nvarchar NOT NULL,
[student_profile_gender] nvarchar NOT NULL
)
The main table is [dbo].[carry_over_course], where all the students that have carry over are store. I want to create a view with the following
[carry_over_course] [student_profile_no],
[academic_semester][academic_semester_name],
[course_master][course_master_code],
[course_master][course_master_credit_unit],
[department][department_code],
[student_profile][student_profile_gender]
totalcreditEarned,
totalcreditFailed,
expectedTotalCredit
Where I have issue is : totalcreditEarned, totalcreditFailed, expectedTotalCredit.
expectedTotalCredit = sum([course_master][course_master_credit_unit]) where [department][department_id] = [departmental_course][department_id] AND [course_master][course_master_id] = [departmental_course][course_master_id]
totalcreditFailed = SUM([course_master][course_master_credit_unit]) where [course_master][course_master_id] is IN [carry_over_course][course_master_id]
totalcreditEarned = expectedTotalCredit - totalcreditFailed
That is everything will be grouped by [department_id], [academic_semester_id] and [student_profile_id]
How do I achieve this?
Also, I dont know how to include [departmental_course] table into it.
Thanks
This is what I have do so far
SELECT
co.student_profile_reg_no, ay.academic_year_name as CarryOverYear, sm.academic_semester_name as CarryOverSemester,
al.academic_level_name as CarryOverLevel, s.student_profile_gender,
CONCAT(s.student_profile_first_name,' ',s.student_profile_first_name) as FullName, so.state_code, so.state_name,
p.programme_code, d.department_code, d.department_name, sc.study_centre_code, sc.study_centre_name,
cm.course_master_code, cm.course_master_credit_unit AS courseCredit
FROM
Student.carry_over_course AS co INNER JOIN
academic_year AS ay ON ay.academic_year_id = co.academic_year_of_carryover INNER JOIN
academic_semester AS sm ON sm.academic_semester_id = co.academic_semester_of_carryover INNER JOIN
academic_level AS al ON al.academic_level_id = co.academic_semester_of_carryover INNER JOIN
student_profile AS s ON s.student_profile_id = co.student_profile_id INNER JOIN
study_centre AS sc ON sc.study_centre_id = co.study_centre_id INNER JOIN
state AS so ON so.state_id = sc.state_id INNER JOIN
department AS d ON d.department_id = co.department_id INNER JOIN
programme AS p ON p.programme_id = d.programme_id INNER JOIN
course_master AS cm ON cm.course_master_id = co.course_master_id
WHERE co.carry_over_course_status = 0