Home » SQL & PL/SQL » SQL & PL/SQL » Using the Sql query to find employees who were recruited on the same day or on neighboring days. (Oracle, 11g, Win 7)
Using the Sql query to find employees who were recruited on the same day or on neighboring days. [message #676483] Wed, 12 June 2019 01:56 Go to next message
orajav
Messages: 23
Registered: June 2019
Junior Member
Hello!

I need in the Sql-request to display the name, surname and date of commencement of work of employees who were hired on the same day or on neighboring days (for example, one employee left March 2, 2015, and the other on March 3, 2015).

Thanks.
Re: Using the Sql query to find employees who were recruited on the same day or on neighboring days. [message #676484 is a reply to message #676483] Wed, 12 June 2019 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 66446
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And your problem to write this query is?
We will help you to write your query but we won't do your homework for you.
Post what already tried and explain where you are stuck.

Clue: self-join on hire date +/- 1.

Re: Using the Sql query to find employees who were recruited on the same day or on neighboring days. [message #676485 is a reply to message #676484] Wed, 12 June 2019 02:24 Go to previous messageGo to next message
orajav
Messages: 23
Registered: June 2019
Junior Member
I tried to implement it this way, but the result is not the same.

with seqw as
(
SELECT e.FIRST_NAME, e.LAST_NAME, trunc(e.HIRE_DATE, 'dd') AS DAY,
COUNT(e.EMPLOYEE_ID), e.EMPLOYEE_ID
FROM EMPLOYEES e
GROUP BY trunc(e.HIRE_DATE, 'dd'), e.FIRST_NAME, e.LAST_NAME, e.EMPLOYEE_ID
having COUNT(e.EMPLOYEE_ID) > 1
ORDER BY DAY)
select w.FIRST_NAME, w.LAST_NAME, w.DAY from seqw w
where w.EMPLOYEE_ID > EMPLOYEE_ID
;
Re: Using the Sql query to find employees who were recruited on the same day or on neighboring days. [message #676486 is a reply to message #676485] Wed, 12 June 2019 02:41 Go to previous messageGo to next message
orajav
Messages: 23
Registered: June 2019
Junior Member
Still was such option, but not quite the lifts, that I need:

select FIRST_NAME, LAST_NAME, HIRE_DATE
FROM
(
select e1.FIRST_NAME, e1.LAST_NAME, e1.HIRE_DATE
FROM
employees e1
where exists (
select 1 FROM
employees e2 where
(trunc(e2.HIRE_DATE, 'dd') = trunc(e1.HIRE_DATE, 'dd'))
and e2.EMPLOYEE_ID > e1.EMPLOYEE_ID
)

union

select e3.FIRST_NAME, e3.LAST_NAME, e3.HIRE_DATE
FROM
employees e3
where exists (
select 1
FROM
employees e4 where ((trunc(e4.HIRE_DATE, 'dd') - trunc(e3.HIRE_DATE, 'dd'))= 1) and
e3.EMPLOYEE_ID < e4.EMPLOYEE_ID
)

union

select e5.FIRST_NAME, e5.LAST_NAME, e5.HIRE_DATE
FROM
employees e5
where exists (
select 1
FROM
employees e6 where
(
(trunc(e6.HIRE_DATE, 'dd') - trunc(e5.HIRE_DATE, 'dd')) = -1) and
e5.EMPLOYEE_ID < e6.EMPLOYEE_ID
)
)
order by 3
;
Re: Using the Sql query to find employees who were recruited on the same day or on neighboring days. [message #676487 is a reply to message #676484] Wed, 12 June 2019 03:27 Go to previous messageGo to next message
orajav
Messages: 23
Registered: June 2019
Junior Member
I created a query as you suggested via self-join, but it displays the wrong result.

select FIRST_NAME, LAST_NAME, HIRE_DATE
FROM
(
select e1.FIRST_NAME, e1.LAST_NAME, e1.HIRE_DATE
FROM
employees e1 join employees e2
on
(trunc(e2.HIRE_DATE, 'dd') = trunc(e1.HIRE_DATE, 'dd'))

join employees e3
on (trunc(e2.HIRE_DATE, 'dd') = trunc(e3.HIRE_DATE, 'dd')+1)

join employees e4
on (trunc(e4.HIRE_DATE, 'dd') = trunc(e3.HIRE_DATE, 'dd')-1)
)
Re: Using the Sql query to find employees who were recruited on the same day or on neighboring days. [message #676488 is a reply to message #676487] Wed, 12 June 2019 03:41 Go to previous messageGo to next message
Michel Cadot
Messages: 66446
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> select e1.LAST_NAME, e1.FIRST_NAME, e1.HIRE_DATE,
  2         e2.HIRE_DATE, e2.LAST_NAME, e2.FIRST_NAME
  3  from employees e1, employees e2
  4  where e2.EMPLOYEE_ID != e1.EMPLOYEE_ID
  5    and e2.HIRE_DATE > trunc(e1.HIRE_DATE) - 2
  6    and e2.HIRE_DATE < trunc(e1.HIRE_DATE) + 2
  7  order by e1.LAST_NAME, e2.HIRE_DATE, e2.LAST_NAME, e2.FIRST_NAME
  8  /
LAST_NAME                 FIRST_NAME           HIRE_DATE   HIRE_DATE   LAST_NAME                 FIRST_NAME
------------------------- -------------------- ----------- ----------- ------------------------- --------------------
Baer                      Hermann              07-JUN-2002 07-JUN-2002 Gietz                     William
Baer                      Hermann              07-JUN-2002 07-JUN-2002 Higgins                   Shelley
Baer                      Hermann              07-JUN-2002 07-JUN-2002 Mavris                    Susan
Banda                     Amit                 21-APR-2008 21-APR-2008 Kumar                     Sundita
Bissot                    Laura                20-AUG-2005 20-AUG-2005 Hall                      Peter
Bloom                     Harrison             23-MAR-2006 24-MAR-2006 Taylor                    Jonathon
Cabrio                    Anthony              07-FEB-2007 07-FEB-2007 Lorentz                   Diana
Davies                    Curtis               29-JAN-2005 30-JAN-2005 Tucker                    Peter
Errazuriz                 Alberto              10-MAR-2005 10-MAR-2005 Smith                     Lindsey
Errazuriz                 Alberto              10-MAR-2005 11-MAR-2005 Ozer                      Lisa
Faviet                    Daniel               16-AUG-2002 17-AUG-2002 Greenberg                 Nancy
Fox                       Tayler               24-JAN-2006 24-JAN-2006 Taylor                    Winston
Gietz                     William              07-JUN-2002 07-JUN-2002 Baer                      Hermann
Gietz                     William              07-JUN-2002 07-JUN-2002 Higgins                   Shelley
Gietz                     William              07-JUN-2002 07-JUN-2002 Mavris                    Susan
Greenberg                 Nancy                17-AUG-2002 16-AUG-2002 Faviet                    Daniel
Hall                      Peter                20-AUG-2005 20-AUG-2005 Bissot                    Laura
Higgins                   Shelley              07-JUN-2002 07-JUN-2002 Baer                      Hermann
Higgins                   Shelley              07-JUN-2002 07-JUN-2002 Gietz                     William
Higgins                   Shelley              07-JUN-2002 07-JUN-2002 Mavris                    Susan
Kumar                     Sundita              21-APR-2008 21-APR-2008 Banda                     Amit
Livingston                Jack                 23-APR-2006 24-APR-2006 Walsh                     Alana
Lorentz                   Diana                07-FEB-2007 07-FEB-2007 Cabrio                    Anthony
Mavris                    Susan                07-JUN-2002 07-JUN-2002 Baer                      Hermann
Mavris                    Susan                07-JUN-2002 07-JUN-2002 Gietz                     William
Mavris                    Susan                07-JUN-2002 07-JUN-2002 Higgins                   Shelley
OConnell                  Donald               21-JUN-2007 21-JUN-2007 Sullivan                  Martha
Ozer                      Lisa                 11-MAR-2005 10-MAR-2005 Errazuriz                 Alberto
Ozer                      Lisa                 11-MAR-2005 10-MAR-2005 Smith                     Lindsey
Smith                     Lindsey              10-MAR-2005 10-MAR-2005 Errazuriz                 Alberto
Smith                     Lindsey              10-MAR-2005 11-MAR-2005 Ozer                      Lisa
Sullivan                  Martha               21-JUN-2007 21-JUN-2007 OConnell                  Donald
Taylor                    Winston              24-JAN-2006 24-JAN-2006 Fox                       Tayler
Taylor                    Jonathon             24-MAR-2006 23-MAR-2006 Bloom                     Harrison
Tucker                    Peter                30-JAN-2005 29-JAN-2005 Davies                    Curtis
Walsh                     Alana                24-APR-2006 23-APR-2006 Livingston                Jack

36 rows selected.
The TRUNC(...) expressions are there because Oracle DATE datatype also contains a time part.
If you don't want both (X,Y) and (Y,X) (for instance, Baer+Gietz and Gietz+Baer) but only one out of the 2 then you have to change the condition in line 4.
If you want to change the delay between the hhire dates you have to change the condtions in line 5 and/or 6.
I let you convert Oracle joins into ANSI ones.

I forgot to mention the moderator bit: please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

[Updated on: Thu, 13 June 2019 00:45]

Report message to a moderator

Re: Using the Sql query to find employees who were recruited on the same day or on neighboring days. [message #676490 is a reply to message #676484] Wed, 12 June 2019 04:22 Go to previous messageGo to next message
orajav
Messages: 23
Registered: June 2019
Junior Member
A similar option c output pairs of employees have already seen
SELECT one.last_name, two.last_name,
       one.hire_date
FROM employee one, employee two
WHERE one.hire_date = two.hire_date
  AND one.emp_no < two.emp_no

,
but in the condition to bring that were hired on the same day or in the next few days.
Re: Using the Sql query to find employees who were recruited on the same day or on neighboring days. [message #676492 is a reply to message #676490] Wed, 12 June 2019 04:40 Go to previous messageGo to next message
Michel Cadot
Messages: 66446
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Which is what I posted.

Re: Using the Sql query to find employees who were recruited on the same day or on neighboring days. [message #676493 is a reply to message #676492] Wed, 12 June 2019 04:46 Go to previous messageGo to next message
orajav
Messages: 23
Registered: June 2019
Junior Member
Ok.
Re: Using the Sql query to find employees who were recruited on the same day or on neighboring days. [message #676502 is a reply to message #676485] Wed, 12 June 2019 15:09 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2858
Registered: January 2010
Location: Connecticut, USA
Senior Member
Keep in mind, Michel's solution has both ways links. E.g.

LAST_NAME                 FIRST_NAME           HIRE_DATE   HIRE_DATE   LAST_NAME                 FIRST_NAME
------------------------- -------------------- ----------- ----------- ------------------------- --------------------
Baer                      Hermann              07-JUN-2002 07-JUN-2002 Gietz                     William
Gietz                     William              07-JUN-2002 07-JUN-2002 Baer                      Hermann

As you can see we have Baer --> Gietz and Gietz --> Bayer. If you consider it a duplicate then change condition

e2.EMPLOYEE_ID != e1.EMPLOYEE_ID

to either

e2.EMPLOYEE_ID > e1.EMPLOYEE_ID

or

e2.EMPLOYEE_ID < e1.EMPLOYEE_ID


Also, 11G is out of support. If you upgrade to 12c then you can use match recognize instead of self join:


SELECT  LAST_NAME1,
        FIRST_NAME1,
        HIRE_DATE1,
        LAST_NAME2,
        FIRST_NAME2,
        HIRE_DATE2
  FROM  HR.EMPLOYEES
  MATCH_RECOGNIZE(
                  ORDER BY HIRE_DATE,
                           LAST_NAME,
                           FIRST_NAME
                  MEASURES FIRST(LAST_NAME) LAST_NAME1,
                           FIRST(FIRST_NAME) FIRST_NAME1,
                           FIRST(HIRE_DATE) HIRE_DATE1,
                           FIRST(EMPLOYEE_ID) EMPLOYEE_ID1,
                           LAST_NAME LAST_NAME2,
                           FIRST_NAME FIRST_NAME2,
                           HIRE_DATE HIRE_DATE2,
                           EMPLOYEE_ID EMPLOYEE_ID2
                  ALL ROWS PER MATCH
                  AFTER MATCH SKIP TO NEXT ROW
                  PATTERN  (GRP{2,})
                  DEFINE   GRP AS HIRE_DATE - FIRST(HIRE_DATE) < 2
                 )
  WHERE EMPLOYEE_ID2 != EMPLOYEE_ID1
  ORDER BY HIRE_DATE1,
           LAST_NAME1,
           HIRE_DATE1,
           LAST_NAME2,
           FIRST_NAME2,
           HIRE_DATE2
/

LAST_NAME1  FIRST_NAME HIRE_DATE LAST_NAME2  FIRST_NAME HIRE_DATE
----------- ---------- --------- ----------- ---------- ---------
Baer        Hermann    07-JUN-02 Gietz       William    07-JUN-02
Baer        Hermann    07-JUN-02 Higgins     Shelley    07-JUN-02
Baer        Hermann    07-JUN-02 Mavris      Susan      07-JUN-02
Gietz       William    07-JUN-02 Higgins     Shelley    07-JUN-02
Gietz       William    07-JUN-02 Mavris      Susan      07-JUN-02
Higgins     Shelley    07-JUN-02 Mavris      Susan      07-JUN-02
Faviet      Daniel     16-AUG-02 Greenberg   Nancy      17-AUG-02
Davies      Curtis     29-JAN-05 Tucker      Peter      30-JAN-05
Errazuriz   Alberto    10-MAR-05 Ozer        Lisa       11-MAR-05
Errazuriz   Alberto    10-MAR-05 Smith       Lindsey    10-MAR-05
Smith       Lindsey    10-MAR-05 Ozer        Lisa       11-MAR-05
Bissot      Laura      20-AUG-05 Hall        Peter      20-AUG-05
Fox         Tayler     24-JAN-06 Taylor      Winston    24-JAN-06
Bloom       Harrison   23-MAR-06 Taylor      Jonathon   24-MAR-06
Livingston  Jack       23-APR-06 Walsh       Alana      24-APR-06
Cabrio      Anthony    07-FEB-07 Lorentz     Diana      07-FEB-07
OConnell    Donald     21-JUN-07 Sullivan    Martha     21-JUN-07
Banda       Amit       21-APR-08 Kumar       Sundita    21-APR-08

18 rows selected.

SQL> 

SY.
Re: Using the Sql query to find employees who were recruited on the same day or on neighboring days. [message #676503 is a reply to message #676488] Thu, 13 June 2019 01:54 Go to previous messageGo to next message
orajav
Messages: 23
Registered: June 2019
Junior Member
Changed the condition as You indicated, but the doubles are not missing. Distinct didn't help. Oracle Oracle version 11G Release 2 (11.2).
See screen https://www.screencast.com/t/B4BNvSPUg.



[Edit MC: add url tags]

[Updated on: Thu, 13 June 2019 02:32] by Moderator

Report message to a moderator

Re: Using the Sql query to find employees who were recruited on the same day or on neighboring days. [message #676504 is a reply to message #676503] Thu, 13 June 2019 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 66446
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What you posted does not show doubles.
One line Higgins is associated with Baer and the other one she is with Gietz, these are not doubles.
The query does show only unique/distinct couples.

Re: Using the Sql query to find employees who were recruited on the same day or on neighboring days. [message #676505 is a reply to message #676504] Thu, 13 June 2019 03:35 Go to previous messageGo to next message
orajav
Messages: 23
Registered: June 2019
Junior Member
Ok. Thank you very mach!
Re: Using the Sql query to find employees who were recruited on the same day or on neighboring days. [message #676506 is a reply to message #676505] Thu, 13 June 2019 03:59 Go to previous messageGo to next message
orajav
Messages: 23
Registered: June 2019
Junior Member
And, how to remake with IN or Exists? In the early after cited queries with Exists, but did not get the correct result.
Re: Using the Sql query to find employees who were recruited on the same day or on neighboring days. [message #676507 is a reply to message #676506] Thu, 13 June 2019 04:04 Go to previous messageGo to next message
cookiemonster
Messages: 13630
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you want both employees in the output then exists and IN are no use to you.
Re: Using the Sql query to find employees who were recruited on the same day or on neighboring days. [message #676508 is a reply to message #676507] Thu, 13 June 2019 06:28 Go to previous messageGo to next message
orajav
Messages: 23
Registered: June 2019
Junior Member
Displays employees in pairs is not a prerequisite.

The task states: Find employees who were hired on the same day or on neighboring days (for example, one employee went out on March 2, 2015, and the other on March 3, 2015).
Print the name, surname and date of commencement of work.
Re: Using the Sql query to find employees who were recruited on the same day or on neighboring days. [message #676512 is a reply to message #676508] Thu, 13 June 2019 09:32 Go to previous messageGo to next message
cookiemonster
Messages: 13630
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can convert Michels query to EXISTS by moving the second employees table reference and the entire where clause into an EXISTS sub-query.
Re: Using the Sql query to find employees who were recruited on the same day or on neighboring days. [message #676517 is a reply to message #676512] Thu, 13 June 2019 10:48 Go to previous messageGo to next message
orajav
Messages: 23
Registered: June 2019
Junior Member
It seems to me so:
select e1.FIRST_NAME as FIRST_NAME, e1.LAST_NAME as LAST_NAME, e1.HIRE_DATE as HIRE_DATE
 from employees e1 
where  exists
(
select e2.* from  employees e2
where e2.EMPLOYEE_ID != e1.EMPLOYEE_ID
   and e2.HIRE_DATE > trunc(e1.HIRE_DATE) - 2
  and e2.HIRE_DATE < trunc(e1.HIRE_DATE) + 2
)
order by HIRE_DATE;
Re: Using the Sql query to find employees who were recruited on the same day or on neighboring days. [message #676523 is a reply to message #676517] Thu, 13 June 2019 11:49 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2858
Registered: January 2010
Location: Connecticut, USA
Senior Member
Compare plans:

SQL> EXPLAIN PLAN FOR
  2  select e1.FIRST_NAME as FIRST_NAME, e1.LAST_NAME as LAST_NAME, e1.HIRE_DATE as HIRE_DATE
  3   from hr.employees e1
  4  where  exists
  5  (
  6  select e2.* from  hr.employees e2
  7  where e2.EMPLOYEE_ID != e1.EMPLOYEE_ID
  8     and e2.HIRE_DATE > trunc(e1.HIRE_DATE) - 2
  9    and e2.HIRE_DATE < trunc(e1.HIRE_DATE) + 2
 10  )
 11  order by HIRE_DATE
 12  /

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
  2  /

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Plan hash value: 554555254

-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |    39 |     9  (34)| 00:00:01 |
|   1 |  SORT ORDER BY        |           |     1 |    39 |     9  (34)| 00:00:01 |
|   2 |   MERGE JOIN SEMI     |           |     1 |    39 |     8  (25)| 00:00:01 |
|   3 |    SORT JOIN          |           |   107 |  2889 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL | EMPLOYEES |   107 |  2889 |     3   (0)| 00:00:01 |
|*  5 |    FILTER             |           |       |       |            |          |
|*  6 |     SORT JOIN         |           |   107 |  1284 |     4  (25)| 00:00:01 |
|   7 |      TABLE ACCESS FULL| EMPLOYEES |   107 |  1284 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("E2"."HIRE_DATE"<TRUNC(INTERNAL_FUNCTION("E1"."HIRE_DATE"))+2
               AND "E2"."EMPLOYEE_ID"<>"E1"."EMPLOYEE_ID")
   6 - access("E2"."HIRE_DATE">TRUNC(INTERNAL_FUNCTION("E1"."HIRE_DATE"))-2
              )
       filter("E2"."HIRE_DATE">TRUNC(INTERNAL_FUNCTION("E1"."HIRE_DATE"))-2
              )

24 rows selected.

SQL> EXPLAIN PLAN FOR
  2  WITH TBL AS (
  3                SELECT  FIRST_NAME,
  4                        LAST_NAME,
  5                        HIRE_DATE,
  6                        COUNT(*) OVER(ORDER BY HIRE_DATE RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) CNT
  7                  FROM  HR.EMPLOYEES
  8               )
  9  SELECT  FIRST_NAME,
 10          LAST_NAME,
 11          HIRE_DATE
 12    FROM  TBL
 13    WHERE CNT > 1
 14    ORDER BY HIRE_DATE
 15  /

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
  2  /

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Plan hash value: 720055818

---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |   107 |  5136 |     4  (25)| 00:00:01 |
|*  1 |  VIEW               |           |   107 |  5136 |     4  (25)| 00:00:01 |
|   2 |   WINDOW SORT       |           |   107 |  2461 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES |   107 |  2461 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CNT">1)

15 rows selected.

SQL> 

SY.
Re: Using the Sql query to find employees who were recruited on the same day or on neighboring days. [message #676526 is a reply to message #676523] Thu, 13 June 2019 11:59 Go to previous messageGo to next message
orajav
Messages: 23
Registered: June 2019
Junior Member
Indeed, you can with intelligence to do, but in the conditions indicated, use "IN" or EXIST.
Ah, performance was out of the question.
Re: Using the Sql query to find employees who were recruited on the same day or on neighboring days. [message #676527 is a reply to message #676526] Thu, 13 June 2019 12:05 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2858
Registered: January 2010
Location: Connecticut, USA
Senior Member
Is this "just for fun"? Because use IN/EXISTS certainly isn't business requirement.

SY.
Re: Using the Sql query to find employees who were recruited on the same day or on neighboring days. [message #676529 is a reply to message #676527] Thu, 13 June 2019 12:46 Go to previous message
orajav
Messages: 23
Registered: June 2019
Junior Member
just for fun
Previous Topic: MV Refresh On Commit
Next Topic: Incorrect work of the sql query with JOIN.
Goto Forum:
  


Current Time: Mon Jun 24 19:07:40 CDT 2019