(Resolvido) Pivot somando numero de propostas

[crazySerk]

Fiz umas pequenas alterações na Pivot que o Dennis disponibilizou aqui no forum, no qual ela faz uma soma total do valor Real da empresa no ano, porém ao invés de somar eu quero saber quantas propostas que foram enviadas em cada mes... bem eu fiz uma mudança no local que realizava um "SUM" para "COUNT"...

Ficando dessa maneira...

CONCAT(M_Columns,
                            ' COUNT( CASE WHEN ',P_Column_Field,'=''',M_Column_Field,'''',
                            ' THEN ',P_Value,
                            ' ELSE 0 END) AS `', M_Column_Field ,'`,');

Funcionou em partes... ele me mostrou o valor correto das proposta... ( tenho cadastradas 6 propostas para uma empresa e 2 para outra...) mas ele repetiu este valor para todos os meses e não fez a separação das mesmas por data.. por exemplo as 6 Propostas que ele me mostrou 2 são do mes 11, e o restante do mes 10...

Eu chamo a pivot com isso:

CALL rel_anual_prop (
						  "b.nome",
						  "month(a.data_cadastro)",
						  "a.cod_proposta",
						  "propostas a",
						  " left outer join empresas_lojas b on
						(b.cod_loja = a.cod_loja) 
						where a.cod_empresa = 6"
						);

a pivot completa está assim:

DELIMITER $$

USE `iwebctrl`$$

DROP PROCEDURE IF EXISTS `rel_anual_prop`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `rel_anual_prop`(IN P_Row_Field VARCHAR(255), IN P_Column_Field VARCHAR(255), IN P_Value VARCHAR(255),
   IN P_From VARCHAR(4000),IN P_Where VARCHAR(4000))
BEGIN
   DECLARE done INT DEFAULT 0;
   DECLARE M_Count_Columns INT DEFAULT 0;
   DECLARE M_Column_Field VARCHAR(60);
   DECLARE M_Columns VARCHAR(8000) DEFAULT '';
   DECLARE M_sqltext VARCHAR(8000);
   DECLARE M_stmt VARCHAR(8000);
   DECLARE cur1 CURSOR FOR SELECT CAST(Column_Field AS CHAR) FROM tem_rel_prop;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
   DROP TABLE IF EXISTS tem_rel_prop;
   SET @M_sqltext = CONCAT('CREATE TEMPORARY TABLE tem_rel_prop ',
                           ' SELECT DISTINCT ',P_Column_Field, ' AS Column_Field',
                           ' FROM ',P_From,
                           P_Where,
                           ' ORDER BY ', P_Column_Field);
   PREPARE M_stmt FROM @M_sqltext;
   EXECUTE M_stmt;
   SELECT COUNT(*) INTO M_Count_Columns
   FROM tem_rel_prop
   WHERE Column_Field IS NOT NULL;
   IF (M_Count_Columns > 0) THEN
      OPEN cur1;
      REPEAT
         FETCH cur1 INTO M_Column_Field;
         IF (NOT done) AND (M_Column_Field IS NOT NULL) THEN
            SET M_Columns = CONCAT(M_Columns,
                            ' COUNT( CASE WHEN ',P_Column_Field,'=''',M_Column_Field,'''',
                            ' THEN ',P_Value,
                            ' ELSE 0 END) AS `', M_Column_Field ,'`,');
         END IF;
      UNTIL done END REPEAT;
      SET M_Columns = LEFT(M_Columns,LENGTH(M_Columns)-1);
      SET @M_sqltext = CONCAT('SELECT ',P_Row_Field,',',M_Columns,
                              ' FROM ', P_From,
                               P_Where,
                              ' GROUP BY ', P_Row_Field,
                              ' ORDER BY ', P_Row_Field);
                
      PREPARE M_stmt FROM @M_sqltext;
      EXECUTE M_stmt;
   END IF;
END$$

DELIMITER ;

[Denis Courcy]

crazySerk,

Lembra o que falei no tópico anterior? Faça o select com group by, verifique os resultados e passe os dados do select para o pivot table. Sua modificação fez com que o pivot table perdesse a função. Refaça seus passos desde o início.

[crazySerk]

Denis consegui resolver o problema, porém gostaria de uma ajuda em outro caso, onde que posso mudar no pivot ao invés de mostras as colunas 1,2,3,4...

exibir, jan, fev,mar,abr, mai,jun...

é possivel?

[Denis Courcy]

Denis consegui resolver o problema, porém gostaria de uma ajuda em outro caso, onde que posso mudar no pivot ao invés de mostras as colunas 1,2,3,4...

exibir, jan, fev,mar,abr, mai,jun...

é possivel?

Crie uma function que retorne char(3) e que receba como parametro um integer.

DELIMITER $$;

DROP FUNCTION IF EXISTS `meubancodedados`.`mesExtenso`$$

CREATE FUNCTION `meubancodedados`.`mesExtenso` (mes smallint unsigned)
	RETURNS char(3)
BEGIN
   RETURN ELT(mes,"JAN","FEV","MAR","ABR","MAI","JUN","JUL","AGO","SET","OUT","NOV","DEZ");
END$$

DELIMITER ;$$

Chame assim:

select mesExtenso(5);

A resposta é MAI

[crazySerk]

Correto, porém onde colocaria isso na pivot?

Nesse trecho aqui?

 IF (NOT done) AND (M_Column_Field IS NOT NULL) THEN
            SET M_Columns = CONCAT(M_Columns,
                            ' SUM( CASE WHEN ',P_Column_Field,'=''',M_Column_Field,'''',
                            ' THEN ',P_Value,
                            ' ELSE 0 END) AS `', SELECT mesExtenso(M_Column_Field) ,'`,');

Obrigado novamente, pela sua atenção!

Editado Novembro 23, 2013 por crazySerk

[Denis Courcy]

Pelo amor de Deus, não faça isto na pivot. Passe a função como um parâmetro. É muito mais fácil. Deixe a pivot em paz.

Usando seu código alguns posts acima faça:

CALL rel_anual_prop (
   "b.nome",
   "mesExtenso(month(a.data_cadastro))",
   "1",
   "propostas a
    left outer join empresas_lojas b on
	(b.cod_loja = a.cod_loja)",
   "where a.cod_empresa = 6");

O "1" é para contar o a.cod_proposta.

[crazySerk]

Opá agora funcionou perfeitamente... e desculpe ai Denis, banco de dados ainda sou novato, mas agradeço mesmo assim sua boa vontade.

Obrigado!