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

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