0

It is very simple to Determine Whether Two Date Ranges Overlap. My case is a little more difficult, but there has to be a simple solution.

I have following tables:

    CREATE TABLE catalog_version
    (
        id character varying(36) NOT NULL,
        name character varying(255),
        valid_from timestamp without time zone,
        valid_to timestamp without time zone,
    )

    CREATE TABLE catalog_article
    (
        id character varying(36) NOT NULL,
        code character varying(100),
        catalog_version_id character varying(36),
        valid_from timestamp without time zone,
        valid_to timestamp without time zone,
    )

Following restrictions:

  • Catalog 1..1 - 0..n catalog versions.
  • Catalog versions 1..1 - 0..n catalog articles.
  • valid_from and valid_to are optional for BOTH versions and articles.
  • If an article doesn't have a date_from, then it is valid when the version begins.
  • If an article doesn't have a date_to, then it is valid until the version expires.
  • There can't be two articles with the same code that are valid at the same time.
  • The framework uses a query builder meaning that I can't use any database specific features.

Is there a way to detect in a simple way, if there are already valid articles at a given time, if a user tries to define a new catalog article?

Examples:

----------------------------------------Version A---------------------------------------I
               I--Article ABC--I
                                               I--Article ABC--I





 Valid cases:
                                I-Article ABC-I
                                                               I-Article ABC-----------------
--Article ABC--I



 Invalid cases:
-----------------------------------------Article ABC-------I
                                    I----------Article ABC-----------------------------------

Does anyone have any idea if there is a way to simplify this task? The only solution I have at hand is to make AND-conditions for every possible case.

DigitalDevil
  • 1
  • 2
  • 1
    What RDBMS is this? If it's Postgres you could make use of [range functions](https://www.postgresql.org/docs/9.3/static/functions-range.html) to detect range overlap. Then it's just `WHERE range1 && range2` instead of all that AND business. – JNevill Feb 08 '18 at 20:42

1 Answers1

0

This was what I found that helped me with this problem. Second comment of having 

 SELECT MAX(starttime), MIN(endtime) FROM #period HAVING MIN(endtime) >
 MAX(starttime);

This answer belongs to this other question:

Overlapping dates

Martin H
  • 100
  • 7