SELECT S.stu开发者_StackOverflow中文版dentID, S.studentFName, S.DOB, S.studentEMail, S.studentAddress
FROM Student AS S RIGHT OUTER JOIN
(SELECT Attendance.studentID, COUNT(*) AS cnt FROM Attendance CROSS JOIN
CourseUnit WHERE (Attendance.attStatus = 'Yes') AND (CourseUnit.courseCode = 'S3000') AND (CONVERT(VARCHAR, Attendance.attDate, 101) < '11/10/2010') GROUP BY Attendance.studentID
HAVING (COUNT(*) < 5)) AS A ON A.studentID = S.studentID
Everything works, except the attDate
. I dont have any records in 2010, and it still brings up all of them ??
Let me guess - your dates are a varchar? Fired. Any reason for that ugly ugly ugly unperformant and error prone construct
(CONVERT(VARCHAR, Attendance.attDate, 101) < '11/10/2010')
instead of Attendance.attDate being a Date object to start with? Try that and see whether that works.
Aren't you saying 'give me all dates less than this day in 2010' (< '2/10/2010'
)?
Maybe you need to switch around your <
to a >
.
Attendance.attDate
should be of data type datetime
and the comparison should be Attendance.attDate < '20101110'
(YYYYMMDD) if you want the rows prior to the given date value.
精彩评论