Attendance and leave of employees
-
Greetings, I have 3 tables respectively called "Employee_Personal_Info", "Employee_Job_Info" and "Attend_and_Leave_Of_Staff". The Last table contains a log from the fingerprint device and its columns are [Record Number], [Machine ID], [Employee ID], [In/Out Mode] which contains only 0 for attend and 1 for leave, [Verify Mode] which contains only 1 for fingerprint and 15 for face, [Date] and [Time] Columns concern me here within those tables are 1- Employee_Personal_Info: [Employee ID], [First Name], [Middle Name], [Last Name] and [Family Name] 2- Employee_Job_Info: [Job title], [Shift ID] 3- Attend_And_Leave_Of_Staff: [Date], [Time] We have 2 work shifts one that starts and ends in the same date begin at 8:00 AM and ends at 4:00 PM and the other begin at 6:30 PM and ends on the next day at 8:00 AM. I need to create a SQL query that returns me all employees registered on a specific work shift with their attend and leave time. That is it when selecting a specific type of shift the query returns me all the employees registered in such shift and beside each one his attend/leave date/time and returns null if one of the date/time is not found and null for both if there is no attendance data for him. I know it may be a complex query specially when dealing with employees registered in the second shift that starts at a day and ends on another one. I will be grateful for any ideas or any help
-
Greetings, I have 3 tables respectively called "Employee_Personal_Info", "Employee_Job_Info" and "Attend_and_Leave_Of_Staff". The Last table contains a log from the fingerprint device and its columns are [Record Number], [Machine ID], [Employee ID], [In/Out Mode] which contains only 0 for attend and 1 for leave, [Verify Mode] which contains only 1 for fingerprint and 15 for face, [Date] and [Time] Columns concern me here within those tables are 1- Employee_Personal_Info: [Employee ID], [First Name], [Middle Name], [Last Name] and [Family Name] 2- Employee_Job_Info: [Job title], [Shift ID] 3- Attend_And_Leave_Of_Staff: [Date], [Time] We have 2 work shifts one that starts and ends in the same date begin at 8:00 AM and ends at 4:00 PM and the other begin at 6:30 PM and ends on the next day at 8:00 AM. I need to create a SQL query that returns me all employees registered on a specific work shift with their attend and leave time. That is it when selecting a specific type of shift the query returns me all the employees registered in such shift and beside each one his attend/leave date/time and returns null if one of the date/time is not found and null for both if there is no attendance data for him. I know it may be a complex query specially when dealing with employees registered in the second shift that starts at a day and ends on another one. I will be grateful for any ideas or any help
I don't know if I understand what output you wanted, but I tried :) I pretend that the ist the EmployeeID in every table, otherwise you can't join data (and you can't know who belongs to the data). There are other columns missing, too. Look at Attend_And_Leave_Of_Staff - where do you know, to which shiftId the date and time belongs? Just a date and a time column doesn't make sense to me.
SELECT a.FirstName, a.FamilyName, b.JobTitle, c.???
FROM Employee_Personal_Info a INNER JOIN Employee_Job_Info b ON a.EmployeeID = b.EmployeeID
LEFT JOIN Attend_And_Leave_Of_Staff c ON a.EmployeeID = c.EmployeeID AND b.ShiftId = c.ShiftId
WHERE b.ShiftId = @shiftIdToLookUpI can't go further because of missing data, but that I changed the second join to LEFT JOIN thanks to Andrei Straut.
------------------------------ Author of Primary ROleplaying SysTem How do I take my coffee? Black as midnight on a moonless night. War doesn't determine who's right. War determines who's left.
-
I don't know if I understand what output you wanted, but I tried :) I pretend that the ist the EmployeeID in every table, otherwise you can't join data (and you can't know who belongs to the data). There are other columns missing, too. Look at Attend_And_Leave_Of_Staff - where do you know, to which shiftId the date and time belongs? Just a date and a time column doesn't make sense to me.
SELECT a.FirstName, a.FamilyName, b.JobTitle, c.???
FROM Employee_Personal_Info a INNER JOIN Employee_Job_Info b ON a.EmployeeID = b.EmployeeID
LEFT JOIN Attend_And_Leave_Of_Staff c ON a.EmployeeID = c.EmployeeID AND b.ShiftId = c.ShiftId
WHERE b.ShiftId = @shiftIdToLookUpI can't go further because of missing data, but that I changed the second join to LEFT JOIN thanks to Andrei Straut.
------------------------------ Author of Primary ROleplaying SysTem How do I take my coffee? Black as midnight on a moonless night. War doesn't determine who's right. War determines who's left.
Maybe you should go with a LEFT JOIN on Attend_And_Leave_Of_Staff? He said he wanted to see the null values for when records are not found, and your inner join will simply disregard (not display at all) the rows that have no correspondence. EDIT: I've upvoted your solution, as it seemed to be what the OP wanted.
Full-fledged Java/.NET lover, full-fledged PHP hater. Full-fledged Google/Microsoft lover, full-fledged Apple hater. Full-fledged Skype lover, full-fledged YM hater.
-
Maybe you should go with a LEFT JOIN on Attend_And_Leave_Of_Staff? He said he wanted to see the null values for when records are not found, and your inner join will simply disregard (not display at all) the rows that have no correspondence. EDIT: I've upvoted your solution, as it seemed to be what the OP wanted.
Full-fledged Java/.NET lover, full-fledged PHP hater. Full-fledged Google/Microsoft lover, full-fledged Apple hater. Full-fledged Skype lover, full-fledged YM hater.
Andrei Straut wrote:
Maybe you should go with a LEFT JOIN on Attend_And_Leave_Of_Staff?
Of course! You are absolutely right! Thanks! :rolleyes:
------------------------------ Author of Primary ROleplaying SysTem How do I take my coffee? Black as midnight on a moonless night. War doesn't determine who's right. War determines who's left.
-
Andrei Straut wrote:
Maybe you should go with a LEFT JOIN on Attend_And_Leave_Of_Staff?
Of course! You are absolutely right! Thanks! :rolleyes:
------------------------------ Author of Primary ROleplaying SysTem How do I take my coffee? Black as midnight on a moonless night. War doesn't determine who's right. War determines who's left.
You're welcome :)
Full-fledged Java/.NET lover, full-fledged PHP hater. Full-fledged Google/Microsoft lover, full-fledged Apple hater. Full-fledged Skype lover, full-fledged YM hater.