46451421/selecting-duplicates-from-sql-server-db-based-on-complex-criteria, Sql Server

Language: Layout:

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

DECLARE @table TABLE
    (
        fileid INT ,
        custid INT ,
        dept1 INT ,
        dept2 INT ,
        date1 DATE ,
        date2 DATE ,
        date3 DATE
    );

INSERT @table VALUES
(123,456,2,4,'2004-01-01','2005-01-01','2006-01-01'),
(777,456,2,4,NULL,'2005-05-30','2007-01-01'),
(111,456,2,4,'2006-12-02',NULL,'2007-03-03'),
(200,456,2,6,'2004-01-01','2004-02-01','2004-03-01'),
(444,456,2,8,'2007-02-01','2007-04-01','2007-06-01'),
(500,456,2,8,'2007-03-01','2007-03-15','2007-04-02'),
(666,456,2,4,'2008-01-01','2008-03-01','2008-05-01');

SELECT  *
INTO    #temp
    FROM
        (   SELECT
                       custid ,
                       dept1 ,
                       dept2
                FROM   @table
                GROUP BY
                       custid ,
                       dept1 ,
                       dept2
                HAVING COUNT(custid) > 1
        ) AS p;
SELECT  *
INTO    #temp2
    FROM
        (   SELECT
                     RN = ROW_NUMBER() OVER ( PARTITION BY
                                                  custid ,
                                                  dept1 ,
                                                  dept2
                                              ORDER BY custid
                                            ) ,
                     *
                FROM @table
                WHERE
                     custid IN ( SELECT custid FROM #temp )
                     AND dept1 IN ( SELECT dept1 FROM #temp )
                     AND dept2 IN ( SELECT dept2 FROM #temp )
        ) AS x;
SELECT   *
    FROM @table
    WHERE
         fileid IN (
                       SELECT   t1.fileid
                           FROM #temp2 AS t1
                                INNER JOIN #temp2 AS t2 ON t1.RN = t2.RN - 1
                                                           AND (
                                                                   COALESCE(t2.date1, t2.date2)
                                                           BETWEEN COALESCE(t1.date1, t1.date2) AND COALESCE(t1.date3, t1.date2)
                                                                   OR COALESCE(t2.date3, t2.date2)
                                                           BETWEEN COALESCE(t1.date1, t1.date2) AND COALESCE(t1.date3, t1.date2)
                                                               )
                                                           AND t2.custid = t1.custid
                                                           AND t2.dept1 = t1.dept1
                                                           AND t2.dept2 = t1.dept2
                   )
         OR fileid IN (
                          SELECT   t2.fileid
                              FROM #temp2 AS t1
                                   INNER JOIN #temp2 AS t2 ON t1.RN = t2.RN - 1
                                                              AND (
                                                                      COALESCE(t2.date1, t2.date2)
                                                              BETWEEN COALESCE(t1.date1, t1.date2) AND COALESCE(t1.date3, t1.date2)
                                                                      OR COALESCE(t2.date3, t2.date2)
                                                              BETWEEN COALESCE(t1.date1, t1.date2) AND COALESCE(t1.date3, t1.date2)
                                                                  )
                                                              AND t2.custid = t1.custid
                                                              AND t2.dept1 = t1.dept1
                                                              AND t2.dept2 = t1.dept2
                      );

View schema
Execution time: 0,16 sec, rows selected: 5, rows affected: 7, absolute service time: 0,31 sec

46451421/selecting-duplicates-from-sql-server-db-based-on-complex-crit...