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.johnston@xxxxxxxxx> wrote:
Rebecca Clarke-2 wrote
> create view vw_employee asThis does not give the correct answer to the poster's question - the LIKE
> select * from employees
> where ((age(joining_date::date) like '5 years%') or
> (age(joining_date::date) like '10 years%') )
with a trailing "%" will pick up non-round intervals.
This works - find out what year-month it was x years ago and compare it to
> create view vw_employee as
> select * from employees
> where
> ((to_char(joining_date, 'YYYY-MM') = to_char((now() - interval '5
> years'), 'YYYY-MM') )
> or
> (to_char(joining_date, 'YYYY-MM') = to_char((now() - interval '10
> years'), 'YYYY-MM')))
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
