Find records with overlap in dates

Transact-SQL
1

Hi everybody,

I'm looking voor a solution for the next question. in a table i have subscriptions with a ~start_date~ , ~end_date~ and a ~paid_till_date~. These subscriptions also have a ~person_id~.

Im looking to build a query to find the subscriptions for persons who have paid double for the same day and i would like the double subscriptions in the result of the query. Can somebody Help?

For example:
person_id '2' has 2 subscriptions

Subscription:1
Start_date 2017-06-30
end_date 2017-07- 14
Paid_till_date 2017-07-07

Subscription:2
Start_date 2017-07-03
end_date 2017-07- 17
Paid_till_date 2017-07-10

These subs overlap and '2' wil pay double for a few days.

2

Try this:

with cte
  as (select person_id
            ,start_date
            ,end_date
            ,row_number() over(partition by person_id order by start_date,end_date) as rn
        from yourtable
     )
select a.person_id
      ,a.start_date
      ,a.end_date
  from cte as a
       inner join cte as b
	           on b.person_id=a.person_id
              and b.rn<>a.rn
              and b.start_date<=a.end_date
              and b.end_date>=a.start_date
;
3

Thanks for your Reply BitsMed

I understand i need to change 'yourtable' but seem like something is not completly correct. Do i need to correct more?

Grtz

4

Please clarify. Do you get an error? What database engine and version are you on?

5

i'm on MYSQL 5.7

The table i extract the data from is called 'Subscriptions'

ERROR:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cte
AS (select Person_id
,Start_date
,End_date
' at line 1 (Line 16)

6

"Common Table Expressions" and "row_number() over()" are not supported in MySQL.

As this forum is for Microsoft SQL Server, you might not get the best answer here. You might be better off asking in a MySQL forum.

You could try this:

select a.person_id
      ,a.start_date
      ,a.end_date
  from yourtable as a
       inner join yourtable as b
               on b.person_id=a.person_id
              and b.start_date<=a.end_date
              and b.end_date>=a.start_date
 group by a.person_id
         ,a.start_date
         ,a.end_date
 having count(*)>1
;

It is more generic sql that should be compatible with most sql database engines. The downside with this is, it won't find duplicates.