On 30 June 2014 17:52, Rebecca Clarke <r.clark...@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 <mabew...@gmail.com> wrote: > >> >> On 30 June 2014 12:38, Arup Rakshit <arupraks...@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