August 13, 2008 at 3:07 pm
declare @SemesterStartDate varchar(12)
set @SemesterStartDate = '03/03/2008'
SELECT stu.StudentID as studentid
,stu.FirstName as FirstName
,stu.LastName as LastName
,reg.RegistrationID as RegistrationID
,c.CourseNumber as CourseNumber
,gr.Grade as Grade
,CONVERT(varchar(12),reg.GradeEnteredDate, 101) as GradeEntered--modified by MSO on 08/13/2008 to accept full date values
,CONVERT(varchar(12),sem.SemesterStart, 101) as SemStart--modified by MSO on 08/13/2008 to accept full date values
,CONVERT(varchar(12),sem.SemesterEnd, 101) as SemEnd--modified by MSO on 08/13/2008 to accept full date values
,sem.SemesterName as TitleIVDescription
,sem.SemesterID,sem.SemesterName as ParentSemesterDesc
,CONVERT(varchar(12),ad.FirstAttendanceDate, 101) AS FirstAttendanceDate--modified by MSO on 08/13/2008 to accept full date values
,CONVERT(varchar(12),ad.LastAttendanceDate, 101) AS LastAttendanceDate--modified by MSO on 08/13/2008 to accept full date values
FROM tblstudent stu WITH (NOLOCK)
INNER JOIN tblregistration reg WITH (NOLOCK) on stu.Studentid = reg.StudentID
INNER JOIN tblCoursesOffered co WITH (NOLOCK) on reg.CourseOfferedID = co.CourseOfferedID
LEFT OUTER JOIN tblAttendanceData ad WITH (NOLOCK) on ad.StudentID=stu.StudentID AND ad.CourseOfferedID=co.CourseOfferedID
INNER JOIN tlkpSemester sem WITH (NOLOCK) ON sem.SemesterID = co.SemesterID
INNER JOIN tblCourses c WITH (NOLOCK)ON co.CourseID = c.CourseID
INNER JOIN tlkpGrade gr WITH (NOLOCK) ON reg.GradeID = gr.GradeID
WHERE sem.SemesterID = (SELECT TOP 1 tlkpSemester.SemesterID FROM tlkpSemester WITH (NOLOCK)
WHERE tlkpSemester.SemesterStart>=@SemesterStartDate
Order by tlkpSemester.SemesterStart)
AND gr.Grade IN ('F','FAIL','WF','W','DP','I')
ORDER BY stu.studentid
August 13, 2008 at 3:09 pm
August 13, 2008 at 3:32 pm
I have a question....The script is showing all students with either F,FAIL,WD,WF,I but I only want them to show if they have at least an F.
August 13, 2008 at 3:37 pm
August 13, 2008 at 4:01 pm
Okay....when you run the query it shows all students that have a grade of either(F-same as fail but interpreted differently by the users),FAIL,WF,WD,I,DP,DF. These are all grades a student can get per a class in a semester. e.g. Student A in summer 08 semester has an F for history class so i want to show Student A on the report. Student B had F in Biology but A+ in Physics so I want to show Student B as well. However, Student C had a DP in Chemistry but no F in any class so I do not want to show Student C....Hope that helped 🙁
August 13, 2008 at 4:07 pm
Thats better. Thanks Sam. So what you really want to display is any student who has had an F in any class?
I'm taking off for the day, but I'll check back in in the morning. Can you post some sample data that matches the query that you have?
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 13, 2008 at 4:23 pm
Thanks John. I appreciate the link as well. I will from now use that as a guide whenever I post any T-SQL statements.
sample data
Student ID Class ID Grade
[highlight=#ffff11]1024149 HIST01 DP[/highlight] --this should not show up
1024153 HMYS01 W --good
1024153 PHYS01 W--good
1024153 MATH91 W--good
1024153 SSYW99 F--good
1025417 DFAG71 DP--good
1025417 DFAG71 WF--good
1025417 DFAG71 FAIL--good
August 14, 2008 at 7:14 am
Hi John,
good morning. I just wanted to touch base with you this morning. I have been able to solve my own puzzle after I did some extensive researching.
What I did was; I dumped my resultset into a temp table, and then queried the temp table for student that had ('ANY'-keyword) 'Fs' or 'FAILs'.
I appreciate all your help.
Have a great day.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply