Checking overlapping date ranges - how

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Checking overlapping date ranges - how

Author

Topic

cornwell
Starting Member

2 Posts

Posted - 2003-02-04 : 21:28:01

Table:
tblONCALL

Fields:
PKey
DeptId
StartDate
EndDate

On insert or update of a new record, I need to count how many records have any part of the new record date range overlapping any part of a date range in the table, for the matching DeptId. There may be many records with the same DeptId.

i.e something like SELECT COUNT(*) FROM tblONCALL WHERE (overlap stuff calculated)

Any help appreciated.

TIA.
cornwell

SamC
White Water Yakist

3467 Posts

Posted - 2003-02-04 : 22:56:59

I haven't tried this but..

If Start or End are between any other row's Start or End

OR

If Start or End of any other row is between the Insert Start or End?

SELECT COUNT(*) FROM tblONCALL T

WHERE
@Start BETWEEN T.StartDate and T.EndDate
or
@End BETWEEN T.StartDate and T.EndDate
or
T.StartDate BETWEEN @Start and @End
or
T.EndDate BETWEEN @Start and @End

There's got to be a more elegant way.

I look forward to reading it.

Sam

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-04 : 23:11:49

WHERE
@Start BETWEEN T.StartDate and T.EndDate
or
@End BETWEEN T.StartDate and T.EndDate
or
T.StartDate BETWEEN @Start and @End
or
T.EndDate BETWEEN @Start and @End

you can miss out any one of these statements e.g.
WHERE
@Start BETWEEN T.StartDate and T.EndDate
or
@End BETWEEN T.StartDate and T.EndDate
or
T.StartDate BETWEEN @Start and @End

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SamC
White Water Yakist

3467 Posts

Posted - 2003-02-04 : 23:18:52

OK, OK you're right

It's shorter which technically makes your suggestion more elegant. I know you won't accept an award on a technicality though.

Sam

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-05 : 00:15:29

Nope - just bored at the moment.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-02-05 : 03:25:00

Hi

This has always worked for me:

SELECT COUNT(*) FROM tblOnCALL WHERE StartDate <= @EndDate AND EndDate >= @StartDate

You have four scenarios to take care of as illustrated below.


      Start----------------------End
1. |------------------------------------|
2. |------------|
3.              |-----------------------|
4.              |---------|

AFAIK, the query above takes care of all four. Can anyone think of a scenario not covered?

OS

Edited by - mohdowais on 02/05/2003 03:36:29

SamC
White Water Yakist

3467 Posts

Posted - 2003-02-05 : 08:54:51

Nice solution.

It is easier to reason that there is no overlap when either of two conditions are met as follows:

WHERE @Startdate > EndDate or @EndDate < StartDate

So, If Overlapping intervals are wanted, the NOT of the no overlap test should work:

WHERE NOT (@StartDate > EndDate or @EndDate < StartDate)

WHERE NOT @StartDate > EndDate AND NOT @EndDate < StartDate

WHERE @StartDate <= EndDate AND @EndDate >= StartDate

Two comparisons. Good job.

Sam

Edited by - SamC on 02/05/2003 16:49:31

cornwell
Starting Member

2 Posts

Posted - 2003-02-05 : 17:04:41

HI mohdowais

Ahh.. how simple it turns outs to be - I am very grateful

cornwell

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-02-06 : 01:59:52

Thanx guys!!

I have always been a big fan of pen and paper (or whiteboard as the case may be). its so much easier to map out complex queries on paper and then work out a solution, rather than in the cramped space inside my brain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources