Need help with how to write diffcult Query
-
Hi,
I am trying to write a query to get results from two tables and for some reason I just can't figure out how to get the results I need. I am about ready to jump off a building, so I figured it would be better to ask for help instead.
Here are examples of the tables I am working with
Employees Table
-EmployeeID
-First Name
-Last Name
MeetingAttendance Table
-MeetingAttendanceID
-MeetingName
-MeetingDate
-MeetingID
-EmployeeID
Using these two tables I can keep track of what meetings a given employee has attended, which is working great.
Here is the problem. I need to write a query that will return all employees that haven't attended a given meeting between specified dates. For example, if I have a company meeting planned on Tuesdays, I need to write a query that will cross reference the Employees Table and the MeetingAttendanceTable and tell me all employees that don't have an attendance record for the past two weeks for the MeetingID specified.
Can anyone help me with this?
Thanks!
-
If I am reading it correctly
Employees: list of all employees
MeetingAttendance: all employees present at a meeting
Where is the reference of who needs to be present?
-
Good, point... the least we need is the list of the meetings (assuming, everyone has to attend to every meeting).
-
Ok, sorry about not providing enough detail at first. Thanks for the quick replies.
So, the query I need assumes that all employess have to attend the specified meeting. So the parameters that I would send to the query would be something like this:
@StartDate = 10/13/2005
@EndDate = 10/27/2005
@MeetingID = 1
Sending in these parameters I would want to receive back all Employees that didn't attend MeetingID 1 between the StartDate and the EndDate (MeetingID references a Meetings table, and lets just say that MeetingID 1 is an all company meeting so ALL Employees in the Employees table are required to attend.)
Does that provide enough information?
Thanks Again!!!!
Aaron
-
quite late here, there is probably a better query.
IF EXISTS(SELECT *
from MeetingAttendance
WHERE MeetingID=@MeetingID
AND MeetingDate BETWEEN @StartDate AND @EndDate
)
BEGIN
select employees.EmployeeID
from MeetingAttendance /*AVAILABLE MEETINGS*/
WHERE MeetingID=@MeetingID
AND MeetingDate BETWEEN @StartDate AND @EndDate
right join employees
on employees.EmployeeID=MeetingDate.EmployeeID
and employees.employeeid IS NULL /*NOT PRESENT*/
END
ELSE /*there was no meeting*/
-
Assuming there is an MeetingDate column, can it be as simple as this?
SELECT * FROM Employees
WHERE EmployeeID NOT IN
(SELECT EmployeeID
FROM MeetingAttendance
WHERE MeetingID=@MeetingID
AND MeetingDate BETWEEN @startdate and @enddate
)
Ideally, there should a Meetings table to store the details of a meeting (such as room, name, speaker, date/time, etc) to remove redundancy. MeetingAttendance should just contain EmployeeID and MeetingID to resolve the many-many relationship between Meetings and Employees.
Regards,
JA
-
This query should return only employees that is not in the selected MeetingAttendance range.
Select A.*
from
Employees A Left outer join
MeetingAttendance B
ON
A.EmployeeID = B.EmployeeID
AND A.Meeting_ID = @Meeting_ID AND b.MeetingDate BETWEEN @startdate and @enddate
WHERE
B.EmployeeID IS NULL
-
select e.employeeID,e.firstName,e.lastName,m.MeetingID
from employees e
left outer join meetingAttendance m
on e.employeeID=m.employeeID
where meetingDate between @StartDate and @EndDate
and MeetingAttendanceID is null
-
WOW... thank you everyone who replied. I tried a few of the different solutions and they all seemed to work! Thank you so much for your time and your help, it is greatly appreciated!!!
Aaron
-
Select * from Employees_Table where employeeid not in (Select employeeid from employee_Table a , MeetingAttendance b where a.employeeid = b.employeeid and meetingid = @meetingid and meetingdate between @fromdate and @todate)
-
Select * from Employees_Table where employeeid not in (Select employeeid from employee_Table a , MeetingAttendance b where a.employeeid = b.employeeid and meetingid = @meetingid and meetingdate between @fromdate and @todate)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply