Re: Re: collecting employees who completed 5 and 10 years in the current month

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(dot)g(dot)johnston(at)gmail(dot)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, '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.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>