BigQuery - How do I check if data is missing from a table for a specific calendar date?

I have a table containing ascending calendar dates. I have another table containing employee time sheets. The employee time sheet table only inserts a row if a time sheet has been created. What I am trying to find is for X date, did the employee complete a time sheet?

My latest query is below. Jumping back into SQL after a couple years off and I'm certain it's something simple that I'm missing:

SELECT 
D. CALENDAR_DATE,
E. EMPLOYEE, 
ROW_NUMBER () OVER (PARTITION BY E. EMPLOYEE, E. CALENDAR_DATE ORDER BY D.CALENDAR_DATE ASC) AS NUMBER OF TIMESHEETS
FROM DIM.CALENDAR D
LEFT JOIN EMPLOYEE_TIMESHEETS E ON E.TIMESHEET_DATE = D.CALENDAR_DATE
WHERE
CALENDAR_DATE BETWEEN '2023-04-01' AND '2023-07-19'
ORDER BY CALENDAR_DATE ASC

Desired Results:

Current Output:

You could use a cartesian join and conditional aggregation with your current layout.

create table calendar (
  calendar_date date
  );
  
insert into calendar values 
('2023-04-01'), 
('2023-04-02'),
('2023-04-03'),
('2023-04-04'),
('2023-10-01');

create table employee_timesheets (
  employee varchar(10), 
  timesheet_date date
  );
  
insert into employee_timesheets values 
('Jerry', '2023-04-01'), 
('Jerry', '2023-04-02'), 
('Tom', '2023-04-03'), 
('Sarah', '2023-04-01'), 
('Sarah', '2023-04-02'),
('Sarah', '2023-04-04'), 
('Sarah', '2023-10-01');

select d.calendar_date, e.employee,
  max(case when d.calendar_date = e.timesheet_date then 1 else 0 end) nbr_of_timesheets
from calendar d, employee_timesheets e
where d.calendar_date between '2023-04-01' and '2023-07-19'
group by 1,2
order by 2,1;

View on DB Fiddle

Using your current design, you really should have 3 tables. One for employees, one for calendar dates, and one for the timesheet date. The timesheet table should simply reference an employee ID, and not store the employee name.

The sample query below will display all dates in the range provided, showing ALL employees and any hours they may have had. If there were no hours worked, the employee will still show. If nobody worked on a specified date, the date will still show.

SELECT c.calendar_date, e.name, ifnull(t.hours,0) hours 
  FROM calendar c 
    left join employee e on true 
	left join timesheet t on c.calendar_date = t.calendar_date and e.employee_id = t.employee_id 
WHERE c.calendar_date between '2023-08-01' and '2023-08-04' 
order by c.calendar_date, e.name

I'll also mention that having a calendar table that stores every possible working date is not the best design. The best solution would be to use some programming language such as PHP to create a data structure (array or multi-dimensional array) that contains all dates for the week. Then, you would query the database, merge the results into the data structure, and then output the data structure. Using a design like that you would not need to include a calendar table. But, if your goal is to write standard SQL, the above should be the best solution.

By the way, my table design is as follows.

'calendar' table
fields:
 calendar_date , date (primary key)

'employee' table
fields:
 employee_id , smallint (primary key)
 name , varchar(30) 
 hire_date , date 

'timesheet' table
fields: 
 timesheet_id , smallint (primary key)
 calendar_date , date 
 employee_id , smallint 
 hours , decimal