Re: Breakdown results by month

On Tue, Aug 02, 2005 at 14:34:46 -0400,
Henry Ortega <juandelacruz(at)gmail(dot)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.