In-Reply-To: <108B61B7-DB9A-4FE3-832C-323866956DFE@seespotcode.net>
References: <467697ED.8030506@terra.com.br>
	<108B61B7-DB9A-4FE3-832C-323866956DFE@seespotcode.net>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <7DA5333D-3437-4033-A222-128E541A04C2@seespotcode.net>
Cc: Ranieri Mazili <ranieri.oliveira@terra.com.br>,
	pgsql-general@postgresql.org, pgsql-sql@postgresql.org
Content-Transfer-Encoding: 7bit
From: Michael Glaesemann <grzm@seespotcode.net>
Subject: Re: Setting Variable - (Correct)
Date: Mon, 18 Jun 2007 11:15:12 -0500
To: Michael Glaesemann <grzm@seespotcode.net>


On Jun 18, 2007, at 10:17 , Michael Glaesemann wrote:

> Looking over your function, I'm a little confused about what you're  
> trying to do. I'm guessing the (final) result you're trying to get  
> is the number of hours lost for each product per area per month for  
> the three years prior to the provided date.

Or, rather, the number of hours lost per production area per month  
for the three years prior to the provided date.

> SELECT date_trunc('month', production.production_date)::date
>     AS production_period
>     , product.id_production_area
>     , sum(production_hours) as total_production_hours
> FROM production
> JOIN product USING (id_product)
> WHERE lost_hours = 'S'
>     AND date_trunc('month', a.production_date)::date BETWEEN
>         date_trunc('month', ? - 3 * interval '1 year')::date
>         AND date_trunc('month', ?)::date;

Looks like I forgot the GROUP BY clause:

GROUP BY production_period, id_production_area

Michael Glaesemann
grzm seespotcode net