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.
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
;
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
Please clarify. Do you get an error? What database engine and version are you on?
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)
"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.