From: Ranieri Mazili <ranieri.oliveira@terra.com.br>
To: pgsql-general@postgresql.org
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Setting Variable - (Correct)
Date: Mon, 18 Jun 2007 14:11:59 -0300
Message-ID: <4676BCDF.5050700@terra.com.br> (raw)
In-Reply-To: <2D977EAC-654B-44F5-AB1F-336031F6466D@seespotcode.net>
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>
-------- 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ranieri.oliveira@terra.com.br, pgsql-general@postgresql.org
Subject: Re: [SQL] Setting Variable - (Correct)
In-Reply-To: <4676BCDF.5050700@terra.com.br>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox