Buscar horas trabajadas en rangos

On 18 jul, 09:38, sqlTest wrote:

Hola grácias por vuestra ayuda,

Creo que no me explique bien al comentar mi problema.

Lo que necesito es calcular la horas totales trabajadas en
determinados rangos.
La estructura de la tabla donde están los registros es la siguiente:

** Empleado ** - código del empleado
** Fecha ** - Se guarda la fecha en que entra el empleado
** HoraEntrada  ** - Se guarda la fecha y la hora en que entra el
empleado
** HoraSalida   ** - Se guarda la fecha y la hora en que sale el
empleado

Por ejemplo, tengo los siguientes registros y quiero saber las horas
realizadas desde las 18 hasta las 19 horas

Empleado   Fecha      HoraEntrada           HoraSalida
TotHoras de 18-19
1                17/7/2008  17/7/2008 17:50       17/7/2008 20:00
1
2         17/7/2008  17/7/2008 15:00       17/7/2008 18:20     0.20
1                18/7/2008  18/7/2008 18:30       18/7/2008 01:00
0.30

Muchísimas grácias por vuestra ayuda.

Pon aquí los CREATE TABLE para TrabajoEmpleados y
para la tabla donde se definen los rangos. Pon además los
INSERT con datos ejemplo con un par de casos normales
y un par de casos que dices son problemáticos. Pon además
el resultado que tu esperas a partir de esos datos. Todo eso
lo mas simplificado que sea posible.

Será así mucho mas fácil ayudarte.

Saludos,
Carlos

cuando un empleado comienza en un dia (a las 23 pej)
horas) y finaliza al dia siguiente (a las 2 horas por ejemplo.

Como has implementado esto en la db?

1 - Ingresando dos filas, una por cada dia
2 - La Fecha de salida es menor a la de entrada


AMB


"sqlTest" wrote:

Hola,
tengo la tabla "TrabajoEmpleados" con la estructura siguiente:

CódigoEmpleado Fecha HoraEntrada HoraSalida
-
-

Por otro lado tengo todos los rangos de las horas de 1 dia como
5-6,6-7,...12-13,..23:0,..hasta 4-5

Bueno el tema es que necesito sumar el número de horas totales
trabajadas en un determinado rango pero después de realizar muchísimas
pruebas no doy con la solución ya que lo que me da más problemas es
cuando un empleado comienza en un dia (a las 23 pej)
horas) y finaliza al dia siguiente (a las 2 horas por ejemplo.

Cabe decir también que estoy utilizando sql-server 2005.

Muchísimas grácias por cualquier ayuda

Carlos,

Esta es la tabla de Trabajoempleados

CREATE TABLE TRABAJOEMPLEADOS (
CODEMPLEADO int NOT NULL,
FECHA Datetime NOT NULL,
HORAIN Datetime NOT NULL,
HORAOUT Datetime NOT NULL,
CONSTRAINT TRABAJOEMPLEADOS_PK PRIMARY KEY (CODEMPLEADO, DIA,
HORAIN));

Y esta es la tabla de los rangos

CREATE TABLE RANGOS(HORAINI DATETIME,HORAFIN DATETIME)

INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('5:00','6:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('6:00','7:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('7:00','8:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('8:00','9:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('9:00','10:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('10:00','11:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('11:00','12:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('12:00','13:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('13:00','14:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('14:00','15:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('15:00','16:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('16:00','17:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('17:00','18:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('18:00','19:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('19:00','20:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('20:00','21:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('21:00','22:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('22:00','23:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('23:00','00:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('00:00','01:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('01:00','02:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('02:00','03:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('03:00','04:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('04:00','05:00')

Aquí pongo los inserts de ejemplo de empleados.

INSERT INTO TRABAJOEMPLEADOS(CODEMPLEADO,DIA,HORAIN,HORAOUT)
VALUES(1,'15/7/2008',
'15/7/2008 17:50','15/7/2008 21:15')
INSERT INTO TRABAJOEMPLEADOS(CODEMPLEADO,DIA,HORAIN,HORAOUT)
VALUES(2,'15/7/2008',
'15/7/2008 16:05','15/7/2008 20:00')
INSERT INTO TRABAJOEMPLEADOS(CODEMPLEADO,DIA,HORAIN,HORAOUT)
VALUES(1,'16/7/2008',
'16/7/2008 18:00','17/7/2008 01:15')
INSERT INTO TRABAJOEMPLEADOS(CODEMPLEADO,DIA,HORAIN,HORAOUT)
VALUES(2,'16/7/2008',
'16/7/2008 16:01','16/7/2008 20:10')

Pues el resultado que me gustaría que mostrara la sql es el siguiente

Rango Totalhoras
5:00-6:00 0
6:00-7:00 0
7:00-8:00 0
8:00-9:00 0
9:00-10:00 0
10:00-11:00 0
11:00-12:00 0
12:00-13:00 0
13:00-14:00 0
14:00-15:00 0
15:00-16:00 0
16:00-17:00 0,9
17:00-18:00 2,16
18:00-19:00 4
19:00-20:00 4
20:00-21:00 2,16
21:00-22:00 1,25
22:00-23:00 1
23:00-00:00 1
00:00-01:00 1
01:00-02:00 0,25
02:00-03:00 0
03:00-04:00 0
04:00-05:00

Alejandro > Fecha Entrada seria 17/7/2008 23:00 y la salida 18/7/2008
02:00

Muchas grácias por vuestra ayuda

Que bueno que posteastes la estructura de las tablas y data de ejemplo.

Una posible solucion puede ser:

;WITH
L0 AS (SELECT 1 AS n UNION ALL SELECT 1),
L1 AS (SELECT 1 AS n FROM L0 AS a, L0 AS b),
L2 AS (SELECT 1 AS n FROM L1 AS a, L1 AS b),
L3 AS (SELECT 1 AS n FROM L2 AS a, L2 AS b),
L4 AS (SELECT 1 AS n FROM L3 AS a, L3 AS b),
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS n FROM L4),
[te]
AS
(
SELECT
a.[CODEMPLEADO],
a.[FECHA],
a.[HORAIN],
a.[HORAOUT],
DATEPART(hour, DATEADD(minute, b.n - 1, a.[HORAIN])) h
FROM
TRABAJOEMPLEADOS AS a
INNER JOIN
Nums AS b
ON b.n <= DATEDIFF(minute, a.HORAIN, a.HORAOUT)
)
SELECT
RIGHT('00' + DATENAME(hour, r.[HORAINI]), 2) + ' - ' + RIGHT('00' +
DATENAME(hour, r.[HORAFIN]), 2) AS rango,
COUNT(b.h) / 60.00 AS TotalHoras
FROM
[RANGOS] AS r
LEFT OUTER JOIN
te AS b
ON b.h >= DATEPART(hour, r.[HORAINI]) AND b.h < DATEPART(hour, r.[HORAFIN])
GROUP BY
r.[HORAINI],
r.[HORAFIN]
ORDER BY
r.[HORAINI],
r.[HORAFIN]
GO


La idea es expandir cada fila de la tabla [TRABAJOEMPLEADOS], y crear una
fila por cada minuto en el intervalo [HORAIN] - [HORAOUT].

Por ejemplo, para la fila:

INSERT INTO TRABAJOEMPLEADOS(CODEMPLEADO,DIA,HORAIN,HORAOUT)
VALUES(1,'15/7/2008',
'15/7/2008 17:50','15/7/2008 21:15')

tendriamos algo asi como:

1,'15/7/2008', '15/7/2008 17:50', '15/7/2008 21:15', 17
1,'15/7/2008', '15/7/2008 17:51', '15/7/2008 21:15', 17
1,'15/7/2008', '15/7/2008 17:52', '15/7/2008 21:15', 17
1,'15/7/2008', '15/7/2008 17:53', '15/7/2008 21:15', 17
1,'15/7/2008', '15/7/2008 17:54', '15/7/2008 21:15', 17
...
1,'15/7/2008', '15/7/2008 21:13', '15/7/2008 21:15', 21
1,'15/7/2008', '15/7/2008 21:14', '15/7/2008 21:15', 21

Fijate en la hora de inicio y la ultima columna que es la hora de esa hora
de inicio. Luego unimos esta expansion con la tabla [RANGOS], usando la
condicion:

ON b.h >= DATEPART(hour, r.[HORAINI]) AND b.h < DATEPART(hour, r.[HORAFIN])

agrupamos para cada rango y contamos cuantas filas machan de el lado de la
CTE, que son los minutos por cada hora en el rango. Luego dividimos esa
cuenta entre 60.00 para ver el numero de horas.


AMB



"sqlTest" wrote:

> Carlos,

Esta es la tabla de Trabajoempleados

CREATE TABLE TRABAJOEMPLEADOS (
CODEMPLEADO int NOT NULL,
FECHA Datetime NOT NULL,
HORAIN Datetime NOT NULL,
HORAOUT Datetime NOT NULL,
CONSTRAINT TRABAJOEMPLEADOS_PK PRIMARY KEY (CODEMPLEADO, DIA,
HORAIN));

Y esta es la tabla de los rangos

CREATE TABLE RANGOS(HORAINI DATETIME,HORAFIN DATETIME)

INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('5:00','6:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('6:00','7:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('7:00','8:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('8:00','9:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('9:00','10:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('10:00','11:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('11:00','12:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('12:00','13:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('13:00','14:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('14:00','15:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('15:00','16:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('16:00','17:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('17:00','18:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('18:00','19:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('19:00','20:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('20:00','21:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('21:00','22:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('22:00','23:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('23:00','00:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('00:00','01:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('01:00','02:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('02:00','03:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('03:00','04:00')
INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('04:00','05:00')

Aquí pongo los inserts de ejemplo de empleados.

INSERT INTO TRABAJOEMPLEADOS(CODEMPLEADO,DIA,HORAIN,HORAOUT)
VALUES(1,'15/7/2008',
'15/7/2008 17:50','15/7/2008 21:15')
INSERT INTO TRABAJOEMPLEADOS(CODEMPLEADO,DIA,HORAIN,HORAOUT)
VALUES(2,'15/7/2008',
'15/7/2008 16:05','15/7/2008 20:00')
INSERT INTO TRABAJOEMPLEADOS(CODEMPLEADO,DIA,HORAIN,HORAOUT)
VALUES(1,'16/7/2008',
'16/7/2008 18:00','17/7/2008 01:15')
INSERT INTO TRABAJOEMPLEADOS(CODEMPLEADO,DIA,HORAIN,HORAOUT)
VALUES(2,'16/7/2008',
'16/7/2008 16:01','16/7/2008 20:10')

Pues el resultado que me gustaría que mostrara la sql es el siguiente

Rango Totalhoras
5:00-6:00 0
6:00-7:00 0
7:00-8:00 0
8:00-9:00 0
9:00-10:00 0
10:00-11:00 0
11:00-12:00 0
12:00-13:00 0
13:00-14:00 0
14:00-15:00 0
15:00-16:00 0
16:00-17:00 0,9
17:00-18:00 2,16
18:00-19:00 4
19:00-20:00 4
20:00-21:00 2,16
21:00-22:00 1,25
22:00-23:00 1
23:00-00:00 1
00:00-01:00 1
01:00-02:00 0,25
02:00-03:00 0
03:00-04:00 0
04:00-05:00

Alejandro > Fecha Entrada seria 17/7/2008 23:00 y la salida 18/7/2008
02:00

Muchas grácias por vuestra ayuda

Se me olvido mencionar que esta solucion es para SS 2005 y que debes tener
cuidado con el numero de filas a procesar, pues recuerda que cada fila se
expande de acuerdo al numero de minutos trabajados por cada turno.


AMB

"Alejandro Mesa" wrote:

Que bueno que posteastes la estructura de las tablas y data de ejemplo.

Una posible solucion puede ser:

;WITH
L0 AS (SELECT 1 AS n UNION ALL SELECT 1),
L1 AS (SELECT 1 AS n FROM L0 AS a, L0 AS b),
L2 AS (SELECT 1 AS n FROM L1 AS a, L1 AS b),
L3 AS (SELECT 1 AS n FROM L2 AS a, L2 AS b),
L4 AS (SELECT 1 AS n FROM L3 AS a, L3 AS b),
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS n FROM L4),
[te]
AS
(
SELECT
a.[CODEMPLEADO],
a.[FECHA],
a.[HORAIN],
a.[HORAOUT],
DATEPART(hour, DATEADD(minute, b.n - 1, a.[HORAIN])) h
FROM
TRABAJOEMPLEADOS AS a
INNER JOIN
Nums AS b
ON b.n <= DATEDIFF(minute, a.HORAIN, a.HORAOUT)
)
SELECT
RIGHT('00' + DATENAME(hour, r.[HORAINI]), 2) + ' - ' + RIGHT('00' +
DATENAME(hour, r.[HORAFIN]), 2) AS rango,
COUNT(b.h) / 60.00 AS TotalHoras
FROM
[RANGOS] AS r
LEFT OUTER JOIN
te AS b
ON b.h >= DATEPART(hour, r.[HORAINI]) AND b.h < DATEPART(hour, r.[HORAFIN])
GROUP BY
r.[HORAINI],
r.[HORAFIN]
ORDER BY
r.[HORAINI],
r.[HORAFIN]
GO


La idea es expandir cada fila de la tabla [TRABAJOEMPLEADOS], y crear una
fila por cada minuto en el intervalo [HORAIN] - [HORAOUT].

Por ejemplo, para la fila:

> INSERT INTO TRABAJOEMPLEADOS(CODEMPLEADO,DIA,HORAIN,HORAOUT)
> VALUES(1,'15/7/2008',
> '15/7/2008 17:50','15/7/2008 21:15')

tendriamos algo asi como:

1,'15/7/2008', '15/7/2008 17:50', '15/7/2008 21:15', 17
1,'15/7/2008', '15/7/2008 17:51', '15/7/2008 21:15', 17
1,'15/7/2008', '15/7/2008 17:52', '15/7/2008 21:15', 17
1,'15/7/2008', '15/7/2008 17:53', '15/7/2008 21:15', 17
1,'15/7/2008', '15/7/2008 17:54', '15/7/2008 21:15', 17
...
1,'15/7/2008', '15/7/2008 21:13', '15/7/2008 21:15', 21
1,'15/7/2008', '15/7/2008 21:14', '15/7/2008 21:15', 21

Fijate en la hora de inicio y la ultima columna que es la hora de esa hora
de inicio. Luego unimos esta expansion con la tabla [RANGOS], usando la
condicion:

ON b.h >= DATEPART(hour, r.[HORAINI]) AND b.h < DATEPART(hour, r.[HORAFIN])

agrupamos para cada rango y contamos cuantas filas machan de el lado de la
CTE, que son los minutos por cada hora en el rango. Luego dividimos esa
cuenta entre 60.00 para ver el numero de horas.


AMB



"sqlTest" wrote:

> > Carlos,
>
> Esta es la tabla de Trabajoempleados
>
> CREATE TABLE TRABAJOEMPLEADOS (
> CODEMPLEADO int NOT NULL,
> FECHA Datetime NOT NULL,
> HORAIN Datetime NOT NULL,
> HORAOUT Datetime NOT NULL,
> CONSTRAINT TRABAJOEMPLEADOS_PK PRIMARY KEY (CODEMPLEADO, DIA,
> HORAIN));
>
> Y esta es la tabla de los rangos
>
> CREATE TABLE RANGOS(HORAINI DATETIME,HORAFIN DATETIME)
>
> INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('5:00','6:00')
> INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('6:00','7:00')
> INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('7:00','8:00')
> INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('8:00','9:00')
> INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('9:00','10:00')
> INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('10:00','11:00')
> INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('11:00','12:00')
> INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('12:00','13:00')
> INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('13:00','14:00')
> INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('14:00','15:00')
> INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('15:00','16:00')
> INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('16:00','17:00')
> INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('17:00','18:00')
> INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('18:00','19:00')
> INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('19:00','20:00')
> INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('20:00','21:00')
> INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('21:00','22:00')
> INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('22:00','23:00')
> INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('23:00','00:00')
> INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('00:00','01:00')
> INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('01:00','02:00')
> INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('02:00','03:00')
> INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('03:00','04:00')
> INSERT INTO RANGOS(HORAINI,HORAFIN) VALUES('04:00','05:00')
>
> Aquí pongo los inserts de ejemplo de empleados.
>
> INSERT INTO TRABAJOEMPLEADOS(CODEMPLEADO,DIA,HORAIN,HORAOUT)
> VALUES(1,'15/7/2008',
> '15/7/2008 17:50','15/7/2008 21:15')
> INSERT INTO TRABAJOEMPLEADOS(CODEMPLEADO,DIA,HORAIN,HORAOUT)
> VALUES(2,'15/7/2008',
> '15/7/2008 16:05','15/7/2008 20:00')
> INSERT INTO TRABAJOEMPLEADOS(CODEMPLEADO,DIA,HORAIN,HORAOUT)
> VALUES(1,'16/7/2008',
> '16/7/2008 18:00','17/7/2008 01:15')
> INSERT INTO TRABAJOEMPLEADOS(CODEMPLEADO,DIA,HORAIN,HORAOUT)
> VALUES(2,'16/7/2008',
> '16/7/2008 16:01','16/7/2008 20:10')
>
> Pues el resultado que me gustaría que mostrara la sql es el siguiente
>
> Rango Totalhoras
> 5:00-6:00 0
> 6:00-7:00 0
> 7:00-8:00 0
> 8:00-9:00 0
> 9:00-10:00 0
> 10:00-11:00 0
> 11:00-12:00 0
> 12:00-13:00 0
> 13:00-14:00 0
> 14:00-15:00 0
> 15:00-16:00 0
> 16:00-17:00 0,9
> 17:00-18:00 2,16
> 18:00-19:00 4
> 19:00-20:00 4
> 20:00-21:00 2,16
> 21:00-22:00 1,25
> 22:00-23:00 1
> 23:00-00:00 1
> 00:00-01:00 1
> 01:00-02:00 0,25
> 02:00-03:00 0
> 03:00-04:00 0
> 04:00-05:00
>
> Alejandro > Fecha Entrada seria 17/7/2008 23:00 y la salida 18/7/2008
> 02:00
>
> Muchas grácias por vuestra ayuda
>