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