DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=Q5dbTEoGrJNCxEj+7n6JqDKWMCKctcV07uQudH57oLubMTH/tP/FvuHj7TONE/3GEPGgkul6EuwfjFOY0BwiKBJ214e6M+I5Hm4y4vKhodTniZILQ4n1hddHxBwKJseYLGFpoaJCDuRdB2QGzzySs86gASEciQzjN5jBefYxhnU=
Message-ID: <b42b73150706190824g5b653258n1de2498866330c08@mail.gmail.com>
Date: Tue, 19 Jun 2007 11:24:29 -0400
From: "Merlin Moncure" <mmoncure@gmail.com>
To: "Ranieri Mazili" <ranieri.oliveira@terra.com.br>
Subject: Re: Subquery problems
Cc: pgsql-general@postgresql.org
In-Reply-To: <4677EF39.6050904@terra.com.br>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <4677C952.9060902@terra.com.br>
	<b42b73150706190640m7eb90d6bg1468765876b13665@mail.gmail.com>
	<4677EF39.6050904@terra.com.br>

On 6/19/07, Ranieri Mazili <ranieri.oliveira@terra.com.br> wrote:
> > sure!.
> >
> >  SELECT    cast(((sum(A.qty_employees_total)
> >            -(sum(A.qty_absence)
> >            -sum(A.qty_vacation)
> >            -sum(A.qty_diseased)
> >            -sum(A.qty_indirect)
> >            -sum(A.qty_transferred))
> >            +sum(A.qty_received))/DIV_MES01) AS integer),
> >            C.id_production_area,
> >            cast(DIV_MES01 as text) AS mes
> >  FROM      head_count A, machine B, machine_type C,
> > (
> >  select case when ct = 0 then 1 else ct end as DIV_MES01 from
> >  (
> >    select count(distinct production_date) as ctfrom production where
> >   extract(year from production_date) = EXTRACT(YEAR FROM current_date)
> >  ) q
> > ) D
> >  WHERE     EXTRACT(YEAR FROM head_count_date) = EXTRACT(YEAR FROM
> > current_date)
> >            AND EXTRACT(MONTH FROM head_count_date) = DIV_MES01
> >            AND A.id_machine = B.id_machine
> >            AND B.id_machine_type = C.id_machine_type
> >  GROUP BY C.id_production_area, B.id_machine_type;
> >
> > ok, I didn't syntax check this monster, but it should give you a
> > start...the trick is to use an 'inline view' to expand your variable
> > list into a set.
> >
> On this way didn't work, I wanna do only one query to return the data of
> entire year, not only one month, but thanks for try.
> If someone have an idea of how do it, please, help :D
>

the inlne view (table 'D') breaks down the year month by month.  maybe
you have to add DIV_MES01 to the group  by?

merlin