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, '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')))
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.
