Find date range overlap via SQL query

[keijen]

Hi,

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 ONCALL WHERE (overlap stuff calculated)

How to??

All help appreciated.

TIA.
keijen

 

[grnzbra]

How about a function which takes the newStartDate and nesEndDate and uses a where clause:
Where newStartDate >= StartDate or newEndDate <= EndDate

 

[grnzbra]

Just noticed that the where clause missed some possibilities so we'll try again with:
Where newStartDate >= StartDate And nesStartDate <= EndDate)or (newEndDate <= EndDate and newEndDate >= StartDate) or (newStartDate <= StartDate and newEndDate >= EndDate)