Date: Wed, 10 Aug 2005 12:52:29 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: Henry Ortega <juandelacruz@gmail.com>
Cc: Pgsql-sql@postgresql.org
Subject: Re: Breakdown results by month
Message-ID: <20050810175229.GA2525@wolff.to>
Mail-Followup-To: Bruno Wolff III <bruno@wolff.to>,
	Henry Ortega <juandelacruz@gmail.com>, Pgsql-sql@postgresql.org
References: <2bffcc3305080211342745b1ab@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <2bffcc3305080211342745b1ab@mail.gmail.com>
User-Agent: Mutt/1.5.6i

On Tue, Aug 02, 2005 at 14:34:46 -0400,
  Henry Ortega <juandelacruz@gmail.com> wrote:
> I have the ff table:
> 
> id       |    total    |     effective        |    end_date
> john          6              01-01-2005        02-28-2005
> john          8              03-01-2005        06-30-2005
> 
> How can I return:
> id       |    total    |     effective        |    end_date
> john          6              01-01-2005        01-31-2005
> john          6              02-01-2005        02-28-2005
> john          8              03-01-2005        03-31-2005
> john          8              04-01-2005        04-30-2005
> john          8              05-01-2005        05-31-2005
> john          8              06-01-2005        06-30-2005
> 
> Any help would be appreciated. Thanks

One approach would be to generate the monthly dates using generate_series
and some date math and join those rows to your ff table where the generated
dates are covered by the effective and end dates in the ff table. You may
need some more trickery if some of the dates aren't on month boundries.