Billy said: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.
Jamie Collins said: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.
John Spencer said: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)
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.