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