I know I've asked a similar question before but I can't find it.
I'm doing a new project for a charity which involves managing a skills /
requesit matrix. For each skill type / staff id I need to keep a record of
when the skill was aquired/renewed and when it expires.
Put simply
skills=# select * from staff;st_id | st_name
-------+--------- 1 | Gary
(1 row)
skills=# select * from skills;sk_id | sk_desc | sk_renewal
-------+---------+------------ 1 | Medical | 5 years
(1 row)
skills=# select * from qualifications ;st_id | sk_id | qu_qualified | qu_renewal
-------+-------+--------------+------------ 1 | 1 | 2004-07-01 | 10 years 1 | 1 | 2009-05-25 | 3
years
(2 rows)
skills=#
What is the best (cleanest SQL or fastest performance) way to produce the
following view?
st_id | st_name | sk_id | sk_desc | last_qualified | Renewal | Expires
-------+---------+-------+---------+----------------|---------|----------- 1 | Gary | 1 | Medical |
2009-05-25 | 3 years | 2012-05-25
I've got the following which gives all but the last two fields. The problem is
that the Renewal period and expires has to be from the most recent record,
i.e. even though the record 1 above expires after record 2, the results of
record 2 have to be used.
select t.*, k.sk_id, k.sk_desc, q.last_qualified from
(select st_id, sk_id, max(qu_qualified) as last_qualified from qualifications
group by st_id, sk_id) q
join staff t on t.st_id = q.st_id
join skills k on k.sk_id = q.sk_id
order by st_id, sk_id
I am still at the concept stage for this project so I can change the schema if
required
--
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk
