Selecting child records

[Billy No-Mates]

I have a database of personnel records (one per person), each with multiple
child record showing pay awards. How do I select the most recent pay award
for each person in a query? Each child record includes the date of the award.
I'm using Access 2002.

 

[John Spencer]

Generically, one solution would look like the following in the SQL window of
a query.

SELECT P.*, A.*
FROM Personnel as P INNER JOIN Awards as A
WHERE A.AwardDate =
(SELECT Max(Awards.AwardDate)
FROM Awards
WHERE Awards.PersonID = P.PersonID)

 

[Billy No-Mates]

Jamie - thanks very much, I'll try that.

Your personnel department is making pay awards to employees' children
<g>?! In all seriousness, the correct term is 'referencing' as in
'referencing table' and 'referenced table'.

Warning: database design is hard work.

Time is best modelled in SQL using closed and open periods of start-
and end date pairs, where the prior end (closed) date is one granule of
time (one second for Access/Jet) before the start (open) date. Google
for Rick Snodgrass's work on temporal modelling in SQL.

In this representation, only one period per employee can have a null
end date, indicating the current pay award.

A bit of hard work on the SQL DDL (design) side of things makes the SQL
DML (e.g. queries) easy to write:

SELECT E1.salary_amount
FROM EarningsHistory AS E1
WHERE E1.employee_nbr = arg_employee_number
AND E1.end_date IS NULL;

If entering future (anticipated) pay awards or termination dates are
allowed, this can be accommodated:

SELECT E1.salary_amount
FROM EarningsHistory AS E1
WHERE E1.employee_nbr = arg_employee_number
AND (NOW() BETWEEN E1.start_date AND E1.end_date
OR E1.end_date IS NULL);

Here's the hard work I did earlier (I did warn you <g>):

CREATE TABLE EarningsHistory (
employee_nbr VARCHAR(11) NOT NULL
REFERENCES Employees (employee_nbr)
ON DELETE NO ACTION
ON UPDATE CASCADE,
start_date DATETIME DEFAULT DATE() NOT NULL,
CONSTRAINT earnings_start_date__open_interval
CHECK(
HOUR(start_date) = 0
AND MINUTE(start_date) = 0
AND SECOND(start_date) = 0),
end_date DATETIME,
CONSTRAINT earnings_end_date__one_granule_closed_interval
CHECK(
HOUR(end_date) = 23
AND MINUTE(end_date) = 59
AND SECOND(end_date) = 59),
CONSTRAINT earnings_dates_order
CHECK (start_date < end_date),
salary_amount CURRENCY NOT NULL,
CONSTRAINT earnings_salary_amount__value
CHECK (salary_amount >= 0),
UNIQUE (employee_nbr, end_date),
PRIMARY KEY (employee_nbr, start_date)
);

We also need some constraints to ensure non-overlapping periods,
contiguous periods only, and that salary amount is different for
contiguous periods:

ALTER TABLE EarningsHistory ADD
CONSTRAINT earnings_history__no_overlapping_periods
CHECK (0 = (
SELECT COUNT(*)
FROM EarningsHistory, EarningsHistory AS E2
WHERE EarningsHistory.employee_nbr = E2.employee_nbr
AND EarningsHistory.start_date < E2.start_date
AND
(
E2.start_date
< IIF(EarningsHistory.end_date IS NULL, NOW(),
EarningsHistory.end_date)
OR IIF(E2.end_date IS NULL,
NOW(),
E2.end_date) < IIF(EarningsHistory.end_date IS NULL,
NOW(), EarningsHistory.end_date)
)
)
);

ALTER TABLE EarningsHistory ADD
CONSTRAINT earnings_history__periods_must_be_contiguous
CHECK ( 0 = (
SELECT COUNT(*)
FROM EarningsHistory AS E1
WHERE EXISTS (
SELECT *
FROM EarningsHistory AS E2
WHERE E1.employee_nbr = E2.employee_nbr
AND E1.start_date < E2.start_date)
AND NOT EXISTS (
SELECT * FROM EarningsHistory AS E2
WHERE E1.employee_nbr = E2.employee_nbr
AND DATEADD('s', 1, E1.end_date) = E2.start_date
)
)
);

ALTER TABLE EarningsHistory ADD
CONSTRAINT earnings_history__contiguous_periods_salary_must_change
CHECK (0 = (
SELECT COUNT(*)
FROM EarningsHistory, EarningsHistory AS E2
WHERE EarningsHistory.employee_nbr =
E2.employee_nbr
AND DATEADD('s', 1,
IIF(EarningsHistory.end_date IS NULL, NOW(),
EarningsHistory.end_date)) = E2.start_date
AND EarningsHistory.salary_amount = E2.salary_amount
)
);

Jamie.

 

[Billy No-Mates]

John - thanks, looks good.

Generically, one solution would look like the following in the SQL window of
a query.

SELECT P.*, A.*
FROM Personnel as P INNER JOIN Awards as A
WHERE A.AwardDate =
(SELECT Max(Awards.AwardDate)
FROM Awards
WHERE Awards.PersonID = P.PersonID)

 

[Billy No-Mates]

Hi John. Your solution worked fine - thanks. But how about if I want the most
recent date which is not greater than today's date? Sorry for the follow-up
and thanks in advance.

 

[John Spencer]

Then include that limit in the subquery, so it returns the max date less
than or equal to the current date.

SELECT P.*, A.*
FROM Personnel as P INNER JOIN Awards as A
WHERE A.AwardDate =
(SELECT Max(Awards.AwardDate)
FROM Awards
WHERE Awards.PersonID = P.PersonID
AND Awards.AwardDate <= Date())