CFQUERY: All employees who haven't entered data...
Hey all, Is there a simple SQL statement that would allow me to select all the employees...or all the EmpIds that are NOT in the Master table.. So I have tblEmployees and tblMaster. Basically I want to display all the bad bad people that did not enter their time for a specific period...an exception report. I can do this easily without factoring a time period, but once I put the Time Period in..it no longer works.. Without Time Period: CFQUERY Name=GetExceptions datasource=#DSN# SELECT tblEmployees.EmpName, tblEmployees.DeptID FROM tblEmployees LEFT JOIN tblMaster ON tblEmployees.ID = tblMaster.empid WHERE tblMaster.empid Is Null /CFQUERY This just shows Employees who have never entered time at all. That isn't exactly what I want. With Time Period CFQUERY Name=GetExceptions datasource=#DSN# SELECT tblEmployees.EmpName, tblEmployees.DeptID FROM tblEmployees LEFT JOIN tblMaster ON tblEmployees.ID = tblMaster.empid WHERE tblMaster.startdate = #CreateODBCDate(enddate)# AND tblMaster.startdate = #CreateODBCDate(startdate)# AND tblMaster.empid Is Null /CFQUERY I want this to show Employees that haven't entered time for a specific period... I know my SQL is confused somewhere along the line...can anyone help? -Angel __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: CFQUERY: All employees who haven't entered data...
Use a subquery: CFQUERY Name=GetExceptions datasource=#DSN# SELECT tblEmployees.EmpName, tblEmployees.DeptID FROM tblEmployees WHERE tblEmployees.ID NOT IN ( SELECT tblMaster.empid FROM tblMaster WHERE tblMaster.startdate = #CreateODBCDate(enddate)# AND tblMaster.startdate = #CreateODBCDate(startdate)# ) /CFQUERY Is there a simple SQL statement that would allow me to select all the employees...or all the EmpIds that are NOT in the Master table.. So I have tblEmployees and tblMaster. Basically I want to display all the bad bad people that did not enter their time for a specific period...an exception report. I can do this easily without factoring a time period, but once I put the Time Period in..it no longer works.. Without Time Period: CFQUERY Name=GetExceptions datasource=#DSN# SELECT tblEmployees.EmpName, tblEmployees.DeptID FROM tblEmployees LEFT JOIN tblMaster ON tblEmployees.ID = tblMaster.empid WHERE tblMaster.empid Is Null /CFQUERY This just shows Employees who have never entered time at all. That isn't exactly what I want. With Time Period CFQUERY Name=GetExceptions datasource=#DSN# SELECT tblEmployees.EmpName, tblEmployees.DeptID FROM tblEmployees LEFT JOIN tblMaster ON tblEmployees.ID = tblMaster.empid WHERE tblMaster.startdate = #CreateODBCDate(enddate)# AND tblMaster.startdate = #CreateODBCDate(startdate)# AND tblMaster.empid Is Null /CFQUERY I want this to show Employees that haven't entered time for a specific period... I know my SQL is confused somewhere along the line...can anyone help? __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: CFQUERY: All employees who haven't entered data...
use subquery. either NOT IN or WHERE NOT EXISTS On Mon, 30 Sep 2002, Angel Stewart wrote: Hey all, Is there a simple SQL statement that would allow me to select all the employees...or all the EmpIds that are NOT in the Master table.. So I have tblEmployees and tblMaster. Basically I want to display all the bad bad people that did not enter their time for a specific period...an exception report. I can do this easily without factoring a time period, but once I put the Time Period in..it no longer works.. Without Time Period: CFQUERY Name=GetExceptions datasource=#DSN# SELECT tblEmployees.EmpName, tblEmployees.DeptID FROM tblEmployees LEFT JOIN tblMaster ON tblEmployees.ID = tblMaster.empid WHERE tblMaster.empid Is Null /CFQUERY This just shows Employees who have never entered time at all. That isn't exactly what I want. With Time Period CFQUERY Name=GetExceptions datasource=#DSN# SELECT tblEmployees.EmpName, tblEmployees.DeptID FROM tblEmployees LEFT JOIN tblMaster ON tblEmployees.ID = tblMaster.empid WHERE tblMaster.startdate = #CreateODBCDate(enddate)# AND tblMaster.startdate = #CreateODBCDate(startdate)# AND tblMaster.empid Is Null /CFQUERY I want this to show Employees that haven't entered time for a specific period... I know my SQL is confused somewhere along the line...can anyone help? -Angel __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: CFQUERY: All employees who haven't entered data...
Thanks Al! :) That worked nicely Thank you very much *nod* *nod* I was thinking something else though... can't subqueries take the place of all those different joins then, if one wanted to use them for some reason? -Gel -Original Message- From: Everett, Al [mailto:[EMAIL PROTECTED]] Sent: Monday, September 30, 2002 11:04 AM To: CF-Talk Subject: RE: CFQUERY: All employees who haven't entered data... Use a subquery: CFQUERY Name=GetExceptions datasource=#DSN# SELECT tblEmployees.EmpName, tblEmployees.DeptID FROM tblEmployees WHERE tblEmployees.ID NOT IN ( SELECT tblMaster.empid FROM tblMaster WHERE tblMaster.startdate = #CreateODBCDate(enddate)# AND tblMaster.startdate = #CreateODBCDate(startdate)# ) /CFQUERY Is there a simple SQL statement that would allow me to select all the employees...or all the EmpIds that are NOT in the Master table.. So I have tblEmployees and tblMaster. Basically I want to display all the bad bad people that did not enter their time for a specific period...an exception report. I can do this easily without factoring a time period, but once I put the Time Period in..it no longer works.. Without Time Period: CFQUERY Name=GetExceptions datasource=#DSN# SELECT tblEmployees.EmpName, tblEmployees.DeptID FROM tblEmployees LEFT JOIN tblMaster ON tblEmployees.ID = tblMaster.empid WHERE tblMaster.empid Is Null /CFQUERY This just shows Employees who have never entered time at all. That isn't exactly what I want. With Time Period CFQUERY Name=GetExceptions datasource=#DSN# SELECT tblEmployees.EmpName, tblEmployees.DeptID FROM tblEmployees LEFT JOIN tblMaster ON tblEmployees.ID = tblMaster.empid WHERE tblMaster.startdate = #CreateODBCDate(enddate)# AND tblMaster.startdate = #CreateODBCDate(startdate)# AND tblMaster.empid Is Null /CFQUERY I want this to show Employees that haven't entered time for a specific period... I know my SQL is confused somewhere along the line...can anyone help? __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
