Counting the Number of Records That Exist Between 2 Dates

[JeffG]

Hello all....

I have a table that has a few fields in it, including Project, Contractor,
Start Date and End Date. I would like to run a query that shows how many
Projects (rows) that a Contractor is assinged to that have over-lapping
dates, meaning that if Contractor X has a Start Date of 1/1/07 and End Date
of 1/31/07 on one Project and a Start Date of 1/15/07 and End Date of
1/25/07, it would show that there are 2 Projects for the Contractor that are
over-lapping.

Thanks.

Jeff G

 

[John Spencer]

Perhaps something like the following

SELECT P1.Contractor
, P1.Project
, P1.Start
, P1.End
, P2.Project
FROM Projects as P1 INNER JOIN Projects as P2
ON P1.ContractorId = P2.ContractorID
AND P1.Start <=P2.End
AND P1.End >= P2.Start
And P1.ProjectId < P2ProjectID

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Marshall Barton]

I have a table that has a few fields in it, including Project, Contractor,
Start Date and End Date. I would like to run a query that shows how many
Projects (rows) that a Contractor is assinged to that have over-lapping
dates, meaning that if Contractor X has a Start Date of 1/1/07 and End Date
of 1/31/07 on one Project and a Start Date of 1/15/07 and End Date of
1/25/07, it would show that there are 2 Projects for the Contractor that are
over-lapping.

SELECT T.Contractor,
T.Project, T.[Start Date], T.[End Date],
X.Project, X.[Start Date], X.[End Date],
FROM table As T INNER JOIN table As X
ON T.Contractor = X.Contractor
WHERE T.[Start Date] < X.[End Date]
And T.[End Date] < X.[Start Date]

 

[JeffG]

John -

Thanks....worked like a charm....

Jeff

Perhaps something like the following

SELECT P1.Contractor
, P1.Project
, P1.Start
, P1.End
, P2.Project
FROM Projects as P1 INNER JOIN Projects as P2
ON P1.ContractorId = P2.ContractorID
AND P1.Start <=P2.End
AND P1.End >= P2.Start
And P1.ProjectId < P2ProjectID

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.