From: | Rebecca Clarke <r(dot)clarke83(at)gmail(dot)com> |
---|---|
To: | Szymon Guz <mabewlun(at)gmail(dot)com> |
Cc: | Arup Rakshit <aruprakshit(at)rocketmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: collecting employees who completed 5 and 10 years in the current month |
Date: | 2014-06-30 15:52:32 |
Message-ID: | CAMChtddLx5bbEUDwBxGXfpLCDYk2ipuKofk4Qm_GNbeJ96HGBg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Arup Rakshit | 2014-06-30 16:15:14 | Re: collecting employees who completed 5 and 10 years in the current month |
Previous Message | Arup Rakshit | 2014-06-30 15:32:33 | Re: collecting employees who completed 5 and 10 years in the current month |