pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBreakdown results by month
4+ messages / 4 participants
[nested] [flat]
* Breakdown results by month
@ 2005-08-02 18:34 Henry Ortega <juandelacruz@gmail.com>
2005-08-03 15:38 ` Re: Breakdown results by month codeWarrior <gpatnude@hotmail.com>
2005-08-04 13:42 ` Re: Breakdown results by month Jeff Boes <jeff@endpoint.com>
2005-08-10 17:52 ` Re: Breakdown results by month Bruno Wolff III <bruno@wolff.to>
0 siblings, 3 replies; 4+ messages in thread
From: Henry Ortega @ 2005-08-02 18:34 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Breakdown results by month
2005-08-02 18:34 Breakdown results by month Henry Ortega <juandelacruz@gmail.com>
@ 2005-08-03 15:38 ` codeWarrior <gpatnude@hotmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: codeWarrior @ 2005-08-03 15:38 UTC (permalink / raw)
To: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Breakdown results by month
2005-08-02 18:34 Breakdown results by month Henry Ortega <juandelacruz@gmail.com>
@ 2005-08-04 13:42 ` Jeff Boes <jeff@endpoint.com>
2 siblings, 0 replies; 4+ messages in thread
From: Jeff Boes @ 2005-08-04 13:42 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Breakdown results by month
2005-08-02 18:34 Breakdown results by month Henry Ortega <juandelacruz@gmail.com>
@ 2005-08-10 17:52 ` Bruno Wolff III <bruno@wolff.to>
2 siblings, 0 replies; 4+ messages in thread
From: Bruno Wolff III @ 2005-08-10 17:52 UTC (permalink / raw)
To: Henry Ortega <juandelacruz@gmail.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-08-10 17:52 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-02 18:34 Breakdown results by month Henry Ortega <juandelacruz@gmail.com>
2005-08-03 15:38 ` codeWarrior <gpatnude@hotmail.com>
2005-08-04 13:42 ` Jeff Boes <jeff@endpoint.com>
2005-08-10 17:52 ` Bruno Wolff III <bruno@wolff.to>
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