SQL Syntax

if there are only 3 plans ... find the employee_id with 3 distinct planid's

select * from employee
where employeeid in 
  (select e.employeeid
  from employee e  
    Inner Join Plan p ON e.EmployeeID = p.EmployeeID
      AND p.StartDate <= GETDATE()               
      AND (p.EndDate IS NULL OR p.EndDate > GETDATE() )
  group by e.employeeid
  having count(distinct p.planid) = 3)

Select allOpen in new window

I would do this with EXISTS clause to check for the existence of other plans for the same employee for the give period:

Select FirstName, LastName
From Employee e  
	Inner Join Plan p ON e.EmployeeID = p.EmployeeID
WHERE p.StartDate <= GETDATE()               
	AND (p.EndDate IS NULL OR p.EndDate > GETDATE())
	AND p.PlanID = 1 
	AND EXISTS (SELECT 1
		FROM Plan p2
		WHERE p2.StartDate <= GETDATE()               
			AND (p2.EndDate IS NULL OR p2.EndDate > GETDATE())
			AND p2.PlanID = 2 AND p2.EmployeeID = p.EmployeeID)
	AND EXISTS (SELECT 1
		FROM Plan p3
		WHERE p3.StartDate <= GETDATE()               
			AND (p3.EndDate IS NULL OR p3.EndDate > GETDATE())
			AND p3.PlanID = 2 AND p3.EmployeeID = p.EmployeeID)

Select allOpen in new window

Several ways to do this.  Probably the simplest is to get the employee/plan joined rows and then aggregate them.  Essentially wrapping your selection with the aggregation should do the trick.

Kent

SELECT firstname, lastname, count(*)
FROM
(
Select FirstName, LastName
From Employee e  
Inner Join Plan p ON e.EmployeeID = p.EmployeeID
AND p.StartDate <= GETDATE()               
AND (p.EndDate IS NULL OR p.EndDate > GETDATE())
WHERE p.PlanID in (1,2,3)
) t0
group by firstname, lastname
HAVING count(*) = 3;

Select allOpen in new window

Geert solution is not bad but doesn't really check for the required plan ids (1, 2, 3). If for example an employee has plans 1, 2, 4 it will be returned. I think it misses the PlanID filter.

Kent's solution also doesn't check if the employee has those 3 plans. If it has repeating values, like for example 1,1,1 it will be returned. If is not allowed for an employee to repeat the same plan then it should works.

vitor ... i commented.
if ... there are only 3 plans

sometimes comments are very important

it's accessing a lot less tables than your solution

if you really want a filter then just add
the required plans

select * from employee
where employeeid in 
  (select e.employeeid
  from employee e  
    Inner Join Plan p ON e.EmployeeID = p.EmployeeID
      AND p.StartDate <= GETDATE()               
      AND (p.EndDate IS NULL OR p.EndDate > GETDATE() )
  where p.planid in (1, 2, 3)
  group by e.employeeid
  having count(distinct p.planid) = 3)

Select allOpen in new window

Yes, with the filter you'll always guarantee the requirement and that's what my comment was about.
Cheers

Hi Vitor,

Yeah.  I assumed a rational data rule that an employee could be a member of a plan only once.


Kent

Select e.FirstName, e.LastName /*, ...*/
From (
    Select EmployeeID
    From [Plan]
    Where PlanID IN (1, 2, 3)
    Group By EmployeeID
    Having Max(Case When PlandID = 1 Then 1 Else 0 End) = 1 And
        Max(Case When PlandID = 2 Then 1 Else 0 End) = 1 And
        Max(Case When PlandID = 3 Then 1 Else 0 End) = 1
) AS p
Inner Join Employee e ON e.EmployeeID = p.EmployeeID

Here is the requirement:
Get all employees that are enrolled in all 3 plans (Medical, Dental and Vision) as of the
day the procedure is run.

There is a lookup table that has all types of plans
- Medical(1), Dental(2), Vision(3) and Medical2(4)

I dont think that table needs to be included because I know the PlanIDs and theres only 4 records

So bottom line is the employee needs to have either 1,2,3 or 2,3,4 to qualify? Also, dont forget the Dates and where you would include them in the proc.

My code works but I wanted to get other opinions or approaches on how you would do it.

Here is the Plan table data

ID	EmployeeID	PlanID	EnrollmentStartDate	EnrollmentEndDate
1	1	               1	                    2015-01-01	2015-03-01
2	1	               2	                    2014-01-01	NULL
3	2	               1	                    2013-06-01	2015-05-01
4	3	               4	                    2013-02-01	NULL
5	4	                2	                    2015-05-03	2015-07-01
6	1	               4	                     2015-03-02	NULL

Select allOpen in new window

Thanks for all the responses and help. I appreciate it all

JK

PS - noone qualifies so there wont be any data being brought back!! But that doesnt matter

Exactly what I was looking for Scott....Thank you

Thanks for your help guys.

CORRECTION:

Oops, one quick correction is needed, which you're probably already spotted, but just to be absolutely sure:

Select e.FirstName, e.LastName /*, ...*/
From (
    Select EmployeeID
    From [Plan]
    Where PlanID IN (1, 2, 3, 4) --<<-- add "4" to this list
    Group By EmployeeID
    Having Max(Case When PlandID IN (1, 4) Then 1 Else 0 End) = 1 And
        Max(Case When PlandID = 2 Then 1 Else 0 End) = 1 And
        Max(Case When PlandID = 3 Then 1 Else 0 End) = 1
) AS p
Inner Join Employee e ON e.EmployeeID = p.EmployeeID

Thanks Scott