SQL statement of how to implement the following functions-CodePudding

If you have the following tableA:

Id name the start_date end_date
1 Amy 8/1/2019 9/23/2019
2 Bob 8/2/2019 9/24/2019
3 Cathy 8/3/2019 9/25/2019
4 Amy 10/4/2019 10/26/2019
5 Amy 8/5/2019 9/29/2019

How to find out the name duplication, overlap of data and time?

Should be found in article 1 and article 5 of the data (because of article 4 of the data is not overlap with other name is Amy's time) :
Id name the start_date end_date
1 Amy 8/1/2019 9/23/2019
5 Amy 8/5/2019 9/29/2019

CodePudding user response:

Since the connection line

 
WITH TMP AS 
(SELECT 1 ID, 'Amy' AS the NAME, TO_DATE (' 8/1/2019 ', 'the mm/dd/yyyy) AS the START_DATE, 
TO_DATE (' 9/23/2019 ', 'the mm/dd/yyyy) AS END_DATE 
The FROM DUAL 
UNION ALL 
SELECT 2, 'Bob' AS the NAME, TO_DATE (' 8/2/2019 ', 'the mm/dd/yyyy), 
TO_DATE (' 9/24/2019 ', 'the mm/dd/yyyy) 
The FROM DUAL 
UNION ALL 
SELECT 3, 'Cathy' AS the NAME, TO_DATE (' 8/3/2019 ', 'the mm/dd/yyyy), 
TO_DATE (' 9/25/2019 ', 'the mm/dd/yyyy) 
The FROM DUAL 
UNION ALL 
SELECT 4, 'Amy' AS the NAME, TO_DATE (' 10/4/2019 ', 'the mm/dd/yyyy), 
TO_DATE (' 10/26/2019 ', 'the mm/dd/yyyy) 
The FROM DUAL 
UNION ALL 
SELECT 5, 'Amy' AS the NAME, TO_DATE (' 8/5/2019 ', 'the mm/dd/yyyy), 
TO_DATE (' 9/29/2019 ', 'the mm/dd/yyyy) 
The FROM DUAL) 
SELECT A. * 
The FROM TMP A 
INNER JOIN TMP B 
ON B.I D & lt;> Anderson, D 
AND B.N AME=A.N AME 
AND B.S TART_DATE & lt;=A.E ND_DATE 
AND B.E ND_DATE & gt;=A.S TART_DATE 
The ORDER BY Anderson D

CodePudding user response:

Thank you very much! If the actual TableA particularly large amount of data, not list?

CodePudding user response:

Analysis function, use the two as a dimension, and then the count

CodePudding user response:

Written as data will be divergent, many-to-many associations affected the accuracy of the data,

reference 1st floor chengccy response:

since the connection line

 
WITH TMP AS 
(SELECT 1 ID, 'Amy' AS the NAME, TO_DATE (' 8/1/2019 ', 'the mm/dd/yyyy) AS the START_DATE, 
TO_DATE (' 9/23/2019 ', 'the mm/dd/yyyy) AS END_DATE 
The FROM DUAL 
UNION ALL 
SELECT 2, 'Bob' AS the NAME, TO_DATE (' 8/2/2019 ', 'the mm/dd/yyyy), 
TO_DATE (' 9/24/2019 ', 'the mm/dd/yyyy) 
The FROM DUAL 
UNION ALL 
SELECT 3, 'Cathy' AS the NAME, TO_DATE (' 8/3/2019 ', 'the mm/dd/yyyy), 
TO_DATE (' 9/25/2019 ', 'the mm/dd/yyyy) 
The FROM DUAL 
UNION ALL 
SELECT 4, 'Amy' AS the NAME, TO_DATE (' 10/4/2019 ', 'the mm/dd/yyyy), 
TO_DATE (' 10/26/2019 ', 'the mm/dd/yyyy) 
The FROM DUAL 
UNION ALL 
SELECT 5, 'Amy' AS the NAME, TO_DATE (' 8/5/2019 ', 'the mm/dd/yyyy), 
TO_DATE (' 9/29/2019 ', 'the mm/dd/yyyy) 
The FROM DUAL) 
SELECT A. * 
The FROM TMP A 
INNER JOIN TMP B 
ON B.I D & lt;> Anderson, D 
AND B.N AME=A.N AME 
AND B.S TART_DATE & lt;=A.E ND_DATE 
AND B.E ND_DATE & gt;=A.S TART_DATE 
The ORDER BY Anderson D

CodePudding user response:

Thought so, find out the name with multiple records, and then see if the date included in another, there is a premise, the start_date must not be greater than end_date

WITH TMP AS
(SELECT 1 ID,
'Amy' AS the NAME,
TO_DATE (' 8/1/2019 ', 'the mm/dd/yyyy) AS the START_DATE,
TO_DATE (' 9/23/2019 ', 'the mm/dd/yyyy) AS END_DATE
The FROM DUAL
UNION ALL
SELECT 2,
'Bob' AS the NAME,
TO_DATE (' 8/2/2019 ', 'the mm/dd/yyyy),
TO_DATE (' 9/24/2019 ', 'the mm/dd/yyyy)
The FROM DUAL
UNION ALL
SELECT 3,
AS the NAME, 'Cathy'
TO_DATE (' 8/3/2019 ', 'the mm/dd/yyyy),
TO_DATE (' 9/25/2019 ', 'the mm/dd/yyyy)
The FROM DUAL
UNION ALL
SELECT 4,
'Amy' AS the NAME,
TO_DATE (' 10/4/2019 ', 'the mm/dd/yyyy),
TO_DATE (' 10/26/2019 ', 'the mm/dd/yyyy)
The FROM DUAL
UNION ALL
SELECT 5,
'Amy' AS the NAME,
TO_DATE (' 8/5/2019 ', 'the mm/dd/yyyy),
TO_DATE (' 9/29/2019 ', 'the mm/dd/yyyy)
The FROM DUAL),
Tmp1 as
(select *
The from TMP
Where 1=1
And the exists (select 1
The from (select name
The from TMP
Where 1=1
Group by TMP. The name
Having a count (1) & gt; 1) mt
Where TMP. Name=mt. Name))
Select *
The from tmp1 A
Where 1=1
And the exists
(select 1
The from tmp1 b
Where 1=1
And Anderson, d & lt;> B.i d
And a.n ame=b.n ame
And (a.s tart_date between b.s tart_date and b.e nd_date or
A.e nd_date between b.s tart_date and b.e nd_date))

CodePudding user response:

reference 4 floor stelf response:

written as data will be divergent, many-to-many associations affected the accuracy of the data,

If the ID is the primary key will not, if there is no primary key ID, can ROWNUM as ID primary key

CodePudding user response:

refer to 6th floor chengccy response:

Quote: refer to 4th floor stelf response:

Written as data will be divergent, many-to-many associations affected the accuracy of the data,

If the ID is the primary key will not, if there is no primary key ID, can ROWNUM as ID primary key

Emm, if time is many-to-many

CodePudding user response:

reference 4 floor stelf response:

written as data will be divergent, many-to-many associations affected the accuracy of the data,

Just change the exists

 
WITH TMP AS 
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull