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.
Loading
Rahul BhattPosted Jul 20, 2012, 9:04 AM
Need to added Group by MonthName and StudentID
Get Month Name using DATENAME(month, a.currentdat)
Make your query as folloiwng way
DECLARE @StudentID as int = 1
DECLARE @FromDate as DateTime= '06/01/2012'
DECLARE @ToDate as DateTime= '07/30/2012'
select
a.studentId,
SUM(Case when a.present_Absent = 1 then 1 else 0 end) as Present,
SUM(Case when a.present_Absent = 0 then 1 else 0 end) as Absent,
COUNT(a.studentId) as TotalLec,
DATENAME(month, a.currentdat) as MonthName
from attendance2 as a
where
a.studentId = @StudentID
and CONVERT(varchar(10),a.currentdat,102)
between CONVERT(varchar(10),@FromDate,102) and CONVERT(varchar(10),@ToDate,102)
Group by
a.studentId,
DATENAME(month, a.currentdat)
Out put
studentId
Present
Absent
TotalLec
MonthName
1
1
0
1
July
1
4
1
5
June
sameer gadadePosted Jul 26, 2012, 12:52 AM
i have learned a lot from you
Santhosh Kumar JayaramanPosted Jul 20, 2012, 7:52 AM
CREATE TABLE [dbo].[attendance2](
[Attenid] [int] NULL,
[studentId] int null,
[present_Absent] int null,
[teacherId] [int] NULL,
[Replacingteacherid] [int] NULL,
[replacedteacherid] [int] NULL,
[currentdat] [datetime] NULL,
[classid] [int] NULL,
[Studid] [int] NULL
) ON [PRIMARY]
insert into attendance2 values(1,1,0,1,1,0,'6/7/2012',1,1)
insert into attendance2 values(1,2,1,1,1,0,'6/7/2012',1,2)
insert into attendance2 values(1,3,1,1,1,0,'6/7/2012',1,3)
insert into attendance2 values(1,4,1,1,1,0,'6/7/2012',1,4)
insert into attendance2 values(2,1,1,1,1,0,'6/7/2012',2,2)
insert into attendance2 values(2,2,1,1,1,0,'6/7/2012',2,3)
insert into attendance2 values(3,1,1,1,2,1,'6/7/2012',3,1)
insert into attendance2 values(3,2,1,1,2,1,'6/8/2012',3,2)
insert into attendance2 values(4,1,1,1,1,0,'6/8/2012',1,3)
insert into attendance2 values(4,2,1,1,1,0,'6/8/2012',1,1)
insert into attendance2 values(5,1,1,1,2,1,'6/8/2012',2,2)
insert into attendance2 values(5,2,1,1,2,1,'6/8/2012',2,3)
insert into attendance2 values(6,1,1,1,2,1,'7/8/2012',2,2)
insert into attendance2 values(6,2,1,1,2,1,'7/8/2012',2,3)
select * from attendance2
select StudentID,count(distinct(AttenID)) as totallectures,
(Select COUNT(([present_Absent])) from attendance2
where [present_Absent]=1
and StudentID=1 and CONVERT(VARCHAR (4),[currentdat],107)=CONVERT(VARCHAR (4),a.currentdat,107)
) as Present,
(Select COUNT(([present_Absent])) from attendance2
where [present_Absent]=0
and StudentID=1 and CONVERT(VARCHAR (4),[currentdat],107)=CONVERT(VARCHAR (4),a.currentdat,107)
) as Absent,
CONVERT(VARCHAR (4),[currentdat],107)AS Month
from attendance2 a
WHERE StudentID=1 and
[currentdat] BETWEEN '1/1/2012' AND '9/9/2012'
group by StudentID,CONVERT(VARCHAR (4),[currentdat],107)
sameer gadadePosted Jul 20, 2012, 7:22 AM
studid totlectures present absent month
1 1 1 0 Jun
1 1 2 0 Jul
1 1 2 0 Jul
i need total lectures by student between date but group by month
ie if date between 1/1/2012 and 20/7/2012(dmy)
then output should show
total lectures,present for jan
total lectures,present for feb
--
total lectures,present for jul
query showing two records for jul instead one
studid totlectures present absent month
1 1 1 0 Jun
1 1 1 0 Jul
i
Santhosh Kumar JayaramanPosted Jul 20, 2012, 6:53 AM
(Select COUNT((Present_Absent)) from attendance
where Present_Absent=1
and Student_ID=1 and CONVERT(VARCHAR (4),[Cur_Date],107)
=CONVERT(VARCHAR (4),a.Cur_Date,107)) as Present,
(Select COUNT((Present_Absent)) from attendance
where Present_Absent=0 and Student_ID=1 and
CONVERT(VARCHAR (4),[Cur_Date],107)=CONVERT(VARCHAR (4),a.Cur_Date,107)) 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