valeu....
SELECT grupo,Sum(dias1),Sum(consumomes1),Sum(estoqueMes1),
Sum(dias2),Sum(consumomes2),Sum(estoqueMes2),
Sum(dias3),Sum(consumomes3),Sum(estoqueMes3),
Sum(dias4),Sum(consumomes4),Sum(estoqueMes4),
Sum(dias5),Sum(consumomes5),Sum(estoqueMes5),
Sum(dias6),Sum(consumomes6),Sum(estoqueMes6),
Sum(dias7),Sum(consumomes7),Sum(estoqueMes7),
Sum(dias8),Sum(consumomes8),Sum(estoqueMes8),
Sum(dias9),Sum(consumomes9),Sum(estoqueMes9),
Sum(dias10),Sum(consumomes10),Sum(estoqueMes10),
Sum(dias11),Sum(consumomes11),Sum(estoqueMes11),
Sum(dias12),Sum(consumomes12),Sum(estoqueMes12)
FROM
(
--dias
SELECT e.grupo,
To_Number(To_Char(LAST_DAY(To_Date('01/01/' || to_char(m.data,'YYYY'),'dd/mm/yyyy')),'DD')) dias1,
0 consumomes1,0 estoqueMes1,
to_number(To_Char(LAST_DAY(To_Date('01/02/' || to_char(m.data,'YYYY'),'dd/mm/yyyy')),'DD')) dias2,
0 ConsumoMes2,0 estoqueMes2,
to_number(To_Char(LAST_DAY(To_Date('01/03/' || to_char(m.data,'YYYY'),'dd/mm/yyyy')),'DD')) dias3,
0 ConsumoMes3,0 estoqueMes3,
to_number(To_Char(LAST_DAY(To_Date('01/04/' || to_char(m.data,'YYYY'),'dd/mm/yyyy')),'DD')) dias4,
0 ConsumoMes4,0 estoqueMes4,
to_number(To_Char(LAST_DAY(To_Date('01/05/' || to_char(m.data,'YYYY'),'dd/mm/yyyy')),'DD')) dias5,
0 ConsumoMes5,0 estoqueMes5,
to_number(To_Char(LAST_DAY(To_Date('01/06/' || to_char(m.data,'YYYY'),'dd/mm/yyyy')),'DD')) dias6,
0 ConsumoMes6,0 estoqueMes6,
to_number(To_Char(LAST_DAY(To_Date('01/07/' || to_char(m.data,'YYYY'),'dd/mm/yyyy')),'DD')) dias7,
0 ConsumoMes7,0 estoqueMes7,
to_number(To_Char(LAST_DAY(To_Date('01/08/' || to_char(m.data,'YYYY'),'dd/mm/yyyy')),'DD')) dias8,
0 ConsumoMes8,0 estoqueMes8,
to_number(To_Char(LAST_DAY(To_Date('01/09/' || to_char(m.data,'YYYY'),'dd/mm/yyyy')),'DD')) dias9,
0 ConsumoMes9,0 estoqueMes9,
to_number(To_Char(LAST_DAY(To_Date('01/10/' || to_char(m.data,'YYYY'),'dd/mm/yyyy')),'DD')) dias10,
0 ConsumoMes10,0 estoqueMes10,
to_number(To_Char(LAST_DAY(To_Date('01/11/' || to_char(m.data,'YYYY'),'dd/mm/yyyy')),'DD')) dias11,
0 ConsumoMes11,0 estoqueMes11,
to_number(To_Char(LAST_DAY(To_Date('01/12/' || to_char(m.data,'YYYY'),'dd/mm/yyyy')),'DD')) dias12,
0 ConsumoMes12,0 estoqueMes12
FROM movimento m,cadastro e
WHERE m.cd_material = e.cd_material
and m.data between To_Date('01/01/' || to_char(TO_DATE('01/12/2010','dd/mm/yyyy'),'YYYY'),'dd/mm/yyyy')
and last_day(TO_DATE('01/12/2010','dd/mm/yyyy')) + 86399/86400
GROUP BY
to_char(data, 'YYYY'),e.grupo
HAVING
to_char(data, 'YYYY') = to_char(TO_DATE('01/12/2010','dd/mm/yyyy'), 'YYYY' )
union
--consumo
SELECT e.grupo,
0 dias1,SUM(decode(EXTRACT(MONTH FROM data), 1,vl_consumo,0)) ConsumoMes1,0 estoqueMes1,
0 dias2,SUM(decode(EXTRACT(MONTH FROM data), 2,vl_consumo,0)) ConsumoMes2,0 estoqueMes2,
0 dias3,SUM(decode(EXTRACT(MONTH FROM data), 3,vl_consumo,0)) ConsumoMes3,0 estoqueMes3,
0 dias4,SUM(decode(EXTRACT(MONTH FROM data), 4,vl_consumo,0)) ConsumoMes4,0 estoqueMes4,
0 dias5,SUM(decode(EXTRACT(MONTH FROM data), 5,vl_consumo,0)) ConsumoMes5,0 estoqueMes5,
0 dias6,SUM(decode(EXTRACT(MONTH FROM data), 6,vl_consumo,0)) ConsumoMes6,0 estoqueMes6,
0 dias7,SUM(decode(EXTRACT(MONTH FROM data), 7,vl_consumo,0)) ConsumoMes7,0 estoqueMes7,
0 dias8,SUM(decode(EXTRACT(MONTH FROM data), 8,vl_consumo,0)) ConsumoMes8,0 estoqueMes8,
0 dias9,SUM(decode(EXTRACT(MONTH FROM data), 9,vl_consumo,0)) ConsumoMes9,0 estoqueMes9,
0 dias10,SUM(decode(EXTRACT(MONTH FROM data),10,vl_consumo,0)) ConsumoMes10,0 estoqueMes10,
0 dias11,SUM(decode(EXTRACT(MONTH FROM data), 11,vl_consumo,0)) ConsumoMes11,0 estoqueMes11,
0 dias12,SUM(decode(EXTRACT(MONTH FROM data), 12,vl_consumo,0)) ConsumoMes12,0 estoqueMes12
FROM movimento m,cadastro e
WHERE m.cd_material = e.cd_material
and m.data between To_Date('01/01/' || to_char(TO_DATE('01/12/2010','dd/mm/yyyy'),'YYYY'),'dd/mm/yyyy')
and last_day(TO_DATE('01/12/2010','dd/mm/yyyy')) + 86399/86400
GROUP BY
to_char(data, 'YYYY'),e.grupo
HAVING
to_char(data, 'YYYY') = to_char(TO_DATE('01/12/2010','dd/mm/yyyy'), 'YYYY' )
UNION
--estoque
SELECT e.grupo,
0 dias1,0 consumomes1,SUM(decode(EXTRACT(MONTH FROM m.data), 1,m.vl_estoque,0)) EstoqueMes1,
0 dias2,0 consumomes2,SUM(decode(EXTRACT(MONTH FROM m.data), 2,m.vl_estoque,0)) EstoqueMes2,
0 dias3,0 consumomes3,SUM(decode(EXTRACT(MONTH FROM m.data), 3,m.vl_estoque,0)) EstoqueMes3,
0 dias4,0 consumomes4,SUM(decode(EXTRACT(MONTH FROM m.data), 4,m.vl_estoque,0)) EstoqueMes4,
0 dias5,0 consumomes5,SUM(decode(EXTRACT(MONTH FROM m.data), 5,m.vl_estoque,0)) EstoqueMes5,
0 dias6,0 consumomes6,SUM(decode(EXTRACT(MONTH FROM m.data), 6,m.vl_estoque,0)) EstoqueMes6,
0 dias7,0 consumomes7,SUM(decode(EXTRACT(MONTH FROM m.data), 7,m.vl_estoque,0)) EstoqueMes7,
0 dias8,0 consumomes8,SUM(decode(EXTRACT(MONTH FROM m.data), 8,m.vl_estoque,0)) EstoqueMes8,
0 dias9,0 consumomes9,SUM(decode(EXTRACT(MONTH FROM m.data), 9,m.vl_estoque,0)) EstoqueMes9,
0 dias10,0 consumomes10,SUM(decode(EXTRACT(MONTH FROM m.data), 10,m.vl_estoque,0)) EstoqueMes10,
0 dias11,0 consumomes11,SUM(decode(EXTRACT(MONTH FROM m.data), 11,m.vl_estoque,0)) EstoqueMes11,
0 dias12,0 consumomes12,SUM(decode(EXTRACT(MONTH FROM m.data), 12,m.vl_estoque,0)) EstoqueMes12
FROM estoque m, cadastro e
WHERE m.cd_material = e.cd_material
and m.data between To_Date('01/01/' || to_char(TO_DATE('01/12/2010','dd/mm/yyyy'),'YYYY'),'dd/mm/yyyy')
and last_day(TO_DATE('01/12/2010','dd/mm/yyyy')) + 86399/86400
GROUP BY
to_char(m.data, 'YYYY'),e.grupo
HAVING
to_char(m.data, 'YYYY') = to_char(TO_DATE('01/12/2010','dd/mm/yyyy'), 'YYYY' )
ORDER BY 1
)
GROUP BY grupo
ORDER BY 1