Re: [SQL] Setting Variable - (Correct)

-------- Original Message  --------
Subject: Re:[SQL] [GENERAL] Setting Variable - (Correct)
From: Michael Glaesemann <grzm@seespotcode.net>
To: Ranieri Mazili <ranieri.oliveira@terra.com.br>
Date: 18/6/2007 13:50
> [Please reply to the list so that others may benefit from and
> participate in the discussion.]
>
> On Jun 18, 2007, at 11:32 , Ranieri Mazili wrote:
>
>> Thanks a lot for your prompt reply.
>> You query is perfect for my problem, but I need another thing with
>> it, I need to return the sum of production_hours of each month of the
>> current year, and I need to return too the average of the 3 past
>> years, can I do all in only one query or I need to do a UNION with
>> another query?
>
> Glad you found it helpful. What have you tried so far?
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
>
Look how I did:

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', production.production_date)::date BETWEEN
        date_trunc('month', CAST('2007-06-18' AS date) - (EXTRACT(MONTH
FROM CAST('2007-06-18' AS date))-1) * interval '1 month')::date
        AND date_trunc('month', CAST('2007-06-18' AS date))::date
GROUP BY production_period, id_production_area

UNION

SELECT date_trunc('year', production.production_date)::date
    AS production_period
    , product.id_production_area
    , sum(production_hours)/12 as total_production_hours
FROM production
JOIN product USING (id_product)
WHERE lost_hours = 'S'
    AND date_trunc('year', production.production_date)::date BETWEEN
        date_trunc('year', CAST('2007-06-18' AS date) - 3 * interval '1
year')::date
        AND date_trunc('year', CAST('2007-06-18' AS date) - 1 * interval
'1 year')::date
GROUP BY production_period, id_production_area
ORDER BY production_period DESC

I changed the "?" for values to test.
Look, I did a UNION, exist other way to do it better?

Thanks