July 2, 2015 at 4:08 am
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.
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 inserted 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.
July 2, 2015 at 4:18 am
You will get a better response to this question (ie, a working solution) if you post it as described in the first link in my signature.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 2, 2015 at 4:19 am
So you want the student's attendance record for the class he or she is currently in? I'm not sure that's possible, because you don't appear to have anything linking students to classes.
John
July 2, 2015 at 4:23 am
I have a table with
StudentClassLink = With columns [Guid](PK), [StudentId](FK of Student table),[ClassId](FK of Class Table).
July 2, 2015 at 4:29 am
Excellent. So that you're not drip-feeding information to us, please follow Phil's advice and post full table DDL and sample data.
Thanks
John
Edit - although I still don't think you've got enough information, because that last table you mentioned doesn't specify when the student started and finished in the class.
July 2, 2015 at 4:42 am
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.
I need the output like
I need the Attendance count as per the column present in the classtable [StartDate] and [Enddate]
July 2, 2015 at 5:40 am
arunnrj87 (7/2/2015)
Daily AttendanceGuid 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.
I need the output like
I need the Attendance count as per the column present in the classtable [StartDate] and [Enddate]
You seem to be having trouble following my link. Here it is again.
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 2, 2015 at 9:34 am
Even without sample data, this one is going to need a calendar table of some kind as well as a holiday table. because there will be a need to represent each and every date that class is held. Additionally, it is always possible for a class to get cancelled due to weather, so a cancellation table will be needed as well, that links cancellations to classes. There's a fair amount of "reality" that has to come into play here. Just having the start and end dates for a class isn't going to help much when the class spans a month or two in the winter. Also, what to do about classes that are held Monday, Wednesday, Friday, versus Tuesday, Thursday versus all 5 weekdays? What about classes held on a weekend ? There needs to be something in the table of classes (or a separate table that links classes to days of the week) to indicate this. Also, how about rescheduled classes, where due to weather, a given class date is moved to a non-normal day of the week? If one is not careful, one could end up preventing proper attendance date values from being entered.
So, not only is the post lacking sufficient information, it also doesn't address some of the most basic stuff that kind of "has to be there" in any such system in order to handle real-life events. Hopefully, this will be taken into account...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply