On 6/19/07, Ranieri Mazili <ranieri.oliveira@xxxxxxxxxxxx> 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
