Received-SPF: pass (temora.hst.terra.com.br: domain of terra.com.br designates
	200.176.10.6 as permitted sender) client-ip=200.176.10.6;
	envelope-from=ranieri.oliveira@terra.com.br; helo=[172.16.2.72];
Message-ID: <4676BCDF.5050700@terra.com.br>
Date: Mon, 18 Jun 2007 14:11:59 -0300
From: Ranieri Mazili <ranieri.oliveira@terra.com.br>
User-Agent: Thunderbird 2.0.0.4 (Windows/20070604)
MIME-Version: 1.0
To: pgsql-general@postgresql.org,  pgsql-sql@postgresql.org
Subject: Re: [SQL] Setting Variable - (Correct)
References: <467697ED.8030506@terra.com.br>
	<108B61B7-DB9A-4FE3-832C-323866956DFE@seespotcode.net>
	<7DA5333D-3437-4033-A222-128E541A04C2@seespotcode.net>
	<4676B38A.303@terra.com.br>
	<2D977EAC-654B-44F5-AB1F-336031F6466D@seespotcode.net>
In-Reply-To: <2D977EAC-654B-44F5-AB1F-336031F6466D@seespotcode.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

-------- 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