Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 <aruprakshit@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux