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