From: Bruno Wolff III <bruno@wolff.to>
To: Henry Ortega <juandelacruz@gmail.com>
Cc: Pgsql-sql@postgresql.org
Subject: Re: Breakdown results by month
Date: Wed, 10 Aug 2005 12:52:29 -0500
Message-ID: <20050810175229.GA2525@wolff.to> (raw)
In-Reply-To: <2bffcc3305080211342745b1ab@mail.gmail.com>
References: <2bffcc3305080211342745b1ab@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, juandelacruz@gmail.com
Subject: Re: Breakdown results by month
In-Reply-To: <20050810175229.GA2525@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox