Comparing Dates In Different Tables

[jaws1972]

I have searched the FAQ's and the messages left for the answer to my problem - but haven't got anywhere

I am trying to create an Absence & Sickness database for my Company and need to compare different tables of information. In a simplified version, I have a list of when people were absent (including dates) and in a seperate table I have a list of departments and the dates in which they worked in them.

What I need the query to do is to look at the date of the Absence and then compare that to the date in the second table to see which department they were working in

For example

TABLE 1

Emp No. Abs Start Abs End
7537 01/02/2003 02/02/2003

TABLE 2

Emp No. Department Dept Start
7537 HR 01/01/2003
7537 FINANCE 01/01/2004

What I would expect to see is that Employee 7537 was working in the HR Dept when he was absent

Any ideas would be greatly appreciated

 

[Nowell]

Hi,

Here is the SQL you require:

SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN Table2 ON Table1.empNo = Table2.EmpNo
WHERE (((Table1.AbsStart) Between Table2.DeptStart And Now()));

Hope this is what you want

 

[jaws1972]

Thanks for the reply Novell - it sort of works

If I expand what I said originally to the actuall tables I get the following

TABLE 1

Emp No. Abs Start Abs End
7537 01/02/2003 02/02/2003
7537 10/01/2004 11/01/2004


TABLE 2

Emp No. Department Dept Start
7537 HR 01/01/2003
7537 FINANCE 01/01/2004


While your answer worked fine on my simplistic example, when there are 2 or more absences I will get more records than needed.

The above example I would like to see the first Absence under HR, and the second under FINANCE. Unfortunately I get the second one reporting correctly, but I will get two instances of the first Absence - as there are two dates that are between the Abs Start Date and Now

Any more answers greatly (and I can't express that part enough) received

Thanks

 

[Nowell]

Hi,

I think really you have a problem in your table design, cos now you have a many to many relationship between the two tables.

You should not have a many to many relationship in a database design.

 

[jaws1972]

You're telling me !!

The information comes from a Payroll and Personnel system and I have been tasked with trying to come up with some sort of reporting to save money

I am finding out that the underlying database is not efficient enough for me to do this sort of reporting

Thanks for the help though

 

[Nowell]

Hi,

Can you re-design it by putting a table inbetween the many to many relationship?

 

[jaws1972]

Hi again

Not really - the absence part of this system does not record what department the person worked in. Thats why I was trying to do it this way, by comparing department start dates and absence dates.

I may have to try some sort of VB coding where the dates are read in, and the absence date is compared to an array of department dates when abs date > dept date (A) and Between dept date (A+1) and Now () - or something like that

Should be a laugh anyway !

 

[cheerio]

How about:

SELECT T1.EmpNo, T1.AbsStart, T1.AbsEnd, T2.Department
FROM TABLE1 T1
JOIN TABLE2 T2
ON T1.EmpNo = T2.EmpNo
AND T2.DeptStart =
(
SELECT MAX(DeptStart)
FROM TABLE2 T3
WHERE T3.EmpNo = T1.EmpNo
AND T3.DeptStart <= T1.AbsStart
)