CODEWITHSUNDEEP
sqlselectdb2repeat
Veljko
Veljko

Reputation: 1808

SQL SELECT repeating rows from table for specific time interval

I have table and I want to find repeating rows for specific time interval (DATE is input parameter for SQL query) where it will list all rows with the same PERSON and TYPE value.

ID  DATE          PERSON   TYPE
1   01.01.2017    PERSON1  TYPE1
2   02.02.2017    PERSON1  TYPE1
3   03.03.2017    PERSON2  TYPE1
4   04.04.2017    PERSON2  TYPE2
5   05.05.2017    PERSON2  TYPE1
6   06.06.2017    PERSON1  TYPE2

So for example if DATE is between 01.01 and 04.04 it should list me rows with ID 1 and 2.

If DATE is between 01.01 and 06.06 it should list me rows with ID 1, 2, 3 and 5 because 1 and 2 have the same person and type in that interval and 3 and 5 have the same person and type in that interval.

SELECT ID FROM TABLE
WHERE DATE>='01.01.2017' AND DATE<='06.06.2017'

but I am not sure even how to start to define this repeating clause based on PERSON and TYPE columns. Maybe can INNER JOIN help with this if referencing the same table and matching those two columns and third column ID is different?: TABLE.PERSON=TABLE.PERSON and TABLE.TYPE=TABLE.TYPE and TABLE.ID!=TABLE.ID of course table is the same but different alias can be used for this?

Upvotes: 0

Views: 1617

Answers (4)

Dan Getz
Dan Getz

Reputation: 18217

Another method would be:

SELECT a.id
FROM tablename a NATURAL JOIN
  (SELECT person,type FROM tablename
   WHERE date>='01.01.2017' AND date<='06.06.2017'
   GROUP BY person, type HAVING COUNT(*)>1) b ;

The NATURAL JOIN would automatically use columns person and type.

Upvotes: 0

toonice
toonice

Reputation: 2236

Please try...

SELECT ID AS ID
FROM tableName
JOIN
(
    SELECT person AS person,
           type AS type,
           COUNT( person ) AS countOfPair
    FROM tableName
    WHERE date BETWEEN startDate AND endDate
    GROUP BY person,
             type
) tempTable ON tableName.person = tempTable.person AND
               tableName.type = tempTable.type
WHERE countOfPair >= 2

The inner SELECT gathers each combination of person and type in between your start and end dates (please replace startDate and endDate with however you are referencing those) and performs a count of them.

The outer SELECT statement's JOIN then has the effect of appending the count of each combination to the end of each row containing that combination. The outer SELECT then retrieves the ID from each row that has a repeated combination.

If you have any questions or comments, then please feel free to post a Comment accordingly.

Upvotes: 1

etsa
etsa

Reputation: 5060

You can try this (I don't know if your version has window analytic function): (X is the name of your table)

SELECT Y.ID, Y.DATE, Y.PERSON, Y.TYPE
FROM (
   SELECT *, COUNT(*) OVER (PARTITION BY PERSON, TYPE) AS RC
   FROM X 
   WHERE DATE >='01.01.2017' AND DATE <='04.04.2017'
    ) Y 
WHERE RC>1

Or this if it doesn't support them:

SELECT X.ID, X.DATE, X.PERSON, X.TYPE
FROM X
INNER JOIN (
   SELECT PERSON, TYPE, COUNT(*) AS RC
   FROM X 
   WHERE DATE >='01.01.2017' AND DATE <='04.04.2017'
    GROUP BY PERSON, TYPE
    ) Y ON X.PERSON = Y.PERSON AND X.TYPE = Y.TYPE
WHERE RC>1

I suggest to use always appropriate conversion for date datatypes.

Upvotes: 0

AleDG
AleDG

Reputation: 84

Add "DISTINCT" clause to avoid redundancy

SELECT DISTINCT ID FROM TABLE
WHERE DATE>='01.01.2017' AND DATE<='06.06.2017'

Upvotes: -2

Related Questions