Re: Subquery problems - Mailing list pgsql-sql

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