Re: Setting Variable - (Correct)

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