Date: Fri, 22 Jun 2007 01:29:00 +0900
From: Masaru Sugawara <rk73@ghost.plala.or.jp>
To: pgsql-sql@postgresql.org
Subject: Re: Subquery problems
In-Reply-To: <4677C952.9060902@terra.com.br>
References: <4677C952.9060902@terra.com.br>
Message-Id: <20070622012757.21BE.RK73@ghost.plala.or.jp>
MIME-Version: 1.0
Content-Type: text/plain; charset="US-ASCII"
Content-Transfer-Encoding: 7bit

On Tue, 19 Jun 2007 09:17:22 -0300
Ranieri Mazili <ranieri.oliveira@terra.com.br> wrote:

Hi,

This reply is not accurate, but I think there are helpful hints.

--
Masaru Sugawara


select 
  C.id_production_area,
  B.id_machine_type,
  A.h_month as month,
  max(A.n) as div_mes,
  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))/max(A.n) as integer)
from (select * 
       from  
       (select *, extract(month from h1.head_count_date) as h_month
           from head_count as h1
           where extract(year from h1.head_count_date) 
               = extract(year from current_date)
        ) as h2,
        (select extract(month from production_date) as p_month,
           count(distinct p1.production_date) as n
           from production as p1
           where extract(year from production_date) 
               = extract(year from current_date)
           group by extract(month from production_date)
         ) as p2
     where h2.h_month = p2.p_month
  ) as A, machine B, machine_type C
where  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, A.h_month
order by C.id_production_area, A.h_month, A.h_month DESC;


> Hello,
> 
> I'm having another "problem", I have a function that declares 12 
> variable, one per month and each them execute a select like bellow:
> *DIV_MES01 := (select count(distinct production_date) from production 
> where extract(month from production_date) = '01' and extract(year from 
> production_date) = EXTRACT(YEAR FROM current_date));
> 
> *Then, I need to check if the variable is equal 0:
> *IF DIV_MES01 = 0 THEN
>    DIV_MES01 := 1;
> END IF;
> 
> *Finally,  I perform the following query:
> 
> *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('01' as text) AS mes
> FROM      head_count A, machine B, machine_type C
> WHERE     EXTRACT(YEAR FROM head_count_date) = EXTRACT(YEAR FROM 
> current_date)
>           AND EXTRACT(MONTH FROM head_count_date) = '01'
>           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
> 
> *Doing it, I need to perform 12 querys united by "UNION", what I want to 
> do is unify it in only one query, I tryed with the query bellow:
> 
> *SELECT     date_trunc('month', A.head_count_date)::date as head_date,
>            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))/(select   count(distinct production_date)
>                                       from     production
>                                       where    extract(month from 
> production_date) = EXTRACT(MONTH FROM date_trunc('month', 
> A.head_count_date)::date)
>                                                and extract(year from 
> production_date) = EXTRACT(YEAR FROM current_date))) AS integer),
>            C.id_production_area
> FROM     head_count A, machine B, machine_type C
> WHERE    date_trunc('month', A.head_count_date)::date BETWEEN
>          date_trunc('month', current_date - (EXTRACT(MONTH FROM 
> current_date)-1) * interval '1 month')::date
>          AND date_trunc('month', current_date)::date
>          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, head_count_date,head_date
> ORDER BY id_production_area, head_count_date,head_date DESC
> 
> *But the results aren't what I want.
> What I trying to do is possible?
> 
> I appreciate any help.
> Thanks
>