Re: Subquery problems

On Tue, 19 Jun 2007 09:17:22 -0300
Ranieri Mazili <ranieri(dot)oliveira(at)terra(dot)com(dot)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
>