Agrupando asistencias

mmm creo que lo deberias resolver en el cliente.
"Josué Maldonado" <josue|at@|lamundial.hn> escribió en el mensaje
news:

Hola lista,

Tengo un dilema o mejor dicho problema con un proceso de consulta de
asistencias. Las asistencias se almacena en una tabla que guarda un
registro por marca por empleado, la estructura de la tabla es
(asi_pk identity, asi_fecha char(10), asi_hora char(5),asi_empleadofk
int, asi_turnofk int, asi_entradasalida char(1), asi_marcanumero)

El inconveniente es que necesito presentar las asistencias del empleado
de forma columnar, algo asi como
empleado entrada salida entrada salida
1 07:55 12:05 13:00 15:00

Pero la cosa se complica aun mas cuando un empleado puede marcar mas de
2 pares de marcas de entrada salida las cuales se configuran en la
table de horarios.

Cualquier comentario o sugerencia sera muy bienvenido,


Sinceramente,

Josué Maldonado.

Hola:
Una recomendacion seria partir o dividir el problema en varios mas
pequenos, lo primero puede ser crear una vista/tabla/tabla-virtual que
tenga los datos de Hora de Entrada y Hora de Salida como columnas.
Asumiendo que no hay horarios nocturnos (que empiezan un dia y terminan
el siguiente). Es posible que algo como esto te sirva:
=SELECT CAST(Entrada.Asi_Fecha AS SMALLDATETIME),
Entrada.Asi_Empleadofk,
Entrada.Asi_Hora AS HoraEntrada,
MIN(Salida.Asi_Hora) AS HoraSalida
FROM MarcaEmpleado AS Entrada
LEFT JOIN MarcaEmpleado AS Salida
ON Entrada.Asi_Fecha=Salida.Asi_Fecha
AND Entrada.Asi_Empleadofk=Salida.Asi_Empleadofk
AND Entrada.Asi_Hora<Salida.Asi_Hora
AND Entrada.asi_entradasalida='E' -- Caracter de entrada
AND Salida.asi_entradasalida='S' -- Caracter de Salida
GROUP BY Entrada.Asi_Fecha, Entrada.Asi_Empleadofk, Entrada.Asi_Hora
=
Si esto te sirve podrias crear una vista con este SELECT, y construir el
resto de las setencias contra esto. Tambien si la tabla que sumistraste
proviene de un dispositivo externo como parece, podrias construir un Tabla e
Insertar el resultado de este SELECT.
Un comentario adicional es que aproveche para cambiar la fecha de CHAR
a SMALLDATETIME para hacer mas facil luego las comparaciones.
Para solucionar la siguiente parte del problema abria que conocer como
funciona la tabla de horarios y cual es su estructura. Pero basicamente se
convierte en un problema de "CROSSTAB". Si la posteas talvez podriamos
ayudarte.
Saludos,

Javier Loria
Costa Rica
Se aprecia la inclusion de DDL (CREATE, INSERTS, etc.)
que pueda ser copiado y pegado al Query Analizer.
La version de SQL y Service Pack tambien ayuda.

Josué Maldonado <josue|at@|lamundial.hn> escribio:

Hola lista,

Tengo un dilema o mejor dicho problema con un proceso de consulta de
asistencias. Las asistencias se almacena en una tabla que guarda un
registro por marca por empleado, la estructura de la tabla es
(asi_pk identity, asi_fecha char(10), asi_hora char(5),asi_empleadofk
int, asi_turnofk int, asi_entradasalida char(1), asi_marcanumero)

El inconveniente es que necesito presentar las asistencias del
empleado de forma columnar, algo asi como
empleado entrada salida entrada salida
1 07:55 12:05 13:00 15:00

Pero la cosa se complica aun mas cuando un empleado puede marcar mas
de 2 pares de marcas de entrada salida las cuales se configuran en
la table de horarios.

Cualquier comentario o sugerencia sera muy bienvenido,

Hola Javier,

Gracias por tu respuesta,

Javier Loria wrote:

Hola:
Una recomendacion seria partir o dividir el problema en varios mas
pequenos, lo primero puede ser crear una vista/tabla/tabla-virtual que
tenga los datos de Hora de Entrada y Hora de Salida como columnas.
Asumiendo que no hay horarios nocturnos (que empiezan un dia y terminan
el siguiente). Es posible que algo como esto te sirva:
=> SELECT CAST(Entrada.Asi_Fecha AS SMALLDATETIME),
Entrada.Asi_Empleadofk,
Entrada.Asi_Hora AS HoraEntrada,
MIN(Salida.Asi_Hora) AS HoraSalida
FROM MarcaEmpleado AS Entrada
LEFT JOIN MarcaEmpleado AS Salida
ON Entrada.Asi_Fecha=Salida.Asi_Fecha
AND Entrada.Asi_Empleadofk=Salida.Asi_Empleadofk
AND Entrada.Asi_Hora<Salida.Asi_Hora
AND Entrada.asi_entradasalida='E' -- Caracter de entrada
AND Salida.asi_entradasalida='S' -- Caracter de Salida
GROUP BY Entrada.Asi_Fecha, Entrada.Asi_Empleadofk, Entrada.Asi_Hora
=

Este codigo genera un registro por cada par de entrada/salida pero
tambien genera registros para el mismo par con nulos de esta forma:

Rfecha Asi_Empfk HoraEntrada HoraSalida
2004-02-09 00:00:00 1098 07:52:55 12:01:11
2004-02-09 00:00:00 1098 12:01:11 NULL
2004-02-09 00:00:00 1098 13:00:23 19:00:31
2004-02-09 00:00:00 1098 19:00:31 NULL

(4 row(s) affected)

Warning: Null value is eliminated by an aggregate or other SET operation.

Si esto te sirve podrias crear una vista con este SELECT, y construir el
resto de las setencias contra esto. Tambien si la tabla que sumistraste
proviene de un dispositivo externo como parece, podrias construir un Tabla e
Insertar el resultado de este SELECT.
Un comentario adicional es que aproveche para cambiar la fecha de CHAR
a SMALLDATETIME para hacer mas facil luego las comparaciones.
Para solucionar la siguiente parte del problema abria que conocer como
funciona la tabla de horarios y cual es su estructura. Pero basicamente se
convierte en un problema de "CROSSTAB". Si la posteas talvez podriamos
ayudarte.
Saludos,
Javier Loria
Costa Rica
Se aprecia la inclusion de DDL (CREATE, INSERTS, etc.)
que pueda ser copiado y pegado al Query Analizer.
La version de SQL y Service Pack tambien ayuda.

En efecto lo he tratado de resolver en el cliente con un crosstab pero
dadas las caracteristicas de este sistema en el cual un empleado puede
tener varios pares de marcas de entrada y salida durante un dia entonces
tambien se me ha dificultado encontrar la solucion apropiada, las
estructuras de las tablas asistencia y horarios por dia son las siguientes:

CREATE TABLE [dbo].[asistencia] (
[asi_pk] [int] NOT NULL ,
[asi_time] [datetime] NULL ,
[asi_inout] [char] (1) NULL ,
[asi_empfk] [int] NULL ,
[asi_horariofk] [smallint] NULL ,
[asi_idmarca] [int] NULL ,
[asi_tarde] [char] (1) NULL ,
[asi_dayfk] [smallint] NULL ,
[asi_marcanum] [char] (1) NULL ,
[asi_fecha] [char] (10) NULL ,
[asi_hora] [char] (5) NULL
) ON [PRIMARY]
go

CREATE TABLE [dbo].[horasxdia] (
[hxd_pk] [int] NULL ,
[hxd_hora] [char] (5) NULL ,
[hxd_hantes] [char] (5) NULL ,
[hxd_hdespues] [char] (5) NULL ,
[hxd_es] [char] (1) NULL ,
[hxd_dayno] [smallint] NULL ,
[hxd_turnodiafk] [int] NULL ,
[hxd_marcaid] [char] (1) NULL
) ON [PRIMARY]
go

insert into asistencia values
(2164,'2004-02-09 07:52:55','E',1098,2060,2060,' ',2,'1','20040209
','07:52:55')
go

insert into asistencia values
(2165,'2004-02-09 12:01:55','S',1098,2061,2061,' ',2,'1','20040209
','12:01:55')
go

insert into asistencia values
(2166,'2004-02-09 12:58:04','E',1098,2062,2062,' ',2,'2','20040209
','12:58:04')
go

insert into asistencia values
(2164,'2004-02-09 17:05:55','S',1098,2060,2060,' ',2,'2','20040209
','17:05:55')
go


En la tabla horas por dias estan definidas las marcas por dia por
horario, Estoy en etapa de diseño y como puedes notar hay redundancia en
algunas columnas, si hay alguna sugerencia o comentario sobre la
estructura de las tablas sera bienvenida. Estoy utilizando MSDE 2000 SP3
en un W2k pro sp3


Sinceramente,

Josué Maldonado.

Hola Josue:
Muchas gracias por el codigo de creacion de las tablas y los Inserts.,
El SELECT que te envie estaba incorrecto. El problema que tiene es el
uso del LEFT JOIN y las condiciones de Entrada en el ON. Por esta razon
debes pasar la condicion al WHERE. Si adicionalmente incluyes la columna del
Numero de Marca y la conviertes en una vista el codigo deberia ser:
=CREATE VIEW MarcasEmpleados
AS
SELECT CAST(Entrada.Asi_Fecha AS SMALLDATETIME) AS Fecha
, Entrada.asi_empfk
, Entrada.Asi_Hora AS HoraEntrada
, Entrada.Asi_Marcanum AS NumMarca
, MIN(Salida.Asi_Hora) AS HoraSalida
FROM Asistencia AS Entrada
LEFT JOIN Asistencia AS Salida
ON Entrada.Asi_Fecha=Salida.Asi_Fecha
AND Entrada.asi_empfk=Salida.asi_empfk
AND Entrada.Asi_Hora<Salida.Asi_Hora
AND Salida.asi_inout='S' -- Caracter de Salida
WHERE Entrada.asi_inout='E' -- Caracter de entrada
GROUP BY Entrada.Asi_Fecha
, Entrada.asi_empfk
, Entrada.Asi_Hora
, Entrada.Asi_Marcanum
=
Para hacer el "CROSSTAB" es ideal hacerlo en el cliente, pero si conoces la
cantidad "maxima" de marcas posibles en tiempo de diseno puedes hacerlo asi:
=SELECT T1.Fecha
, T1.Asi_EmpFK
, T1.HoraEntrada as Entrada1
, T1.HoraSalida as Salida1
, T2.HoraEntrada as Entrada2
, T2.HoraSalida as Salida2
, T3.HoraEntrada as Entrada3
, T3.HoraSalida as Salida3
FROM MarcasEmpleados AS T1
LEFT JOIN MarcasEmpleados AS T2
ON T1.Fecha=T2.Fecha
AND T2.NumMarca=2
LEFT JOIN MarcasEmpleados AS T3
ON T1.Fecha=T3.Fecha
AND T3.NumMarca=3
WHERE T1.NumMarca=1

=
Este es un ejemplo con 3 marcas pero es facil convertirlo a mas.
Espero te sirva,



Javier Loria
Costa Rica
Se aprecia la inclusion de DDL (CREATE, INSERTS, etc.)
que pueda ser copiado y pegado al Query Analizer.
La version de SQL y Service Pack tambien ayuda.

Josué Maldonado <josue|at@|lamundial.hn> escribio:

Hola Javier,

Gracias por tu respuesta,

Javier Loria wrote:

Hola:
Una recomendacion seria partir o dividir el problema en varios
mas pequenos, lo primero puede ser crear una
vista/tabla/tabla-virtual que tenga los datos de Hora de Entrada y
Hora de Salida como columnas. Asumiendo que no hay horarios
nocturnos (que empiezan un dia y terminan
el siguiente). Es posible que algo como esto te sirva:
=>> SELECT CAST(Entrada.Asi_Fecha AS SMALLDATETIME),
Entrada.Asi_Empleadofk,
Entrada.Asi_Hora AS HoraEntrada,
MIN(Salida.Asi_Hora) AS HoraSalida
FROM MarcaEmpleado AS Entrada
LEFT JOIN MarcaEmpleado AS Salida
ON Entrada.Asi_Fecha=Salida.Asi_Fecha
AND Entrada.Asi_Empleadofk=Salida.Asi_Empleadofk
AND Entrada.Asi_Hora<Salida.Asi_Hora
AND Entrada.asi_entradasalida='E' -- Caracter de entrada
AND Salida.asi_entradasalida='S' -- Caracter de Salida
GROUP BY Entrada.Asi_Fecha, Entrada.Asi_Empleadofk, Entrada.Asi_Hora
=>

Este codigo genera un registro por cada par de entrada/salida pero
tambien genera registros para el mismo par con nulos de esta forma:

Rfecha Asi_Empfk HoraEntrada HoraSalida
2004-02-09 00:00:00 1098 07:52:55 12:01:11
2004-02-09 00:00:00 1098 12:01:11 NULL
2004-02-09 00:00:00 1098 13:00:23 19:00:31
2004-02-09 00:00:00 1098 19:00:31 NULL

(4 row(s) affected)

Warning: Null value is eliminated by an aggregate or other SET
operation.

Si esto te sirve podrias crear una vista con este SELECT, y
construir el resto de las setencias contra esto. Tambien si la tabla
que sumistraste proviene de un dispositivo externo como parece,
podrias construir un Tabla e Insertar el resultado de este SELECT.
Un comentario adicional es que aproveche para cambiar la fecha
de CHAR a SMALLDATETIME para hacer mas facil luego las
comparaciones. Para solucionar la siguiente parte del problema
abria que conocer como funciona la tabla de horarios y cual es su
estructura. Pero basicamente se convierte en un problema de
"CROSSTAB". Si la posteas talvez podriamos ayudarte.
Saludos,

> Javier Loria
> Costa Rica
> Se aprecia la inclusion de DDL (CREATE, INSERTS, etc.)
> que pueda ser copiado y pegado al Query Analizer.
> La version de SQL y Service Pack tambien ayuda.

En efecto lo he tratado de resolver en el cliente con un crosstab pero
dadas las caracteristicas de este sistema en el cual un empleado puede
tener varios pares de marcas de entrada y salida durante un dia
entonces tambien se me ha dificultado encontrar la solucion
apropiada, las
estructuras de las tablas asistencia y horarios por dia son las
siguientes:

CREATE TABLE [dbo].[asistencia] (
[asi_pk] [int] NOT NULL ,
[asi_time] [datetime] NULL ,
[asi_inout] [char] (1) NULL ,
[asi_empfk] [int] NULL ,
[asi_horariofk] [smallint] NULL ,
[asi_idmarca] [int] NULL ,
[asi_tarde] [char] (1) NULL ,
[asi_dayfk] [smallint] NULL ,
[asi_marcanum] [char] (1) NULL ,
[asi_fecha] [char] (10) NULL ,
[asi_hora] [char] (5) NULL
) ON [PRIMARY]
go

CREATE TABLE [dbo].[horasxdia] (
[hxd_pk] [int] NULL ,
[hxd_hora] [char] (5) NULL ,
[hxd_hantes] [char] (5) NULL ,
[hxd_hdespues] [char] (5) NULL ,
[hxd_es] [char] (1) NULL ,
[hxd_dayno] [smallint] NULL ,
[hxd_turnodiafk] [int] NULL ,
[hxd_marcaid] [char] (1) NULL
) ON [PRIMARY]
go

insert into asistencia values
(2164,'2004-02-09 07:52:55','E',1098,2060,2060,' ',2,'1','20040209
','07:52:55')
go

insert into asistencia values
(2165,'2004-02-09 12:01:55','S',1098,2061,2061,' ',2,'1','20040209
','12:01:55')
go

insert into asistencia values
(2166,'2004-02-09 12:58:04','E',1098,2062,2062,' ',2,'2','20040209
','12:58:04')
go

insert into asistencia values
(2164,'2004-02-09 17:05:55','S',1098,2060,2060,' ',2,'2','20040209
','17:05:55')
go


En la tabla horas por dias estan definidas las marcas por dia por
horario, Estoy en etapa de diseño y como puedes notar hay redundancia
en algunas columnas, si hay alguna sugerencia o comentario sobre la
estructura de las tablas sera bienvenida. Estoy utilizando MSDE 2000
SP3
en un W2k pro sp3

Gracias Javier, me ha sido muy util tu ayuda aprecio mucho tu nobleza.

Saludos,

Sinceramente,

Josué Maldonado.

Javier Loria wrote:

Hola Josue:
Muchas gracias por el codigo de creacion de las tablas y los Inserts.,
El SELECT que te envie estaba incorrecto. El problema que tiene es el
uso del LEFT JOIN y las condiciones de Entrada en el ON. Por esta razon
debes pasar la condicion al WHERE. Si adicionalmente incluyes la columna del
Numero de Marca y la conviertes en una vista el codigo deberia ser:
=> CREATE VIEW MarcasEmpleados
AS
SELECT CAST(Entrada.Asi_Fecha AS SMALLDATETIME) AS Fecha
, Entrada.asi_empfk
, Entrada.Asi_Hora AS HoraEntrada
, Entrada.Asi_Marcanum AS NumMarca
, MIN(Salida.Asi_Hora) AS HoraSalida
FROM Asistencia AS Entrada
LEFT JOIN Asistencia AS Salida
ON Entrada.Asi_Fecha=Salida.Asi_Fecha
AND Entrada.asi_empfk=Salida.asi_empfk
AND Entrada.Asi_Hora<Salida.Asi_Hora
AND Salida.asi_inout='S' -- Caracter de Salida
WHERE Entrada.asi_inout='E' -- Caracter de entrada
GROUP BY Entrada.Asi_Fecha
, Entrada.asi_empfk
, Entrada.Asi_Hora
, Entrada.Asi_Marcanum
=>
Para hacer el "CROSSTAB" es ideal hacerlo en el cliente, pero si conoces la
cantidad "maxima" de marcas posibles en tiempo de diseno puedes hacerlo asi:
=> SELECT T1.Fecha
, T1.Asi_EmpFK
, T1.HoraEntrada as Entrada1
, T1.HoraSalida as Salida1
, T2.HoraEntrada as Entrada2
, T2.HoraSalida as Salida2
, T3.HoraEntrada as Entrada3
, T3.HoraSalida as Salida3
FROM MarcasEmpleados AS T1
LEFT JOIN MarcasEmpleados AS T2
ON T1.Fecha=T2.Fecha
AND T2.NumMarca=2
LEFT JOIN MarcasEmpleados AS T3
ON T1.Fecha=T3.Fecha
AND T3.NumMarca=3
WHERE T1.NumMarca=1

=>
Este es un ejemplo con 3 marcas pero es facil convertirlo a mas.
Espero te sirva,



Javier Loria
Costa Rica
Se aprecia la inclusion de DDL (CREATE, INSERTS, etc.)
que pueda ser copiado y pegado al Query Analizer.
La version de SQL y Service Pack tambien ayuda.

Josué Maldonado <josue|at@|lamundial.hn> escribio:

Hola Javier,

Gracias por tu respuesta,

Javier Loria wrote:

Hola:
Una recomendacion seria partir o dividir el problema en varios
mas pequenos, lo primero puede ser crear una
vista/tabla/tabla-virtual que tenga los datos de Hora de Entrada y
Hora de Salida como columnas. Asumiendo que no hay horarios
nocturnos (que empiezan un dia y terminan
el siguiente). Es posible que algo como esto te sirva:
=>>>SELECT CAST(Entrada.Asi_Fecha AS SMALLDATETIME),
Entrada.Asi_Empleadofk,
Entrada.Asi_Hora AS HoraEntrada,
MIN(Salida.Asi_Hora) AS HoraSalida
FROM MarcaEmpleado AS Entrada
LEFT JOIN MarcaEmpleado AS Salida
ON Entrada.Asi_Fecha=Salida.Asi_Fecha
AND Entrada.Asi_Empleadofk=Salida.Asi_Empleadofk
AND Entrada.Asi_Hora<Salida.Asi_Hora
AND Entrada.asi_entradasalida='E' -- Caracter de entrada
AND Salida.asi_entradasalida='S' -- Caracter de Salida
GROUP BY Entrada.Asi_Fecha, Entrada.Asi_Empleadofk, Entrada.Asi_Hora
=>>

Este codigo genera un registro por cada par de entrada/salida pero
tambien genera registros para el mismo par con nulos de esta forma:

Rfecha Asi_Empfk HoraEntrada HoraSalida
2004-02-09 00:00:00 1098 07:52:55 12:01:11
2004-02-09 00:00:00 1098 12:01:11 NULL
2004-02-09 00:00:00 1098 13:00:23 19:00:31
2004-02-09 00:00:00 1098 19:00:31 NULL

(4 row(s) affected)

Warning: Null value is eliminated by an aggregate or other SET
operation.

Si esto te sirve podrias crear una vista con este SELECT, y
construir el resto de las setencias contra esto. Tambien si la tabla
que sumistraste proviene de un dispositivo externo como parece,
podrias construir un Tabla e Insertar el resultado de este SELECT.
Un comentario adicional es que aproveche para cambiar la fecha
de CHAR a SMALLDATETIME para hacer mas facil luego las
comparaciones. Para solucionar la siguiente parte del problema
abria que conocer como funciona la tabla de horarios y cual es su
estructura. Pero basicamente se convierte en un problema de
"CROSSTAB". Si la posteas talvez podriamos ayudarte.
Saludos,

> Javier Loria
> Costa Rica
> Se aprecia la inclusion de DDL (CREATE, INSERTS, etc.)
> que pueda ser copiado y pegado al Query Analizer.
> La version de SQL y Service Pack tambien ayuda.

En efecto lo he tratado de resolver en el cliente con un crosstab pero
dadas las caracteristicas de este sistema en el cual un empleado puede
tener varios pares de marcas de entrada y salida durante un dia
entonces tambien se me ha dificultado encontrar la solucion
apropiada, las
estructuras de las tablas asistencia y horarios por dia son las
siguientes:

CREATE TABLE [dbo].[asistencia] (
[asi_pk] [int] NOT NULL ,
[asi_time] [datetime] NULL ,
[asi_inout] [char] (1) NULL ,
[asi_empfk] [int] NULL ,
[asi_horariofk] [smallint] NULL ,
[asi_idmarca] [int] NULL ,
[asi_tarde] [char] (1) NULL ,
[asi_dayfk] [smallint] NULL ,
[asi_marcanum] [char] (1) NULL ,
[asi_fecha] [char] (10) NULL ,
[asi_hora] [char] (5) NULL
) ON [PRIMARY]
go

CREATE TABLE [dbo].[horasxdia] (
[hxd_pk] [int] NULL ,
[hxd_hora] [char] (5) NULL ,
[hxd_hantes] [char] (5) NULL ,
[hxd_hdespues] [char] (5) NULL ,
[hxd_es] [char] (1) NULL ,
[hxd_dayno] [smallint] NULL ,
[hxd_turnodiafk] [int] NULL ,
[hxd_marcaid] [char] (1) NULL
) ON [PRIMARY]
go

insert into asistencia values
(2164,'2004-02-09 07:52:55','E',1098,2060,2060,' ',2,'1','20040209
','07:52:55')
go

insert into asistencia values
(2165,'2004-02-09 12:01:55','S',1098,2061,2061,' ',2,'1','20040209
','12:01:55')
go

insert into asistencia values
(2166,'2004-02-09 12:58:04','E',1098,2062,2062,' ',2,'2','20040209
','12:58:04')
go

insert into asistencia values
(2164,'2004-02-09 17:05:55','S',1098,2060,2060,' ',2,'2','20040209
','17:05:55')
go


En la tabla horas por dias estan definidas las marcas por dia por
horario, Estoy en etapa de diseño y como puedes notar hay redundancia
en algunas columnas, si hay alguna sugerencia o comentario sobre la
estructura de las tablas sera bienvenida. Estoy utilizando MSDE 2000
SP3
en un W2k pro sp3