I have to calculate the Total number of days present and absent for a singel student.
AS of now i have 3 tables.
1.Daily attendance - Columns -[Guid][,AcademyId],[StudentId],[Date],[Status],[Reason]
2.Student details - Columns - [Guid],[FullName],[DOB],[Address]
3.Class Details - Columns - [Guid],[AcademyId],[Class],[Section],[Startdate],Enddate]
So now i have loaded all the data into the table.
According to above screen i need to fetch the data.
I can fetch the counts for total present and absent
Query i have tried is
@StudentId Uniqueidentifier ='0B2D4D41-8D33-4D79-A981-03E0F093F458'
select A.StudentId ,A.Date,Count(Date)Total,B.Guid,
(select COUNT(Date) from DailyAttendance where Status = 'P' And StudentId = @StudentId) As Attended,
(select COUNT(Date) from DailyAttendance where Status = 'A' And StudentId = @StudentId) As Missed from DailyAttendance A
Where A.StudentId = @StudentId
Group by A.StudentID,A.date
AS result of this query i get the data.Present count and Absent count from date Entered in Dailyattendance tables.
SO my problem is if the student have promoted to next class then by this query it will count the before year also how do i need to calculate the count according to the Class StartDate and Enddate as i mention in the Class Details table what will be the query.