Thread: Breakdown results by month
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
You are looking for what is commonly referred to as a "pivot-table" or a "cross-tab". Pivot-tables are a fairly sophisticated, non-normalized view of a dataset, nd most commonly appear in spreadsheets and are used for financial or statistical analysis. These queries typically use an aggregate function (sum, min, max, etc....) and a "group by" clause when generated from an SQL query... There are lots of decent tutorials / samples out there (Google for "Pivot Table SQL" and you'll see...) http://www.windowsitpro.com/Files/09/15608/Listing_01.txt http://mail.python.org/pipermail/python-list/2005-February/264233.html "Henry Ortega" <juandelacruz@gmail.com> wrote in message news:2bffcc3305080211342745b1ab@mail.gmail.com... >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 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >
Henry Ortega 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 I would look at writing a PL/PgSQL function that walks through the rows, generating substitute rows for the dates you want.
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.