Suggest a feature
×

Welcome to TagMyCode

Please login or create account to add a snippet.
0
0
 
0
Language: SQL
Posted by: Carlos Luciano Garcia Zeman
Added: Mar 7, 2019 2:48 PM
Modified: Mar 7, 2019 2:48 PM
Views: 47
Tags: no tags
Sirve para poner nombres variables a las columnas de los informes.
  1.         DROP TEMPORARY TABLE IF EXISTS tmp_final;
  2.     CALL ssp_eval(CONCAT("CREATE TEMPORARY TABLE tmp_final ENGINE = MEMORY
  3.                 SELECT Comercio, NroComercio, COALESCE(tm.Monto, 0) as '", DATE_FORMAT(pPeriodo, '%m/%y'), "', COALESCE(tma.Monto, 0) as '", DATE_FORMAT(pPeriodoAnt, '%m/%y'), "',
  4.                         CONCAT(ROUND(COALESCE(((tm.Monto / tma.Monto) - 1 ) * 100, 0), 2), ' %') as 'Var%',
  5.            COALESCE(tmac.Monto, 0) 'Acum ",DATE_FORMAT(pPeriodo, '%m/%y'),"', COALESCE(tmaca.Monto,0) 'Acum ",DATE_FORMAT(pPeriodoAnt, '%m/%y'),"',
  6.                         CONCAT(ROUND(COALESCE(((tmac.Monto / tmaca.Monto) - 1 ) * 100, 0), 2), ' %') as 'Var Acum%'
  7.                 FROM Comercios c
  8.                 INNER JOIN GruposComercio gc ON c.IdGrupoComercio = gc.IdGrupoComercio
  9.                 LEFT JOIN Gestores g ON g.IdComercio = c.IdComercio
  10.                 LEFT JOIN tmp_base tm ON c.IdComercio = tm.IdComercio
  11.                 LEFT JOIN tmp_base_ant tma ON c.IdComercio = tma.IdComercio
  12.                 LEFT JOIN tmp_base_acum tmac ON c.IdComercio = tmac.IdComercio
  13.                 LEFT JOIN tmp_base_acum_ant tmaca ON c.IdComercio = tmaca.IdComercio
  14.                 WHERE EsPropio = 'S' AND g.IdComercio IS NULL;
  15.        "));
  16.    
  17.     SELECT * FROM tmp_final;