SQL Server 2008 Attendance report for every day of a month

Question

I have two tables, one is called ATTENDANCE and the other is called DATES.

In the ATTENDANCE I have rows like this:

Name | when       | Entry | Exit 
-----+------------+-------+------
Bob  | 2019-01-02 | 08:00 | 16:00
Bob  | 2019-01-04 | 09:00 | 17:00
Bob  | 2019-01-05 | 07:00 | 13:00

and in the DATES table

DATE
----------
2019-01-01
2019-01-02
2019-01-03
...

What I need is a report like this when selecting Bob for month January:

date       | entry | exit
-----------+-------+-----
2019-01-01 | null  | null
2019-01-02 | 08:00 | 16:00
2019-01-03 | null  | null
2019-01-04 | 09:00 | 17:00
2019-01-05 | null  | null
2019-01-06 | 07:00 | 13:00
2019-01-05 | null  | null
...

I tried with left join like this:

select date, entry, exit 
from DATES
left join ATTENDANCE on date = when 
where name = 'Bob' 
  and month(date) = (select month('2019-01-01'))

But I get only this:

 2019-01-02 | 08:00 | 16:00
 2019-01-04 | 09:00 | 17:00
 2019-01-05 | 07:00 | 13:00

Can please someone help me?

Thank you regards

Answer 1

You are close. You just need to move the filtering on the second table to the on condition:

select d.date, a.entry, a.exit
from DATES d left join
     ATTENDANCE a
     on d.date = a.when and a.name = 'Bob' 
where MONTH(d.date) = MONTH('2019-01-01') and
      YEAR(d.date) = YEAR('2019-01-01')

Notes:

• Use table aliases. The recommended aliases are abbreviations for the table name.
• Qualify all column names, so you know what table they come from.
• No subquery is needed to extract the month from a constant.
• You should include the year as well as the month.

A better way to write the date condition is:

where d.date >= '2019-01-01' and
      d.date < '2019-01-01' + interval 1 month

This is better because the optimizer can use an index on date if one is available.