0

I'm having problems selecting the data from the following example.

I have a dataset with contracts belonging to users that, in some cases, are overlapping in time (say, for current_date). Only when this overlap happens, I just want to know the latest contract signed. However, if one of the overlapping contracts is a 'test' (type = test), then I should take the valid one. The problem is that I can't figure out how to select this. Could anyone give me a hint?

So in this example

Table contracts
    START_DATE  END_DATE    USER_ID TYPE            CONTRACT_ID
    2015-09-17  2017-09-16  3193522 valid           318159780
    2016-09-21  2017-09-20  3193522 valid           318159779
    2015-11-26  2016-11-25  3510657 valid           319501137
    2016-09-20  2016-11-26  3510657 test            319501127

The desired output would be

    START_DATE  END_DATE    USER_ID TYPE            CONTRACT_ID
    2016-09-21  2017-09-20  3193522 valid           318159779
    2015-11-26  2016-11-25  3510657 valid           319501137

The dbms is MySQL. If they don't overlap I want to show them regardless their type. There are no overlapping contracts with the same start_date, this is controlled at application level.

5
  • 3
    Which DBMS are you using?
    – user1822
    Oct 6, 2016 at 17:21
  • And if they are both valid and do not overlap? Oct 6, 2016 at 18:15
  • 1
    I'd use a ranking query, giving an integer weighting (with a CASE statement, i.e. WHEN valid THEN 1 ELSE 2), then order by that rank and have a HAVING [rank] = 1. Oct 6, 2016 at 21:48
  • What if there are two or more matching contracts with the same START_DATE, which one should be picked? What if all contracts that match the date are test, should you pick one of them at all?
    – Andriy M
    Oct 7, 2016 at 6:14
  • The dbms is MySQL. If they don't overlap I want to show them regardless their type. There are no overlapping contracts with the same start_date, this is controlled at application level Oct 7, 2016 at 7:40

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.