[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: [GENERAL] collecting employees who completed 5 and 10 years in the current month
From: Szymon Guz <mabewlun () gmail ! com>
Date: 2014-06-30 16:55:36
Message-ID: CAFjNrYur2h61Z8VS1TKNbFoOamUZS1VAH_oCuDF_TpoHc7ZPrA () mail ! gmail ! com
[Download RAW message or body]
On 30 June 2014 17:52, Rebecca Clarke <r.clarke83@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 <mabewlun@gmail.com> wrote:
>
>>
>> On 30 June 2014 12:38, Arup Rakshit <aruprakshit@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
[Attachment #3 (text/html)]
<div dir="ltr"><br><div class="gmail_extra"><br><br><div class="gmail_quote">On 30 \
June 2014 17:52, Rebecca Clarke <span dir="ltr"><<a \
href="mailto:r.clarke83@gmail.com" \
target="_blank">r.clarke83@gmail.com</a>></span> wrote:<br> <blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div \
style="font-family:tahoma,sans-serif;font-size:small;color:rgb(0,0,0)">Hi Arup,</div> \
<div style="font-family:tahoma,sans-serif;font-size:small;color:rgb(0,0,0)">
<br></div><div style="font-family:tahoma,sans-serif;font-size:small;color:rgb(0,0,0)">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.</div>
<div style="font-family:tahoma,sans-serif;font-size:small;color:rgb(0,0,0)"><br></div><div \
style="font-family:tahoma,sans-serif;font-size:small;color:rgb(0,0,0)"><br></div><div \
style="font-family:tahoma,sans-serif;font-size:small;color:rgb(0,0,0)">
create view vw_employee as<br></div><div><div><font color="#000000" face="tahoma, \
sans-serif"> select * from employees</font></div> <div><font color="#000000" \
face="tahoma, sans-serif"> where ((age(joining_date::date) like '5 \
years%') or (age(joining_date::date) like '10 years%') \
)</font></div><div>
<font color="#000000" face="tahoma, sans-serif"><br></font></div><div><font \
color="#000000" face="tahoma, sans-serif">or</font></div><div><font color="#000000" \
face="tahoma, sans-serif"><br>
</font></div><div><span style="color:rgb(0,0,0);font-family:tahoma,sans-serif"> \
create view </span><span \
style="color:rgb(0,0,0);font-family:tahoma,sans-serif">vw_employee</span><span \
style="color:rgb(0,0,0);font-family:tahoma,sans-serif"> as</span><font \
color="#000000" face="tahoma, sans-serif"><br>
</font></div><div><div> select * from employees</div><div> where </div><div> \
((to_char(joining_date, 'YYYY-MM') = to_char((now() - interval '5 \
years'), 'YYYY-MM') ) </div>
<div> or</div><div> (to_char(joining_date, 'YYYY-MM') = \
to_char((now() - interval '10 years'), \
'YYYY-MM')))</div><div><br></div></div><div \
style="color:rgb(0,0,0);font-family:tahoma,sans-serif;font-size:small">
<br></div><div style="color:rgb(0,0,0);font-family:tahoma,sans-serif;font-size:small"><br></div><div \
style="color:rgb(0,0,0);font-family:tahoma,sans-serif;font-size:small">And then to \
check the employees who have completed 5 or 10 years, you'll just do:</div>
<div style="color:rgb(0,0,0);font-family:tahoma,sans-serif;font-size:small"><br></div><div \
style="color:rgb(0,0,0);font-family:tahoma,sans-serif;font-size:small"> select * \
from vw_employee</div><div \
style="color:rgb(0,0,0);font-family:tahoma,sans-serif;font-size:small">
<br></div><div style="color:rgb(0,0,0);font-family:tahoma,sans-serif;font-size:small"><br></div><div \
style="color:rgb(0,0,0);font-family:tahoma,sans-serif;font-size:small">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.</div> <span class=""><font color="#888888">
<div style="color:rgb(0,0,0);font-family:tahoma,sans-serif;font-size:small"><br></div><div \
style="color:rgb(0,0,0);font-family:tahoma,sans-serif;font-size:small"> \
<br></div><div style="color:rgb(0,0,0);font-family:tahoma,sans-serif;font-size:small"> \
- Rebecca<br></div></font></span></div></div><div class=""><div class="h5"><div \
class="gmail_extra"><br><br><div class="gmail_quote"> On Mon, Jun 30, 2014 at 12:00 \
PM, Szymon Guz <span dir="ltr"><<a href="mailto:mabewlun@gmail.com" \
target="_blank">mabewlun@gmail.com</a>></span> wrote:<br> <blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div><div><br><div \
class="gmail_extra"><div class="gmail_quote">On 30 June 2014 12:38, Arup Rakshit \
<span dir="ltr"><<a href="mailto:aruprakshit@rocketmail.com" \
target="_blank">aruprakshit@rocketmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div><div \
style="color:rgb(0,0,0);background-color:rgb(255,255,255);font-family:lucida \
console,sans-serif;font-size:10pt">
<div>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.</div><div></div>
<div> </div><div><span \
style="color:rgb(76,118,162)"><span>Regards,<br></span></span><span><span \
style="color:rgb(76,118,162)">Arup \
Rakshit</span></span></div></div></div></blockquote></div><br></div></div></div><div \
class="gmail_extra">
Hi,<br></div><div class="gmail_extra">take a look at this example:<br><br></div><div \
class="gmail_extra">I've created a sample table:<br><br>create table users(id \
serial, joining_date date);<br></div><div class="gmail_extra">
<br>and filled it with sample data: <br><br>insert into users(joining_date) select \
now() - (j::text || 'days' )::interval from generate_series(1,10000) \
j;<br><br></div><div class="gmail_extra">Then the query showing up all users who \
complete 5 and 10 years this month can look like:<br>
<br>with u as (<br> select id, date_trunc('month', age(now()::date, \
joining_date)) age<br> from users<br>)<br>select * <br>from u <br>where u.age in \
('5 years', '10 years');<br><br><br></div><div class="gmail_extra">
- Szymon<br></div></div>
</blockquote></div><br></div>
</div></div></blockquote></div><br></div><div class="gmail_extra">Yea, quite nice \
Rebecca, I always forget the simplest solutions :)<br><br></div>- Szymon<br></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic