hiii everyone i want to get monthly attendance of each student .i have a table(tbl_attendance) in which student_id ,attendance_Id ,Attendance _Date ,bacth_Id and Status field is exist.and in Status field i am storing absent,persent ,holiday,leaves Status by (0,1,2,3).
i want to get attendance when i select batch,month,and year then i will get monthly attendance of each student based on number of persent with total number of class after removing Holiday in a month.
my stored procedure is
CREATE proc [dbo].[Sp_GetStudentAttendancePercentage]
(@studentId int,
@dateOfRqr date=null
)
as
BEGIN
select distinct ((select convert(decimal,(select COUNT(a.Student_Id)*100
from tbl_Attendance a where
a.Student_Id=@studentid and Status=1 and Month_Date between dateadd(mm,datediff(m,0,@dateOfRqr),0) and DATEADD(d,-1,DATEADD(m,1,dateadd(mm,datediff(m,0,@dateOfRqr),0)))
)))
/(select (DATEDIFF(d,dateadd(mm,datediff(m,0,@dateOfRqr),0),DATEADD(d,-1,DATEADD(m,1,dateadd(mm,datediff(m,0,@dateOfRqr),0))))+1) as percentage)
) as attendenceInPercent,s.Student_Id, s.First_Name,s.Middle_Name,s.Last_Name from tbl_Attendance t inner join tbl_Student s on t.Student_Id=s.Student_Id and t.Student_Id=@studentid
END
GO