SQL Server - How to only join when other attribute between select

Question

I am trying to work up a query to show a students attendance records. These are my tables.

This is what I have started so far...

I am matching a list of studentId's (StudentCalendar) to days school was open (DateCollection) then to records of missed days (Attendance).

I end up with something like this..

 SELECT s.personid,
        dc.calendarid, 
        dc.[date], 
        coalesce(absentMinutes,0) AS absentMinutes
   FROM [dbo].[StudentCalendar] s
        INNER JOIN [dbo].[DateCollection] dc ON s.calendarId = dc.calendarId
         LEFT JOIN [dbo].[Attendance] a ON a.dateOccurred = dc.[date]
               AND a.personId = s.personId

I now have a list of students with every day school was open. My problem is that not every student was signed up for the full school year.

Is there a way for me to only match the list of studentid's (StudentCalendar) with days school was open (DateCollection) when it's within the range given in a students calendar startDate & endDate (Calendar) for each student?

something like this..

Answer 1

This should do it for you:

SELECT s.personid,
    dc.calendarid, 
    dc.[date], 
    coalesce(absentMinutes,0) AS absentMinutes
FROM [dbo].[StudentCalendar] s
    INNER JOIN [dbo].[DateCollection] dc ON s.calendarId = dc.calendarId
    INNER JOIN [dbo].[Attendance] a ON a.dateOccurred = dc.[date]
           AND a.personId = s.personId
           AND dc.[date] BETWEEN s.startDate AND isnull(s.endDate,'99991231')