Re: Aggregate query for multiple records

Scott,

> Unfortunately, your revised query works like a charm except for the
> fact that prd_data."date" - prd2."date" + 1 give incorrect values when
> the year wraps, see in the output below. Need to conditionally
> subtract 88 from the date or use an incrementing count() function
> instead of date math to get the correct values.

Oh, that column is text, not a serial number. Well, it's your fault for
using wierd custom data types; you figure it out.

> I'm also wondering if using the date functions of Postgres would be
> helpful since the date column does have the YYYY-year and MM-month
> parts.

Well, you'd want to convert the column to a timestamp, and then you could
compute months. Or you could break it in seperate integer "year" and
"month" columns and do the same thing.

--
Josh Berkus
Aglio Database Solutions
San Francisco