CODEWITHSUNDEEP
sqlsql-server
JonDog
JonDog

Reputation: 555

SQL Server Query - Joining individual values with a full list of possible values

I couldnt think of a very good title so let me explain.

I have a list of days that students were absent (dbo.Attendance).

absent

I also have a list of days that school was open (dbo.DateCollection).

school days

My question is.. Is there some why for me to join/combined the two table into a list that shows each day for each student with their attendance? something like this..

Any help or direction would be well appreciated!!

enter image description here

Upvotes: 0

Views: 96

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Yes. You can do this by joining the tables. In this case, it is a bit tricky, because you have to start with a row for all students on all days, and then see if the student was present. Let me assume that you have a Students table:

select s.personid, c.calendarid, c.date, coalesce(a.absentMinutes, 0) as absentMinutes
from Calendar c cross join
     Students s left outer join
     Attendance a
     on a.dateOccurred = c.date and
        a.personid = s.personid;

If you don't have students table, then you have a problem. You can only report on students that are in the Attendance table. You could do this as:

select s.personid, c.calendarid, c.date, coalesce(a.absentMinutes, 0) as absentMinutes
from Calendar c cross join
     (select distinct personid from Attendance) s left outer join
     Attendance a
     on a.dateOccurred = c.date and
        a.personid = s.personid;

Upvotes: 3

JonDog
JonDog

Reputation: 555

Thanks for all the input.

I ended up using a bridge table for students as well.. This is what I have got working for me.

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

Upvotes: 0

Related Questions