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
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.
