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)
|
|
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 |
|
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 |
|
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 |
|
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 |
|
Michel Cadot
Messages: 68653 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 #676502 is a reply to message #676485] |
Wed, 12 June 2019 15:09 |
Solomon Yakobson
Messages: 3273 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 #676523 is a reply to message #676517] |
Thu, 13 June 2019 11:49 |
Solomon Yakobson
Messages: 3273 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.
|
|
|
|
|
|
Goto Forum:
Current Time: Tue May 21 13:23:14 CDT 2024
|