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@xxxxxxxxxxxxxx> wrote:
On Monday, June 30, 2014 04:52:32 PM Rebecca Clarke wrote:Can this query be set up like :-
> 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')))
>
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
