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

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

From

Arup Rakshit

Date:

30 June 2014, 13:39:04

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

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

From

Szymon Guz

Date:

30 June 2014, 14:00:30

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

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

From

Rebecca Clarke

Date:

30 June 2014, 18:52:38

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

create view vw_employee as

select * from employees

where

((to_char(joining_date, 'YYYY-MM') = to_char((now() - interval '5 years'), 'YYYY-MM') )

(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

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

From

Arup Rakshit

Date:

30 June 2014, 19:33:24

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

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

From

Szymon Guz

Date:

30 June 2014, 19:55:44

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

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

From

David G Johnston

Date:

30 June 2014, 20:08:50

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.

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

From

Arup Rakshit

Date:

30 June 2014, 20:15:43

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

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

From

Rebecca Clarke

Date:

01 July 2014, 12:23:34

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:

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

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

From

Rebecca Clarke

Date:

01 July 2014, 13:25:48

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)

((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:

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