I am trying to create a report that shows the days a person did not show up for work. The difficulty comes from the fact that currently there is no record in the table that would show they where not here. Only the dates they are here show up in the table.
Here is an example of the current table.
Name Badge# Date Time (in minutes)
John Belushi 1005 09/01/15 600
John Belushi 1005 09/02/15 600
John Belushi 1005 09/03/15 600
John Belushi 1005 09/08/15 600
John Belushi 1005 09/10/15 600
John Belushi 1005 09/15/15 600
What I am trying to find from this is that this person was not here on 09/09/15 & 09/14/15. The reason for it just being those two days is because we work a 4 day week, so we are not here on Friday, Saturday and Sunday and 09/07 was a holiday.
I do not see a way to create a report just using this table. It would seem to me the easiest way would be to create a calendar table in SQL that shows all the dates and we could check the holidays and the weekends so we could ignore those form the report. But I am not sure how to create the sql table.
If someone can tell me either how to create the sql table or a way to create the report in Crystal without the calendar table. I would appreciate the help.