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

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

On Monday, June 30, 2014 04:52:32 PM you wrote:

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

But I am using Ruby on Rails framework to develop web application. Here I use
basically query. If no way, then I go for view. It seems I can use this as a
select query. But view of course a good idea.In our web app, we will show this
data as a report. A user can run it whenever he/she feel. All query seems like
current day query. But I really need current month. Again it sometimes feel
like ok, sometimes not. :-)

--
================
Regards,
Arup Rakshit
================
Debugging is twice as hard as writing the code in the first place. Therefore,
if you write the code as cleverly as possible, you are, by definition, not
smart enough to debug it.

--Brian Kernighan

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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 Monday, June 30, 2014 04:52:32 PM Rebecca Clarke wrote:

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

Can this query be set up like :-

Consider the below scenarios :

Ram completed 5 years on 12/04/2014
Shyam completed 5 years on 21/04/2014
Ayan completed 10 years on 12/04/2014
and so on...

Now consider the current month is *march*. I have 12 employees. Out of which
above only completed 5 and 10 years. Thus my output should come as

Name milestones when
Ram 5 12/04/2014
Shyam 5 21/04/2014
Ayan 10 12/04/2014

--
================
Regards,
Arup Rakshit
================
Debugging is twice as hard as writing the code in the first place. Therefore,
if you write the code as cleverly as possible, you are, by definition, not
smart enough to debug it.

--Brian Kernighan

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 30 June 2014 17:52, Rebecca Clarke <r.clarke83@gmail.com> wrote:

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

Yea, quite nice Rebecca, I always forget the simplest solutions :)

- Szymon

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

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@gmail.com> wrote:

From my understanding of what you're saying, you want all the employees
that have a 5 year, or 10 year anniversary between today and the start of
the current month?

If that is the case, then this is what I came up with:

select
employee_name,
to_char(current_date, 'YYYY')::integer - to_char(joining_date::date,
'YYYY')::integer as milestone,
joining_date + (current_date - joining_date) as anniversary_date
from employees
where
((joining_date::date + interval '5 years') >= to_char(current_date,
'YYYY-MM-1')::date and (joining_date::date + interval '5 years') <=
current_date)
or
((joining_date::date + interval '10 years') >= to_char(current_date,
'YYYY-MM-1')::date and (joining_date::date + interval '10 years') <=
current_date)

Once again, excuse any syntax errors.

On Mon, Jun 30, 2014 at 5:15 PM, Arup Rakshit <aruprakshit@rocketmail.com>
wrote: