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

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general