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

On 30 June 2014 17:52, Rebecca Clarke <r(dot)clarke83(at)gmail(dot)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 <mabewlun(at)gmail(dot)com> wrote:
>
>>
>> On 30 June 2014 12:38, Arup Rakshit <aruprakshit(at)rocketmail(dot)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