Search Postgresql Archives

Subquery problems

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux