Re: collecting employees who completed 5 and 10 years in the current month - Mailing list pgsql-general

Hi Arup,

Two ways come to mind for me. They're pretty much the same as Szymon's, just minus the sample table creation. I would suggest creating a view instead, so you can just select from it whenever you please.


 create view vw_employee as
   select * from employees
   where ((age(joining_date::date) like '5 years%') or (age(joining_date::date) like '10 years%') )

or

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



And then to check the employees who have completed 5 or 10 years, you'll just do:

   select * from vw_employee


This is done off the top of my head so there will likely be syntax errors, but I hope this can give you a general idea.


 - Rebecca


On Mon, Jun 30, 2014 at 12:00 PM, Szymon Guz <mabewlun@gmail.com> wrote:

On 30 June 2014 12:38, Arup Rakshit <aruprakshit@rocketmail.com> wrote:
I have employee table. Where I have a column joining_date. Now I am looking for a way to get all employee, who completed 5 years, 10 years current month. How to do so ? I am not able to figure this out.
 
Regards,
Arup Rakshit

Hi,
take a look at this example:

I've created a sample table:

create table users(id serial, joining_date date);

and filled it with sample data:

insert into users(joining_date) select now() - (j::text || 'days' )::interval from generate_series(1,10000) j;

Then the query showing up all users who complete 5 and 10 years this month can look like:

with u as (
  select id, date_trunc('month', age(now()::date, joining_date)) age
  from users
)
select *
from u
where u.age in ('5 years', '10 years');


- Szymon

pgsql-general by date:

Previous
From: Thomas Kellerer
Date:
Subject: Re: Examples of projects that use Postgres "as API server"
Next
From: Arup Rakshit
Date:
Subject: Re: collecting employees who completed 5 and 10 years in the current month