SQL query / data version check

Question

I get stuck with some SQL query which check if dateto from first row is not > than datefrom for second row within same ID.Maybe the easiest way is to present it on example:

ID     DATEFROM   DATETO     PK
1234   20150512   20150518   1
1234   20150514   20150520   2
1234   20150519   null       3
2313   20150512   20150518   4
44341  20150512   null       5

Now, within id 1234 flow is:

1.2015-05-12 -> 2015-05-18
2.2015-05-13 -> 2015-05-20  WRONG
3.2015-05-19 -> null

In this example end date of first row is > than start date for second row. As output I would like to display PK id ( in this example:2).

Could anyone give me some hints how to approach this? I don't want SQL but just hints. Thanks in advance

For your data and rules, (3) should also be returned.

Gordon Linoff
– Gordon Linoff

2016-01-05 15:11:47 +00:00
Commented Jan 5, 2016 at 15:11 — Gordon Linoff
– Gordon Linoff, Commented Jan 5, 2016 at 15:11

Gordon Linoff · Accepted Answer · 2016-01-05 15:11:17Z

2

You can use a join or correlated subquery for this. I suspect that you want a full overlap comparison:

select t.*
from t
where exists (select 1
              from t t2
              where t.id = t2.id and
                    (t2.start_date < t.start_date and
                     t2.end_date > t.start_date or t2.end_date is null)
             );

answered Jan 5, 2016 at 15:11

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bazyl Over a year ago

You are right, thanks for that, that;s easiest than I thought ; )

David Rushton Over a year ago

If this worked for you @user5506560 please consider accepting the answer. This will help those who follow you.

Collectives™ on Stack Overflow

SQL query / data version check

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related