Choose active employes per month with dates formatted dd/mm/yyyy

Question

I'm having a hard time explaining this through writing, so please be patient.

I'm making this project in which I have to choose a month and a year to know all the active employees during that month of the year.. but in my database I'm storing the dates when they started and when they finished in dd/mm/yyyy format.

So if I have an employee who worked for 4 months eg. from 01/01/2013 to 01/05/2013 I'll have him in four months. I'd need to make him appear 4 tables(one for every active month) with the other employees that are active during those months. In this case those will be: January, February, March and April of 2013.

The problem is I have no idea how to make a query here or php processing to achieve this.

All I can think is something like (I'd run this query for every month, passing the year and month as argument)

pg_query= "SELECT employee_name FROM employees
           WHERE month_and_year between start_date AND finish_date"

But that can't be done, mainly because month_and_year must be a column not a variable.
Ideas anyone?

UPDATE

Yes, I'm very sorry that I forgot to say I was using DATE as data type.

The easiest solution I found was to use EXTRACT

select * from employees where extract (year FROM start_date)>='2013'
AND extract (month FROM start_date)='06' AND extract (month FROM finish_date)<='07'

This gives me all records from june of 2013 you sure can substite the literal variables for any variable of your preference

Answer 1

There is no need to create a range to make an overlap:

select to_char(d, 'YYYY-MM') as "Month", e.name
from
    (
        select generate_series(
            '2013-01-01'::date, '2013-05-01', '1 month'
        )::date
    ) s(d)
    inner join
    employee e on
        date_trunc('month', e.start_date)::date <= s.d
        and coalesce(e.finish_date, 'infinity') > s.d
order by 1, 2

SQL Fiddle

If you want the months with no active employees to show then change the inner for a left join

---

Erwin, about your comment:

the second expression would have to be coalesce(e.finish_date, 'infinity') >= s.d

Notice the requirement:

So if I have an employee who worked for 4 months eg. from 01/01/2013 to 01/05/2013 I'll have him in four months

From that I understand that the last active day is indeed the previous day from finish.

If I use your "fix" I will include employee f in month 05 from my example. He finished in 2013-05-01:

('f', '2013-04-17', '2013-05-01'),

SQL Fiddle with your fix