3 of 3 people found this helpful
You're close. You need a data scaffold and you've done part of that by using the right join to the calendar, but that only gets you part of the way there. You also need a list of your unique employees, which I'm calling Employee Master. Once you have those, perform a cross join of the Employee Master and Calendar data. This will give you one row for each employee for each date. You can do a cross join using a 1=1 join calculation as shown below. I also go into these a bit in the following blog: https://www.kenflerlage.com/2018/09/sql-part2.html
Then you can add in your actual employee data and do a left join based on the employee name from the Employee Master and the Date from the Calendar:
Then, on a new sheet, you can filter to show only values where the Date from the employee data is null (I've called this field Date (Employee Data) ). This will basically filter down to any records from the scaffold (the list of dates for each employee) that are not in your actual employee data (i.e. the missing dates).
Then simply add the User (from the Employee Master) and the Date (from the Calendar) to the view:
Thanks A lot.
really appreciates the help