How to calculate the Attendance for single student

Transact-SQL
1

Hi ,

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

Declare
@StudentId Uniqueidentifier ='0B2D4D41-8D33-4D79-A981-03E0F093F458'

Begin

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

END

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.

2

Would you be able to provide sample data for a student for those 3 tables and an example of the expected outcome?

3

Daily Attendance
Guid Uniqueidentifer Number
AcademyId Uniqueidentifer Number
StudentId Uniqueidentifer Number
Guid, AcademyId , StudentId ,Date, Status,Reason

  •       -                  -              2015-06-24      P         -

  •         -                  -              2015-06-25      A         Health Problem

Student Details
Guid Uniqueidentifer Number

Guid FullName DOb Address

  •             Arun        30-12-2009          XYZ

ClassDetails
Guid Uniqueidentifer Number

Guid AcademyId Class Section Startdate Enddate

  •                  -                    5                  A                2015-05-07                      2016-03-14

For Each tables we have Unique Guid 16 digits number

I have another tables linking StudentID and ClassId

Table Name = StudentclassLink with Columns
GUid, StudentID and Classid.