Skip to content

Instantly share code, notes, and snippets.

@gmocamilotd
Last active Sep 20, 2019
Embed
What would you like to do?
sistema de victor
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
al registrar al personal
en zcategoria los valores posibles
en zclasificacion la relacion con esa tabla
para que salgan en la consulta de victor
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment