I have an employee table (empl), and a labor table (lab). The labor table is populated whenever an employee clocks in/out. If an employee does not clock in, they do not appear in the labor table. I need to capture employees whose time is less than 8 hours, or whoever did not log in at all that date. If I run the following query without a WHERE clause, I get all of my employees, with NULL data in the labor table if they did not log in that day. This is good.However, if I add WHERE (DATEDIFF(s, tt.StartTime, tt.EndTime) < 28800) OR (tt.StartTime = NULL) I get all employees whose time is less than 8 hours, but do not get any of the employees who did not work at all that date, and that's expected... there should NEVER be a NULL in the labor table - it is populated whenever somebody clocks in/out.So, how would I query the resulting table to get those that worked less than 8 hours, and those that did not? select et.EmpNo et.FirstName, et.LastName, tt.StartTime, tt.EndTime, DATEDIFF(s, tt.StartTime, tt.EndTime) as SecondsFROM (select empl.EmpNum as EmpNo, empl.FFName as FirstName, empl.FLName as LastName from empl where empl.ftermdate IN ('1900-01-01 00:00:00.000') AS etLEFT JOIN (select MIN(lab.StartDt) as StartTime, MAX(lab.EndDt) as EndTime, lab.WorkDt as WorkDate, lab.EmpNum as EmpNo from lab where lab.WorkDt in ('2008-03-03 00:00:00.000') group by lab.WorkDT, lab.EmpNum) AS TTON et.EmpNo = tt.EmpNo
How would I get a subset of this?
Posted - 2008-04-30 : 16:43:37
