Occurances happening at the same time query

[james.szumigata]

Hello,

I have a question that I was hoping that someone could help me with. I
need to know if two events are happening at the same time. For
example:

Joe Smith was working on project A from 1/1/2006 to 1/31/2006 and was
working on Project B in 1/5/2006. I need to know how to identify if
they are working onan two projects at the same time...I know its easy
with 1, but with 50,000 people i need to use a query.

Any help would be greatly appreciated.

 

[KARL DEWEY]

How do you record the work?
One record per project with start and end dates?
Or one record per employee per project per day?

 

[james.szumigata]

I just need to know the workers name:

for example:

Joe Smith would show up in the query as he is working on project B on a
day within the range he was working on project A

 

[KARL DEWEY]

I can not tell you how to construct a query if I do not know how you collect
and store the data.

What is your table structure?

 

[james.szumigata]

May be I need to be mopre specific. I have two tables: Workers who
worked on project A and Wrokers who worked on project B. With start
and end dates for all projects. I need to know how many workers in
table B that had a start date between the start and end date on the
project a table?

 

[james.szumigata]

Is this what you mean

TABLE A:

Worker Name:
Project: (All A's I separated them out)
Project Start Date
Project End Date


TABLE B:

Worker Name:
Project: (All B's I separated them out)
Project Start Date
Project End Date

 

[james.szumigata]

Top

Is this what you mean

TABLE A:

Worker Name:
Project: (All A's I separated them out)
Project Start Date
Project End Date


TABLE B:

Worker Name:
Project: (All B's I separated them out)
Project Start Date
Project End Date

 

[John Spencer]

Guessing,

SELECT A.*, B.*
FROM TableA as A INNER JOIN TableB as B
ON A.WorkerName = B.WorkerName
WHERE A.ProjectStartDate <=B.ProjectEndDate
AND A.ProjectEndDate >= B.ProjectStartDate