pecentage - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

pecentage

Author

Topic

Mohamed Faisal
Yak Posting Veteran

51 Posts

Posted - 2013-08-11 : 03:42:42

I have 3 tables:

Student table
(StudentNumber,Name,Contact,Address,DateOfBirth,YearEnrolled,Year Of Study)

Absence table
(SubjectCode,StudentNumber, DateOfAbsence)

Subject table
(SubjectCode, Subject Name,Description)

I would need to display StudentNumber, Name, SubjectCode and the pecentatage of Date of Absence)taking that there are 10 lesson

How can i disply the above.

jethrow
Starting Member

37 Posts

Posted - 2013-08-11 : 04:23:20

[code]Declare @NumOfLessions float = 10 --specified in the OP
Declare @Student table (StudentNumber int, Name varchar(32))
Insert Into @Student values
(1, 'Joe'),
(2, 'Kelly'),
(3, 'Casey');
Declare @Absence table (SubjectCode char(3), StudentNumber int, DateOfAbsence date);
Insert Into @Absence values
('mat', 2, '2013-01-13'),
('mat', 1, '2013-01-16'),
('mat', 1, '2013-01-25'),
('ped', 3, '2013-01-15'),
('ped', 3, '2013-01-20');

Select a.StudentNumber, Name, SubjectCode,
Cast(Count(DateOfAbsence)/@NumOfLessions*100 as VarChar(5))+'%' PercentAbsent
From @Absence a
Left Join @Student s On a.StudentNumber=s.StudentNumber
Group By a.StudentNumber, Name, SubjectCode;[/code]

Microsoft SQL Server Noobie