I have 3 tables in my database HR_Employee_Personal_Info, HR_Employee_Job_Info and HR_Attend_And_Leave_Of_Staff respectively, I want to get the attendance of each employee based on his work shift. I will mention the columns within each table which will be of interest in order to do the job.
Table HR_Employee_Personal_Info: [Employee ID], [Employee Name]
Table HR_Employee_Job_Info: [Employee ID], [Job Title], [Shift ID]
Table HR_Attend_And_Leave_Of_Staff: [Employee ID], [Verify Mode] which will be 1 for fingerprint and 15 for face, [In/Out Mode] which will be 0 for attend and 1 for leave, [Date], [Time]
I have 2 work shifts one begin at 8:00:00 AM and end on the same day at 4:00:00 PM and the other begin at 6:30:00 PM and end on the next day at 8:15:00 AM, 8:15:00 to allow workers of the day shift to put their face & fingerprint, I have a datagridview , 2 datetimepickers and a combobox contains the shift names I have. All what I want that after I select specific days within the datetimepickers and select the shift and press a button I retrieve attendance of only those employees in that shift within the table of attendance log I mentioned above Table No. 3. The datagridview showed as follows
[Employee ID], [Employee Name], [Job Title], [Attendance Data]. [Attendance Time], [Leave data], [Leave Time]
So, I want to generate a query or number of quries that is when executed return me that data of employee that way looks in the datagridview returns NULL if it found one or both of [Attendance Time], [Leave Time] has no value within the Attendance Log Table (Table No. 3) I hope that I explained my problem well and the idea of the problem becomes clear.
Thanks in advance. Best Regards.