0

I'm using DATEDIFF and IS NULL to find records missing data for a certain number of days, For example, here I'm trying to find students who haven't taken any classes for 1095 days (3 years), if they're a registered student they can't go more than 3 years without taking any classes. I think I'm doing this right, just making sure. As I'm learning, after the OUTER JOIN all columns return back and you filter columns you do/don't want in the WHERE.

SELECT DISTINCT
    stu.campusID AS [Campus ID],
    camp.name AS [School],
    stu.studentID AS [Student ID],
    stu.lName AS [Last Name],
    stu.fName AS [First Name]

FROM
    students stu
    LEFT OUTER JOIN
    coursesTaken cour
    ON
    cour.campusID = stu.campusID
    AND
    cour.studentID = stu.studentID
    AND
    DATEDIFF(D, cour.dateTaken, GETDATE()) >= 1095
    INNER JOIN
    campusNames camp
    ON
    camp.campusID = stu.campusID

WHERE
    cour.dateTaken IS NULL
Chris
  • 71
  • 1
  • 1
  • 14

1 Answers1

2

If you're wanting to find students that haven't taken any courses for the last 3 years, there's a couple of things I'd change around:

SELECT
    stu.campusID AS [Campus ID],
    camp.name AS [School],
    stu.studentID AS [Student ID],
    stu.lName AS [Last Name],
    stu.fName AS [First Name]

FROM
    students stu
      INNER JOIN
    campusNames camp
      ON
        camp.campusID = stu.campusID
WHERE
    NOT EXISTS (SELECT * FROM coursesTaken co
           where co.StudentID = stu.StudentID and
           co.DateTaken > DATEADD(year,-3,GETDATE())

Which hopefully reads more cleanly/closely to your spec. (You can change the DATEADD back to using 1095 days if that's the actual requirement, but if it's 3 years then the above is better because it takes leap years into account)

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • I forgot about NOT EXISTS, this method is easier to understand, to me, than an OUTER JOIN with a preserved side and a non-preserved side and figuring out which items go where. thanks! – Chris Mar 13 '14 at 14:24
  • This is also much better from a performance perspective. Not only do you get a performance boost from using the `NOT EXISTS` which is incredibly efficient. `DATEADD(year,-3,GETDATE())` Is calculated prior to runtime and therefore matching should provide a very efficient seek for DateTaken if that's indexed. – Zane Mar 13 '14 at 14:38
  • ok thanks Zane, finding students with no classes was just an example I was using, basically trying to find people x that have y and haven't had z for N days. like if you were admitted to the hospital and were not helped for days. The hospital may have a threshold of 7 days. – Chris Mar 14 '14 at 14:34
  • @Chris - the general point that Zane was making is that, whenever possible, you should do any date maths against constants or `GETDATE()` rather than doing it to your columns. If you do date maths with your columns, then any indexes on those columns are useless. – Damien_The_Unbeliever Mar 14 '14 at 14:38
  • @Damien_The_Unbeliever gotcha! making a note for future reference Thanks Again! – Chris Mar 14 '14 at 15:15