Reporte de marcación

Creado por LUIS ENRIQUE ILLESCAS MORALES, Modificado el Mar, 20 Feb, 2024 a 1:52 P. M. por LUIS ENRIQUE ILLESCAS MORALES

José Carlo

SELECT * FROM(

SELECT *, ROW_NUMBER() OVER (PARTITION BY nombreCompleto,Fecha ORDER BY Fecha) AS Contador

FROM (

select distinct

ui.BADGENUMBER,

replicate ('0',(10 - len(ui.BADGENUMBER))) + convert(varchar, ui.BADGENUMBER) as Nro_Agenda,

parametros.dbo.fc_agenda_nombre(replicate ('0',(10 - len(ui.BADGENUMBER))) + convert(varchar, ui.BADGENUMBER),null,null,null,null)as nombreCompleto,

convert(char(10), inout.CHECKTIME, 103) as Fecha,

convert(VARCHAR(5),inout.CHECKTIME,108) as 'Hora'

FROM puntual.dbrfidsolution.dbo.USERINFO ui

inner join puntual.dbrfidsolution.dbo.CHECKINOUTx inout on ui.USERID = inout.USERID

LEFT join ESCORPION.dbAgendaTelefonica.dbo.Agenda ag on ui.BADGENUMBER = ag.cod_agenda

left join Escorpion.dbAgendaTelefonica.dbo.Area ar on ag.idArea = ar.idArea

WHERE

inout.SENSORID = 1 --and ui.DEFAULTDEPTID = 3

and inout.CHECKTIME between '2024/01/25 00:00:00' and '2024/02/20 23:59:59'

/* and ag.idarea = 2000 --Servicios Estudiantiles*/

--SAE AND ui.BADGENUMBER IN (0000051309,0000006906,0000019783,0000043557,0000067466,0000054220,0000067902,0000045754,0000065020,0000049912,0000054043,0000056627,0000065146,0000067978) --EN CASO DE QUE DESEE DE ALGUN ADMINISTRATIVO EN ESPECIFICO

--registro AND ui.BADGENUMBER IN (0000047146,0000042734,0000047332,0000066971,0000034789,0000012330,0000051080,0000038387,0000067527)

--contabilidad AND ui.BADGENUMBER IN (0000003650,0000054468,0000051307,0000045393)

--AND ui.BADGENUMBER IN (0000056971)

) as Resultado

)AS Conteo

PIVOT

(

MAX(Hora)

FOR Contador IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])

) AS PivotTable

--Quitar Comentario si desea Ordenar por nombre o fecha

ORDER BY Fecha ASC

--nombreCompleto,

--Fecha

;