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: 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)# ) > 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: > > > > SELECT tblEmployees.EmpName, tblEmployees.DeptID > FROM tblEmployees LEFT JOIN tblMaster ON tblEmployees.ID = > tblMaster.empid > WHERE tblMaster.empid Is Null > > > This just shows Employees who have never entered time at all. > That isn't > exactly what I want. > > With Time Period > > > > 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 > > > 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
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: > > > > SELECT tblEmployees.EmpName, tblEmployees.DeptID > FROM tblEmployees LEFT JOIN tblMaster ON tblEmployees.ID = > tblMaster.empid > WHERE tblMaster.empid Is Null > > > This just shows Employees who have never entered time at all. That isn't > exactly what I want. > > With Time Period > > > > 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 > > > 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...
Use a subquery: 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)# ) > 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: > > > > SELECT tblEmployees.EmpName, tblEmployees.DeptID > FROM tblEmployees LEFT JOIN tblMaster ON tblEmployees.ID = > tblMaster.empid > WHERE tblMaster.empid Is Null > > > This just shows Employees who have never entered time at all. > That isn't > exactly what I want. > > With Time Period > > > > 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 > > > 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
