Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more: , +
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
SQL
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
Posted
Updated 26-Sep-13 18:51pm
v2
Comments
ArunRajendra 27-Sep-13 0:54am    
Are you getting any error? or no getting the result as expected? Post the sample data and expected result.
manvendra patel 27-Sep-13 1:01am    
i am not getting error . at this time i am get attnedance percentage of each student on the based on number of persent in a month but i want firstly count number of working days after minus from holiday like (sunady) then count number of persent in working days.still i am getting percentage only by persent base in class and it not see how mucch working days in a month
ArunRajendra 27-Sep-13 1:06am    
Can you post your result along with sample data?
manvendra patel 27-Sep-13 1:22am    
on my attedance page is
1-one dropdown to select batch
2-second dropdown for select month
3-third dropdown for select year and on year dropdown selectindex to bind number of student and Perecetage in gridview which is exists in attendace table
ArunRajendra 27-Sep-13 2:19am    
I wont be able to help you with this information. I asked the sample data and the process and this I am repeating for 3rd time. Before replying understand what is asked.

1 solution

Code to find number of working days in a month.
pass the date in @Date variable

SQL
Declare @Date datetime
SET @Date='04/02/2014' /*mm/dd/yyyy*/

select DATEDIFF(DD,convert(varchar(250),DATEADD(dd,-(DAY(@Date)-1),@Date),101),convert(varchar(250),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@Date)+1,0)),101))+1



Write a function to ignore holidays from the date duration and call it in youre select statement. you'll get the actual working days.

Then calculate your student attendance %
 
Share this answer
 
Comments
manvendra patel 22-May-14 1:13am    
thanks lot for ur response Mr.aravinth04 . i solved this problem
m jawad niaz 9-Oct-17 23:06pm    
Can you plz share your full attendance app. i have a some problem. my yahoo mail is
mjawadniaz@yahoo.com

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900