sistema de victor

mysql

set @fechainicio = '2019-07-01';
set @fechafin = '2019-08-31';
select zca.grupo, zca.categoria, p.apellidos, p.nombres, d.* 
from personal p
inner join (
select 
	a.IdPersonal,
   	year(a.fecha) as anyo,
            month(a.fecha) as nromes, a.orden,
		    max( case when DAY(a.fecha) = 1 then a.Hora else '' end ) as d01,
    		max( case when DAY(a.fecha) = 2 then a.Hora else '' end ) as d02,
		    max( case when DAY(a.fecha) = 3 then a.Hora else '' end ) as d03,
		    max( case when DAY(a.fecha) = 4 then a.Hora else '' end ) as d04,
		    max( case when DAY(a.fecha) = 5 then a.Hora else '' end ) as d05,
		    max( case when DAY(a.fecha) = 6 then a.Hora else '' end ) as d06,
		    max( case when DAY(a.fecha) = 7 then a.Hora else '' end ) as d07,
		    max( case when DAY(a.fecha) = 8 then a.Hora else '' end ) as d08,
		    max( case when DAY(a.fecha) = 9 then a.Hora else '' end ) as d09,
		    max( case when DAY(a.fecha) = 10 then a.Hora else '' end ) as d10,
		    max( case when DAY(a.fecha) = 11 then a.Hora else '' end ) as d11,
		    max( case when DAY(a.fecha) = 12 then a.Hora else '' end ) as d12,
		    max( case when DAY(a.fecha) = 13 then a.Hora else '' end ) as d13,
		    max( case when DAY(a.fecha) = 14 then a.Hora else '' end ) as d14,
		    max( case when DAY(a.fecha) = 15 then a.Hora else '' end ) as d15,
		    max( case when DAY(a.fecha) = 16 then a.Hora else '' end ) as d16,
		    max( case when DAY(a.fecha) = 17 then a.Hora else '' end ) as d17,
		    max( case when DAY(a.fecha) = 18 then a.Hora else '' end ) as d18,
		    max( case when DAY(a.fecha) = 19 then a.Hora else '' end ) as d19,
		    max( case when DAY(a.fecha) = 20 then a.Hora else '' end ) as d20,
		    max( case when DAY(a.fecha) = 21 then a.Hora else '' end ) as d21,
		    max( case when DAY(a.fecha) = 22 then a.Hora else '' end ) as d22,
		    max( case when DAY(a.fecha) = 23 then a.Hora else '' end ) as d23,
		    max( case when DAY(a.fecha) = 24 then a.Hora else '' end ) as d24,
		    max( case when DAY(a.fecha) = 25 then a.Hora else '' end ) as d25,
		    max( case when DAY(a.fecha) = 26 then a.Hora else '' end ) as d26,
		    max( case when DAY(a.fecha) = 27 then a.Hora else '' end ) as d27,
		    max( case when DAY(a.fecha) = 28 then a.Hora else '' end ) as d28,
		    max( case when DAY(a.fecha) = 29 then a.Hora else '' end ) as d29,
		    max( case when DAY(a.fecha) = 30 then a.Hora else '' end ) as d30,
		    max( case when DAY(a.fecha) = 31 then a.Hora else '' end ) as d31
from (
select 
 b.IdPersonal, b.fecha, 
@nm :=if( @fc=b.fecha,@nm +1, 1) as orden,
b.hora,
@fc := b.fecha as dummy
from marcacion b,
(select @nm:=0) as t1,
(select @fc:= '0-0-0' ) as t2,
zclasificacion clas,
zcategoria cat
where  clas.idcategoria = cat.idcategoria
and clas.idclasificado = b.idpersonal
and b.fecha between @fechainicio and @fechafin
and b.idtipomarcacion = 1
order by b.idpersonal,b.fecha, b.hora
) a
group by a.idpersonal, year(a.fecha),month(a.fecha), a.orden
order by a.fecha,a.idpersonal, a.orden
 ) d on p.idpersonal = d.idpersonal
inner join zclasificacion zcl on p.idpersonal = zcl.idclasificado
inner join zcategoria zca on zcl.idcategoria = zca.idcategoria
order by 1, 2 ,3 ,4, d.anyo, d.nromes, d.orden

recordar

al registrar al personal
en zcategoria los valores posibles
en zclasificacion la relacion con esa tabla
para que salgan en la consulta de victor