[SQL] How to find records that doesn't have 1 to 1 match between a schedule and timecard table...

I have a schedule table and a timecard table and i want to find all records from the schedule table when the corresponding record is not found in the timecard table.

For example the schedule table has 5 records starting from 9/30 to 10/5

ID START DATE END DATE START TIME END TIME
123456 2024-09-30 2024-09-30 8:00 16:00
123456 2024-10-01 2024-10-01 8:00 16:00
123456 2024-10-02 2024-10-02 8:00 16:00
123456 2024-10-03 2024-10-03 8:00 16:00
123456 2024-10-04 2024-10-04 8:00 16:00

the timecard table looks like this

ID PUNCH DATE PUNCH IN PUNCH OUT
123456 2024-09-30 7:59 16:01
123456 2024-10-01 7:59 16:01
123456 2024-10-02 7:59 16:01
123456 2024-10-04 7:59 16:01

I've tried the following script, but I can't seem to report just the missing 10/3 record

SELECT *
FROM schedule s
WHERE NOT EXISTS (
SELECT 1
FROM timecard t
WHERE t.id = s.id
AND [t.punch date] BETWEEN '2024-09-30' AND '2024-10-05'
);
and [s.start date] BETWEEN '2024-09-30' AND '2024-10-05'


this gives me date if no records were found (not even one) in the timecard table (which is good, but doesn't serve my purpose of finding just the 10/3 record), so i also tried the following script and that also doesn't help

SELECT s.*
FROM schedule s
LEFT JOIN timecard t ON s.id = t.id AND [t.punch date] BETWEEN '2024-09-30' AND '2024-10-05'
WHERE t.id IS NULL OR [t.punch date] IS NULL;

Continue reading...