Date: Sat, 23 Jun 2007 10:07:22 +0900
From: Masaru Sugawara <rk73@ghost.plala.or.jp>
To: pgsql-sql@postgresql.org
Subject: Re: Subquery problems
In-Reply-To: <467AB989.9010809@terra.com.br>
References: <20070622004400.21B9.RK73@ghost.plala.or.jp>
	<467AB989.9010809@terra.com.br>
Message-Id: <20070622230811.E58E.RK73@ghost.plala.or.jp>
MIME-Version: 1.0
Content-Type: text/plain; charset="US-ASCII"
Content-Transfer-Encoding: 7bit

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 from current_date)
              ) as h2,
              (select t1.p_month, coalesce (p1.n, 1) as n
                 from tbl_month as 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(year from production_date) 
                             = extract(year from current_date)
                      group by 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;
> >
> >
> >
> >
> >
> >