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
