From: | Ranieri Mazili <ranieri(dot)oliveira(at)terra(dot)com(dot)br> |
---|---|
To: | pgsql-general(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org |
Subject: | Subquery problems |
Date: | 2007-06-19 12:17:22 |
Message-ID: | 4677C952.9060902@terra.com.br |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Kenneth Downs | 2007-06-19 12:29:27 | Re: How to prevent modifications in a tree of rows, based on a condition? |
Previous Message | Francisco Reyes | 2007-06-19 12:13:18 | Re: pg_restore out of memory |
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2007-06-19 13:40:16 | Re: Subquery problems |
Previous Message | Rodrigo De León | 2007-06-19 04:06:49 | Re: How can you generate a counter for ordered sets? |