Subquery problems

On 6/19/07, Ranieri Mazili <ranieri.oliveira@terra.com.br> wrote:

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

sure!.

  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(DIV_MES01 as text) AS mes
  FROM      head_count A, machine B, machine_type C,
(
  select case when ct = 0 then 1 else ct end as DIV_MES01 from
  (
    select count(distinct production_date) as ctfrom production where
   extract(year from production_date) = EXTRACT(YEAR FROM current_date)
  ) q
) D
  WHERE     EXTRACT(YEAR FROM head_count_date) = EXTRACT(YEAR FROM
 current_date)
            AND EXTRACT(MONTH FROM head_count_date) = DIV_MES01
            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;

ok, I didn't syntax check this monster, but it should give you a
start...the trick is to use an 'inline view' to expand your variable
list into a set.

merlin

-------- Original Message --------
Subject: Re:[GENERAL] Subquery problems
From: Merlin Moncure <mmoncure@gmail.com>
To: Ranieri Mazili <ranieri.oliveira@terra.com.br>
Date: 19/6/2007 10:40

On 6/19/07, Ranieri Mazili <ranieri.oliveira@terra.com.br> wrote:

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

sure!.

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(DIV_MES01 as text) AS mes
FROM      head_count A, machine B, machine_type C,
(
select case when ct = 0 then 1 else ct end as DIV_MES01 from
(
select count(distinct production_date) as ctfrom production where
extract(year from production_date) = EXTRACT(YEAR FROM current_date)
) q
) D
WHERE     EXTRACT(YEAR FROM head_count_date) = EXTRACT(YEAR FROM
current_date)
AND EXTRACT(MONTH FROM head_count_date) = DIV_MES01
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;

ok, I didn't syntax check this monster, but it should give you a
start...the trick is to use an 'inline view' to expand your variable
list into a set.

merlin

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On this way didn't work, I wanna do only one query to return the data of
entire year, not only one month, but thanks for try.
If someone have an idea of how do it, please, help :D

Thanks

On 6/19/07, Ranieri Mazili <ranieri.oliveira@terra.com.br> wrote:

sure!.

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(DIV_MES01 as text) AS mes
FROM      head_count A, machine B, machine_type C,
(
select case when ct = 0 then 1 else ct end as DIV_MES01 from
(
select count(distinct production_date) as ctfrom production where
extract(year from production_date) = EXTRACT(YEAR FROM current_date)
) q
) D
WHERE     EXTRACT(YEAR FROM head_count_date) = EXTRACT(YEAR FROM
current_date)
AND EXTRACT(MONTH FROM head_count_date) = DIV_MES01
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;

ok, I didn't syntax check this monster, but it should give you a
start...the trick is to use an 'inline view' to expand your variable
list into a set.

On this way didn't work, I wanna do only one query to return the data of
entire year, not only one month, but thanks for try.
If someone have an idea of how do it, please, help :D

the inlne view (table 'D') breaks down the year month by month. maybe
you have to add DIV_MES01 to the group by?

merlin

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