hi i want help for query for calculating yearly attendance of student
table :
AttID-StudentId-present/absent- TeachrID-ReplacTeachID-ReplacID-StudID-Cur_Date
1 1 0 - 1 - 1 - 0 - 1 -6/1/2012(m/d/y)
1 2 1 - 1 - 1 - 0 - 2 -6/1/2012(m/d/y)
1 3 1 - 1 - 1 - 0 - 3 -6/1/2012(m/d/y)
2 1 1 - 1 - 1 - 0 - 1 -6/2/2012(m/d/y)
2 2 1 - 1 - 1 - 0 - 2 -6/2/2012(m/d/y)
2 3 1 - 1 - 1 - 0 - 3 -6/2/2012(m/d/y)
3 1 1 - 1 - 1 - 0 - 4 -6/3/2012(m/d/y)
3 2 1 - 1 - 1 - 0 - 5 -6/3/2012(m/d/y)
4 1 1 - 1 - 2 - 1 - 11 -6/4/2012(m/d/y)
4 2 1 - 1 - 2 - 1 - 12 -6/4/2012(m/d/y)
5 1 1 - 1 - 1 - 0 - 1 -6/7/2012(m/d/y)
5 2 1 - 1 - 1 - 0 - 2 -6/7/2012(m/d/y)
5 3 1 - 1 - 1 - 0 - 3 -6/7/2012(m/d/y)
6 1 0 - 1 - 2 - 1 - 1 -7/2/2012(m/d/y)
6 2 0 - 1 - 2 - 1 - 2 -7/2/2012(m/d/y)
6 3 1 - 1 - 2 - 1 - 3 -7/2/2012(m/d/y)
This is Student Attendance table.
if student is present then present/absent values=1 else 0
I wana output like this
Search Criteria for this- Student ID and From date and To date
Required Output:
i want total lectures and total attendance of student for months as
Student ID - Present - Absent - Total Lectures Month(curdate)
1 - 4 - 1 5 -jun
1 - 0 - 1 1 -jul
-------------------------------------------------------------------------
my query:
select Student_ID,count(distinct(Attendance_ID)) as totallectures,
(Select COUNT((Present_Absent)) from attendance
where Present_Absent=1
and Student_ID=1
) as Present,
(Select COUNT((Present_Absent)) from attendance
where Present_Absent=0
and Student_ID=1
) as Absent,
CONVERT(VARCHAR (4),[Cur_Date],107)AS Month
from attendance a
WHERE Student_ID=1 and
Cur_Date BETWEEN '1/1/2012' AND '9/9/2012'
group by Cur_Date,Student_ID
-------------------------------------------------------
please tell me where i am going wrong .
if possible please send me the query.