Regarding Dates Overlap - SQL Server Forums

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

Transact-SQL (2000)

Regarding Dates Overlap

Author

Topic

winrodri
Starting Member

2 Posts

Posted - 2006-06-09 : 10:45:28

Hi...

There is a table Rationale...
having 4 columns...
RationaleID NOT NULL,RationaleName NOT NULL,StartDate NOT NULL,EndDate NULL

While Inserting into the table...
For a particular RationaleName there can exist many records...as long as the dates do not overlap...

and since the EndDate could also be NULL... there cannot be any record for that period...after it's startdate...

Could one do this in a query...to find the overlapping records...or the non overlapping records...either way...

Thanks in advance...

Rodri.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-09 : 11:46:44

What is overlapping? When a record's EndDate is later/newer than other record's StartDate? And if so, which record would you like to display?

Please post sample data and sample output.

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-06-10 : 22:50:11

I find that the best way to find overlapping dates is to find records where both start dates occur before both end dates. If you're putting it into a trigger (which I would imagine would be easiest), then use Inserted. Like this:

if exists (
select *
from Inserted i
join
Rationale r
on r.startdate > isnull(i.enddate,'31-dec-2199')
and i.startdate < isnull(r.enddate,'31-dec-2199')
)
rollback transcation

Hope this helps,

Rob

Rob Farley
http://robfarley.blogspot.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-11 : 11:14:59

try this.


declare	@Rationale table
(
	RationaleID 	int identity(1,1)	NOT NULL,
	RationaleName 	varchar(10) 	NOT NULL,
	StartDate 	datetime	NOT NULL,
	EndDate 	datetime	NULL
)

insert into @Rationale(RationaleName, StartDate, EndDate)
select	'Name 1', '2006-06-01', '2006-06-05'	union all
select	'Name 2', '2006-06-03', '2006-06-10'	union all
select	'Name 3', '2006-06-04', NULL		union all
select	'Name 4', '2006-05-01', '2006-05-20'	union all
select	'Name 5', '2006-05-21', '2006-06-02'

select	distinct s.*
from	@Rationale s inner join @Rationale e
	on	s.RationaleID		<> e.RationaleID
	and	(
			s.StartDate			between	e.StartDate and isnull(e.EndDate, '99991231')
		or	isnull(s.EndDate, '99991231')	between	e.StartDate and isnull(e.EndDate, '99991231')
		)
order by s.RationaleID

/* RESULT

RationaleID RationaleName StartDate   EndDate                                                
----------- ------------- ----------- -----------
1           Name 1        2006-06-01  2006-06-05 
2           Name 2        2006-06-03  2006-06-10 
3           Name 3        2006-06-04  NULL
5           Name 5        2006-05-21  2006-06-02 
*/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources