Extract data by condition across table

1

Hi,
I would like to ask for your help...
I have a single table with employees (numbers) and their working days (dates).
I need to extract only the employees that worked BOTH in Date [X] and in Date [Y] (that I will indicate in my query).
I need to solve this in 2 different ways...
What are the easiest ways to solve this?

Thanks in advance :slight_smile:

2

Hi hope this helps

( please bear in mind there is a OCEAN of things in this .. mine is just a small drop .. )
( not interested in swimming the OCEAN )

drop table Employees

create table Employees
(
Employee_Id int ,
Working_Day date 
)

insert into Employees select 1,'2012-10-05'
insert into Employees select 1,'2012-10-07'
insert into Employees select 1,'2012-10-10'

insert into Employees select 2,'2012-10-05'
insert into Employees select 2,'2012-10-07'
insert into Employees select 2,'2012-10-21'

insert into Employees select 3,'2012-10-12'
insert into Employees select 3,'2012-10-14'
insert into Employees select 3,'2012-10-25'

select 'Sample Data', * from Employees

select Employee_Id from Employees where Working_Day = '2012-10-05'
intersect
select Employee_Id from Employees where Working_Day = '2012-10-07'

image
image493×519 7.12 KB

1 Like
3

Thanks a lot @harishgg1 for your great help! :slight_smile:
I tried so many ways to solve it with no success, INCLUDING your suggested INTERSECT solution here.
Probably I didn't managed to apply it successfully since besides the Employee_ID, I've added to the SELECT the Working_Day column as well to be shown as part of the output. Now I understand this is not possible in this kind of query... right?

By the way, can you think of an additional simple way to solve it besides INTERSECT?

Thanks a lot!

4 
 SELECT employee_id,
       working_day
FROM   employees
WHERE  employee_id IN (SELECT employee_id
                       FROM   employees
                       WHERE  working_day = '2012-10-05'
                              AND employee_id IN (SELECT employee_id
                                                  FROM   employees
                                                  WHERE
                                  working_day = '2012-10-07'))
1 Like
5

Again, Thanks a lot @harishgg1. It works perfect! :slight_smile:

Just to understand one issue... in this option I see that you did add the Working_day (together with the Employee_ID) to be shown in the output table, so why is it not applicable in your INTERSECT solution? I've tried to add there the DATE and I got an empty table as the output. Only afer removing the DATE from the select and leaving only the ID to be shown, I got the expected result...

6

INTERSECT

if you add Working_Day to INTERSECT the meaning and results change completely

You can add a outer select to the intersect and join to the table to get the Working Days along with ID

If you want me to explain to you using Excel Diagrams = please let me know

1 Like
7

Thanks @harishgg1.

This is good enough for now.

Thanks a lot :slight_smile:

8 
SELECT Employee_Id
FROM dbo.Employees
GROUP BY Employee_Id
HAVING MAX(CASE WHEN Working_Day = '2012-10-05' THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN Working_Day = '2012-10-07' THEN 1 ELSE 0 END) = 1
1 Like
9

Thanks a lot @ScottPletcher! It works perfect :slight_smile:

10

You're welcome!

HAVING is the least understood and most underused option of a SELECT statement.