Re: How to Find Overlapping Date Ranges in a Table

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thank you for taking the time to comment on my style of asking a question,
but really, you needn't have troubled yourself. Two other people have
managed to decipher my apparently cryptic question and given succinct and
helpful answers.

Perhaps you have a link to "How to answer questions in a newsgroup"? I
imagine it might talk about grandmother's and egg sucking.

I have read the remainder of your reply with interest. Thank you for that.

Charles


"--CELKO--" <jcelko212@xxxxxxxxxxxxx> wrote in message
news:d4f701e2-a12a-4c9f-a1c7-cdb910fbbea2@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative

Referential Integrity, data types, etc. in your schema are. If you
know how, follow ISO-11179

data element naming conventions and formatting rules. Temporal data
should use ISO-8601

formats. Code should be in Standard SQL as much as possible and not
local dialect.

Sample data is also a good idea, along with clear specifications. It
is very hard to debug

code when you do not let us see it. If you want to learn how to ask a
question on a Newsgroup,

look at: http://www.catb.org/~esr/faqs/smart-questions.html

I have a table with a PK, two FKs and two dates (datetime)[are they DATE
or DATETIME?] : PeriodStart and PeriodEnd. <<

By definition, it has to have a Primary Key to even be a table. Wild
ass guessing lead me to this skeleton.

CREATE TABLE FoobarPeriods
(foobar_id INTEGER NOT NULL PRIMARY KEY,
period_start_date DATE NOT NULL,
period_end_date DATE NOT NULL,
CHECK (period_start_date < period_end_date),
foo_id INTEGER NOT NULL
REFERENCES Foo(foo_id),
bar_id INTEGER NOT NULL
REFERENCES Bar(bar_id),
..);

Due to some fault, the table has ended up with some duplicate date
ranges and some overlapping ranges, which I need to identify. <<

And you then need to add constraints to prevent this from happening
over and over. The first constraint is UNIQUE (period_start_date,
period_end_date). The second is best done with a TRIGGER in SQL
Server; it still does not have all of the SQL-92 features (I can show
you how to use a VIEW WITH CHECK OPTION, if you wish).

There are two scenarios, depending on the particular table:
a) FK_1 must not have any periods that overlap, but FK_2 can
b) neither FK_1 nor FK_2 can have any overlapping periods <<

This makes no sense. What do the two referenced tables look like?
Read how bad your narrative is!

Can anyone think of a neat way to identify those rogue records [sic:
rows are not records]? <<

You should have a Calendar table in your schema. Look for a calendar
date that falls in two or more ranges

SELECT C.cal_date, MIN(foobar_id), MAX(foobar_id), COUNT(foobar_id)
FROM FoobarPeriods AS F, Calendar AS C
WHERE C.cal_date BETWEEN F.period_start_date AND F.period_end_date
GROUP BY C.cal_date
HAVING COUNT(foobar_id) > 1




.

Relevant Pages

  • Re: The C Boolean Fallacy Crack
    ... in response to a calendar. ... Better welcome constraints now or ... Dianna will hopefully dislike them except for you. ...
    (sci.crypt)
  • Re: Task Delaying start
    ... Your thoughts are right on but I refrain from constraints so there are none ... no lag and no resource contention. ... You should also check the working time calendar to make sure the 3 day ...
    (microsoft.public.project)
  • Re: Date Calculation
    ... Remember duration is the ... constraints on the task, ... between the tasks, the resources you have to do them with, and how long each ... very careful about using the 24 hour calendar as ...
    (microsoft.public.project)
  • Re: Task Constraints
    ... This is exactly the situation you interpret, Steve. ... > you can't "Always Start Before 9am" by using constraints. ... > is to create a calendar that show hours of work from, say, 07:59 to 08:59. ... > between the task you need to start before 9 and its predecessor. ...
    (microsoft.public.project)
  • Re: How do I add more ranges to the following formula?
    ... teams (alternating). ... I want the days in the calendar to change color ... In each day of the calendar I used the folowing formula in Condition 1 and ... This works for up to 8 ranges. ...
    (microsoft.public.excel.misc)