Right you are David re my first query. That'll be more appropriate if you
want to establish if they're in their 5th year, or 10th year.
On Mon, Jun 30, 2014 at 6:08 PM, David G Johnston
david.g.johns...@gmail.com wrote:
Rebecca Clarke-2 wrote
create view vw_employee as
select * from employees
where ((age(joining_date::date) like '5 years%') or
(age(joining_date::date) like '10 years%') )
This does not give the correct answer to the poster's question - the LIKE
with a trailing % will pick up non-round intervals.
create view vw_employee as
select * from employees
where
((to_char(joining_date, '-MM') = to_char((now() - interval '5
years'), '-MM') )
or
(to_char(joining_date, '-MM') = to_char((now() - interval '10
years'), '-MM')))
This works - find out what year-month it was x years ago and compare it to
the corresponding year-month of the requested date.
If one were to be doing this often it would probably be worth while to
either use a functional index or a trigger-maintained field to store the
to_char(joining_date) calculation.
WHERE joining_date_yearmonth = ANY( ARRAY['2009-06','1999-06']::text[] );
Was also pondering using a VARIADIC function to pass in integer year(s),
which would then be converted into the corresponding array.
Haven't actually played with the above and so not sure how index-friendly
the =ANY(...) construct is but it does allow you to avoid add entire OR
clauses and instead simply supply a different comparison array.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/collecting-employees-who-completed-5-and-10-years-in-the-current-month-tp5809762p5809828.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
