This is getting the best of me!!
I created calendar table (table1) loaded with all days for a period of 5 years. Among other things the table includes the date, the day type (weekend, holiday etc.) Important fields here are table1.date and table1.type
I have a second table (table2) that records days worked. Important fields here are table2.date and table2.employee
I want to identify which dates were not worked by an employee excluding any days that might be a weekend or holiday since those are not required work days.
table1 ======= 2/10/20 Weekday 2/11/20 Weekday 2/12/20 Weekday 2/13/20 Weekday 2/14/20 HOLIDAY 2/15/20 Weekend 2/16/20 Weekend table2 ======= 2/10/20 john 2/10/20 jimmy 2/10/20 mary 2/11/20 john 2/11/20 jimmy 2/11/20 mary 2/12/20 john 2/12/20 mary 2/13/20 jimmy 2/13/20 mary 2/14/20 jimmy
Query result should show days that do not exist in table2.
Jimmy 2/12/20 John 2/13/20