|
|
Posted by Ed Murphy on 10/12/06 16:46
--CELKO-- wrote:
> First, let's clean up your missing DDL. The table name should tell us
> what set of entities is modeled in the table; do you really have one
> employee? Small firm! Try Personnel -- the collective name of the set
> or something that tells us what the set is.
What about "Employees"? But this is mostly grammatical pedantry; any
reasonable person will understand the implicit plural.
> Since you did not post DDL, we have to guess at constaints and
> keys. A skeleton of what you need is something like this:
>
> CREATE TABLE PersonnelActions
> (emp_id INTEGER NOT NULL,
> action_date action_dateTIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
> PRIMARY KEY (emp_id, foobar_date),
> action_code CHAR(2) NOT NULL
> CHECK (foobar_code IN ('VO', 'XD'))
> );
"something like", indeed. That penultimate line should be more like
CHECK (action_code IN ('VO', 'XD', 'AD', 'WR', 'AT', 'XR'))
or should refer to an ActionCodes table, or should simply be omitted.
> SELECT DISTINCT emp_id
Need to specify whether it comes from PVO or PAD.
> FROM PersonnelAction AS PVO,
> PersonnelAction AS PAD
> WHERE PVO.emp_id = PAD.emp_id
> AND PVO.action_code = 'VO'
> AND PAD.action_code = 'AD'
> AND PAD.action_date < PVO.action_date
The following is clearer IMO:
FROM PersonnelAction AS PEarlier
JOIN PersonnelAction AS PLater ON PEarlier.emp_id = PLater.emp_id
AND PEarlier.action_date < PLater.action_date
WHERE PEarlier.action_code = 'AD'
AND PLater.action_code = 'VO'
> AND NOT EXISTS
> (SELECT *
> FROM PersonnelAction AS PAT
> WHERE PAT.action_code = 'AT'
> AND PAT.emp_id = PVO.emp_id
> AND PAT_action_date BETWEEN PAD.action_date AND
> PVO.action_date);
Navigation:
[Reply to this message]
|