CREATE PROCEDURE `CargaDeJueces` (IN `p_desde_dia_carga` INT, IN `p_hasta_dia_carga` INT, IN `p_desde_anteriores` INT, IN `p_base_historico` INT, IN `p_fecha_competencia` DATE)   BEGIN
SELECT  
  mj.nombre,
  mj.juez_id, 
SUM(CASE WHEN (ta.IPP = 1 OR ta.JO = 1) and (me.start_time > p_desde_dia_carga AND me.start_time < p_hasta_dia_carga OR me.start_time < p_desde_dia_carga AND me.end_time > p_desde_dia_carga) then 1 ELSE 0 END ) HOY,
SUM(CASE WHEN ta.IPP = 1 and me.start_time > p_desde_anteriores and me.start_time < p_desde_dia_carga and noContarEstaAudiencia = 0 then 1 ELSE 0 END) PASADASIPP,
SUM(CASE WHEN ta.JO = 1 and me.start_time > p_desde_anteriores and me.start_time < p_desde_dia_carga and noContarEstaAudiencia = 0 then 3 ELSE 0 END) PASADASDEBATE,
SUM(CASE WHEN ta.IPP = 1 and me.start_time > p_desde_anteriores and me.start_time < p_desde_dia_carga and noContarEstaAudiencia = 0 then 1 
		 WHEN ta.JO = 1  and me.start_time > p_desde_anteriores and me.start_time < p_desde_dia_carga and noContarEstaAudiencia = 0 then 3
			ELSE 0 END) PASADASTOT,
SUM(CASE WHEN (ta.IPP = 1 OR ta.JO = 1) and me.start_time > p_desde_dia_carga then 1 ELSE 0 END) FUTURAS,
SUM(CASE WHEN (me.start_time > p_desde_dia_carga AND me.start_time < p_hasta_dia_carga OR me.start_time < p_desde_dia_carga AND me.end_time > p_desde_dia_carga) AND ta.TipoAudiencia IN ('Juicio Debate','Juicio Debate Querella') AND ta.TipoAudiencia <> 'Veredicto' then 1 ELSE 0 END ) DEBATE,
SUM(CASE WHEN (me.start_time > p_desde_dia_carga AND me.start_time < p_hasta_dia_carga OR me.start_time < p_desde_dia_carga AND me.end_time > p_desde_dia_carga) AND ta.TipoAudiencia = 'Licencia' then 1 ELSE 0 END ) LICENCIA,
SUM(CASE WHEN (me.start_time > p_desde_dia_carga AND me.start_time < p_hasta_dia_carga OR me.start_time < p_desde_dia_carga AND me.end_time > p_desde_dia_carga) AND ta.TipoAudiencia = 'Fundamentos' then 1 ELSE 0 END ) FUNDAMENTOS,
SUM(CASE WHEN (me.start_time > p_desde_dia_carga AND me.start_time < p_hasta_dia_carga OR me.start_time < p_desde_dia_carga AND me.end_time > p_desde_dia_carga) AND ta.TipoAudiencia = 'Deliberacion' then 1 ELSE 0 END ) DELIBERACION,
SUM(CASE WHEN (me.start_time > p_desde_dia_carga AND me.start_time < p_hasta_dia_carga OR me.start_time < p_desde_dia_carga AND me.end_time > p_desde_dia_carga) AND ta.TipoAudiencia = 'Veredicto' then 1 ELSE 0 END ) VEREDICTO,
SUM(CASE WHEN (ta.IPP = 1 OR ta.JO = 1) AND ta.cuenta = 1
    and (me.start_time > p_desde_dia_carga AND me.start_time < p_hasta_dia_carga OR me.start_time < p_desde_dia_carga AND me.end_time > p_desde_dia_carga) then 1 ELSE 0 END ) + mj.Coordinacion  INDICE,
SUM(CASE WHEN (me.start_time > p_desde_dia_carga AND me.start_time < p_hasta_dia_carga OR me.start_time < p_desde_dia_carga AND me.end_time > p_desde_dia_carga) AND ta.id = 'IP75' then 1 ELSE 0 END ) ACT_CSJ,
(SELECT mc.competencia FROM mrbs_competencia_jueces mc where mc.juez_id = mj.juez_id AND desde <= p_fecha_competencia AND hasta >= p_fecha_competencia),
SUM(CASE WHEN (ta.IPP = 1 OR ta.JO = 1) and (me.start_time > p_desde_dia_carga AND me.start_time < p_hasta_dia_carga OR me.start_time < p_desde_dia_carga AND me.end_time > p_desde_dia_carga) then (me.end_time-me.start_time) ELSE 0 END )/60 minutos
FROM                   
	mrbs_entry me
    inner join mrbs_tipo_audiencia ta on me.tipoAudiencia_id = ta.id 
	left OUTER join mrbs_juez mj on me.juez_id=mj.juez_id OR me.juez2_id=mj.juez_id OR me.juez3_id=mj.juez_id
	inner join mrbs_room mr on me.room_id=mr.id
	WHERE
    me.start_time > p_base_historico AND mj.juez_id <> 'JT00' AND mj.juez_id <> 'JC00' AND me.estado <> 'Cancelada' AND mj.instancia = 1 AND me.NoContarEstaAudiencia = 0 AND mj.activo = 1
  GROUP BY
    mj.nombre,mj.juez_id 
  ORDER BY
    LICENCIA, DEBATE, DELIBERACION, VEREDICTO, ACT_CSJ, FUNDAMENTOS, minutos, PASADASIPP, mj.juez_id Desc ;
END$$