This is my best effort so far is below. My concern is that it isn't very 
efficient and will slow down as record numbers increase

create view current_qualifications as 
select q.*, (q.qu_qualified+q.qu_renewal)::date as qu_expires from 
qualifications q 
join (select st_id, sk_id, max(qu_qualified) as qu_qualified from 
qualifications group by st_id, sk_id) s
on q.st_id=s.st_id and q.sk_id = s.sk_id and q.qu_qualified = s.qu_qualified;


select t.st_id, t.st_name, k.sk_id, k.sk_desc, q.qu_qualified, q.qu_renewal, 
q.qu_expires 
from current_qualifications q
join staff t on t.st_id = q.st_id
join skills k on k.sk_id = q.sk_id;


-- 
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk 


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Reply via email to