Thread: Subquery problems
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
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
-------- 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 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
ash1 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
>
On Thu, 21 Jun 2007 14:46:49 -0300
Ranieri Mazili <ranieri.oliveira@terra.com.br> wrote:
Hi,
I noticed that if DIV_MES = 0 (= NULL) then the previous query
didn't go well. I rewrite the query. It's including a check statement
to replace NULL with 1.
At first, you need to create a table of months.
--
Masaru Sugawara
create table tbl_month (p_month integer);
insert into tbl_month values (1);
insert into tbl_month values (2);
insert into tbl_month values (3);
insert into tbl_month values (4);
insert into tbl_month values (5);
insert into tbl_month values (6);
insert into tbl_month values (7);
insert into tbl_month values (8);
insert into tbl_month values (9);
insert into tbl_month values (10);
insert into tbl_month values (11);
insert into tbl_month values (12);
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
fromcurrent_date) ) as h2, (select t1.p_month, coalesce (p1.n, 1) as n from
tbl_monthas t1 left outer join (select extract(month from production_date) as m,
count(distinct p0.production_date) as n from production as p0 where
extract(yearfrom production_date) = extract(year from current_date)
groupby extract(month from production_date) ) as p1 on (t1.p_month = p1.m) ) 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,
>
> Your solution works fine, I would appreciated your prompt reply.
> Thanks a lot
>
>
> -------- Original Message --------
> Subject: Re:[SQL] Subquery problems
> From: Masaru Sugawara <rk73@ghost.plala.or.jp>
> To: Ranieri Mazili <ranieri.oliveira@terra.com.br>
> Date: 21/6/2007 13:25
> > 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;
> >
> >
> >
> >
> >
> >