Re: Subquery problems - Mailing list pgsql-sql

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