How do you select a correct effective date?

[M Skabialka]

I need help with a query for some reports.
I am tracking the amount of money we pay our contractors. Periodically the
rate changes, and contracts start and stop all the time..

So I have a table of contracts showing the begin and end date, and a table
of pay rates showing the effective date for the job code.


tblContract:
ContractNumber
ContractStartDate
ContractEndDate
ContractCompany (each uses its own rates)
e.g.
12345 3/1/03 9/30/03 ABC
12346 1/1/04 12/31/04 ABC
12360 10/1/03 9/30/04 XYZ
12360 1/1/04 9/30/04 XYZ


tblRate:
JobCode
Rate
EffectiveDate
ContractCompany
e.g.
Mngr 30.00 1/1/03 ABC
Mngr 31.00 1/1/04 ABC
Admin 11.00 10/1/03 XYZ
Admin 11.50 10/1/04 XYZ

I want to be able to run a report for the employees of one company, showing
all their work on all contracts, showing the correct rate for the period
they worked, and the total pay based on the number of hours.

However I am having a hard time getting a query to pick up only one rate
which is active during the period of the contract; I keep picking up all of
the rates so my query is wrong.

Thanks,
Mich

 

[MGFoster]

M Skabialka wrote:

So I have a table of contracts showing the begin and end date, and a table
of pay rates showing the effective date for the job code.

tblContract:
ContractNumber
ContractStartDate
ContractEndDate
ContractCompany (each uses its own rates)
e.g.
12345 3/1/03 9/30/03 ABC
12346 1/1/04 12/31/04 ABC
12360 10/1/03 9/30/04 XYZ
12360 1/1/04 9/30/04 XYZ

tblRate:
JobCode
Rate
EffectiveDate
ContractCompany
e.g.
Mngr 30.00 1/1/03 ABC
Mngr 31.00 1/1/04 ABC
Admin 11.00 10/1/03 XYZ
Admin 11.50 10/1/04 XYZ

I want to be able to run a report for the employees of one company, showing
all their work on all contracts, showing the correct rate for the period
they worked, and the total pay based on the number of hours.

However I am having a hard time getting a query to pick up only one rate
which is active during the period of the contract; I keep picking up all of
the rates so my query is wrong.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try something like:

SELECT C.ContractNumber, C.ContractCompany, R.JobCode, R.Rate
FROM tblContract As C INNER JOIN tblRate As R
ON C.ContractCompany = R.ContractCompany
WHERE R.EffectiveDate =
(SELECT Max(EffectiveDate)
FROM tblRate
WHERE EffectiveDate <= C.ContractEndDate
AND ContractCompany = C.ContractCompany)

This will only get the last (Max) Rate value effective during the
contract period. So, if the contract starts out at one Rate and
changes to another Rate during the contract period, only the last Rate
will be retrieved.

hth,

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQFYz34echKqOuFEgEQJWKgCg9Yy6Vb9IefVHwYYFEMesICJWaRYAnj7w
yKKk+Xu2EIjF455w5+3xjQWN
=5VmQ
-----END PGP SIGNATURE-----

 

[M Skabialka]

Thanks so much, I was able to make this work. I had tried putting a SELECT
as the criteria for effective date but I was out of the ball park, and using
"between x and y" wasn't working at all.

Mich