0

As a Rookie in SQL I have a problem creating a query.

Here is the problem : I would like to check on a table if there are lines that share the same DATES.

For example I would like to know if in TABLE A, there are cases like this :

ID | LIB | START_DATE | END_DATE
01 | A1  | 01-01-2016 | 05-03-2016
02 | A2  | 03-03-2016 | 05-06-2016

Those lines are SURVEYS and I would like to know if an admin created a survey that is starting during an other survey. I did some searches on AUTO JOIN but this wasn't really what I was looking for.

My case is a bit more complicated but I will find out myself if you explain how to proceed on this simple case.

2

Must be something like:

select   s1.id, s2.id
from     surveys s1, surveys s2
where    (s2.start_date between s1.start_date and s1.end_date
or        s2.end_date   between s1.start_date and s1.end_date)
and      s1.id!=s2.id;

This fetches you the IDs where the START_DATE or END_DATE (of s2) is in the period of another START_DATE and END_DATE (s1).

  • Is there a way to not having in results 25, 52 when you already have 52, 25 ? – Julien S Jun 27 '16 at 14:49
  • @JulienS - this is a new question. Ask it as such. – Vérace Jun 27 '16 at 19:27
2

this would be exact

select min(id), max(id)  
from table 
group by start_da, end_date 
having count(*) > 1

start in

select t1.*, t2*
from table t1 
join table t2
      on t1.startdate between t2.startdate and t2.end_date
  • Thank you for your comment, but the first query returns nothing and the second one return every surveys. – Julien S Jun 27 '16 at 14:27
  • Then every survey satisfies t1.startdate between t2.startdate and t2.end_date It is exactly the stated question and the same as the answer you accepted. You must have keyed it in wrong. – paparazzo Jun 27 '16 at 14:32
  • I'll double check, anyway thank you for your help :) – Julien S Jun 27 '16 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.