Buscar horas trabajadas en rangos

Por cierto, Alejandro:

Esta consulta (abajo) que había montado yo tiene el mismo problema.
La consulta hace uso de una función Numbers().

La idea es hacer un join de los Rangos con Trabajoempleados, pero
eso después de cambiar los rangos (si horaini>horafin se suma 1 dia
a horafin) y de partir los registros TrabajoEmpleados que abarcan
mas de un día en varios registros. Para esto último es para lo que
uso la función Numbers().

A las fechas que vienen de Rangos se le suma la diferencia en
dias de las fechas en TrabajoEmpleados con la fecha cero y así
se puede ver si hay solapamientos con el periodo en TrabajoEmp.
Si estos rangos se solapan (join), el solapamiento en minutos
es lo que se suma en un total por rango.

La cosa funciona, pero tiene el mismo problema que tu consulta
con el último rango del dia si este no termina exactamente en
00:00. (Como el ejemplo que he puesto con 23:00-01:00)

Saludos,
Carlos

[
CREATE FUNCTION [dbo].[Numbers] (@min INT, @max INT, @step INT)
RETURNS @numbers TABLE (number int)
AS
BEGIN
IF @step <= 0
RETURN
WHILE @min <= @max
BEGIN
INSERT @numbers VALUES (@min)
SET @min = @min + @step
END
RETURN
END
select
left(convert(varchar, R.horaini, 8), 5) + '-' +
left(convert(varchar, R.horafin, 8), 5) as Rango,
isnull(sum(datediff(
mi,
case when t.horain >= dateadd(day ,datediff(day, 0,
T.horain),r.horaini)
then t.horain
else dateadd(day ,datediff(day, 0, T.horain),r.horaini)
end,
case when t.horaout <= dateadd(day ,datediff(day, 0,
T.horain),r.horafin)
then t.horaout
else dateadd(day ,datediff(day, 0, T.horain),r.horafin)
end
)),0)/60.0 as TotalHoras
from
(select
horaini,
case when horaini>horafin then dateadd(day,1,horafin)
else horafin
end as horafin
from rangos) R
left join
(select codempleado,
fecha,
case when n.number>0 and n.number <datediff(day,horain,horaout)
then dateadd(day, datediff(day,0,horain),n.number)
else horain
end as horain,
case when n.number<datediff(day,horain,horaout)
then dateadd(day, datediff(day,0,horaout),n.number)
else horaout
end as horaout
from trabajoempleados join dbo.numbers(0,10,1) N
on datediff(day, horain, horaout) >= N.number) T
on t.horaout >= dateadd(day ,datediff(day, 0, T.horain),r.horaini)
and
t.horain <= dateadd(day ,datediff(day, 0, T.horain), r.horafin)
group by r.horaini,r.horafin
order by R.horaini
-

Carlos,

Aun así, si cambio (por ejemplo) los rangos

23:00-00:00
00:00-01:00

por uno solo:

23:00-01:00

ya no funciona (da 0 horas en ese rango).

Pero esto es un rango especial que abarca horas de el dia sgte, por lo que
la logica que hemos de usar a de cambiar. Reconozco que es un problema
interesante.

Con:

ON b.h >= DATEPART(hour, r.[HORAINI])
AND b.h < DATEPART(hour, r.[HORAFIN]) +
CASE WHEN r.HORAINI > r.HORAFIN THEN 24 ELSE 0 END

no depende de que el rango termine exactamente a las 00:00 horas.

Interesante propuesta, me gusta.

De todas formas esta soluciónn dá 1 hora en ese rango, pero
(con los datos ejemplo de nuestro amigo) tienen que ser 2 horas.
Quizás tu puedas ver donde está la otra hora. :-)

Ya me estoy poniendo viejo, no veo por que a de dar ser 2. La unica fila que
incluye mas de un dia es:

INSERT INTO TRABAJOEMPLEADOS(CODEMPLEADO,FECHA,HORAIN,HORAOUT)
VALUES(1,'16/7/2008','16/7/2008 18:00','17/7/2008 01:15')

y el resto de las filas no incluye 23:00 -- 00:00.


AMB




"Carlos M. Calvelo" wrote:

Hola Alejandro,

On 19 jul, 00:08, Alejandro Mesa
wrote:
> Carlos,
>
> > Debería ser siempre así eh? :-)
>
> Asi es, pero eso no le ve quien esta de el otro lado.
>
> > Alejandro, excelente tu solución! Pero todavía tiene un problema.
> > Y es que para el rango 23-00 dá 0 pero tiene que ser 1.
> > Yo estoy teniendo exactamente el mismo problema, :-o vaya!!!
> > (haciendolo solo con sintaxis sql 2000) y no doy con ello!
>
> Muy buena atrapada!!!
>
> Eso se debe a que el rango es un poco raro 23 - 00,

Exacto! El único rango con HORAINI > HORAFIN.

> asi que le hice un
> pequeño arreglo a la condicion de union entre la CTE y la tabla rangos.
>
> ON b.h >= DATEPART(hour, r.[HORAINI])
> AND b.h < ISNULL(NULLIF(DATEPART(hour, r.[HORAFIN]), 0), 24)

Aun así, si cambio (por ejemplo) los rangos

23:00-00:00
00:00-01:00

por uno solo:

23:00-01:00

ya no funciona (da 0 horas en ese rango).

Con:

ON b.h >= DATEPART(hour, r.[HORAINI])
AND b.h < DATEPART(hour, r.[HORAFIN]) +
CASE WHEN r.HORAINI > r.HORAFIN THEN 24 ELSE 0 END

no depende de que el rango termine exactamente a las 00:00 horas.

De todas formas esta soluciónn dá 1 hora en ese rango, pero
(con los datos ejemplo de nuestro amigo) tienen que ser 2 horas.
Quizás tu puedas ver donde está la otra hora. :-)

Saludos,
Carlos

Carlos,

Sin duda de que es una brillante idea y reconozco que me ha tomado tiempo
entenderla.

Agradezco tu colaboracion en este hilo.


AMB


"Carlos M. Calvelo" wrote:

Por cierto, Alejandro:

Esta consulta (abajo) que había montado yo tiene el mismo problema.
La consulta hace uso de una función Numbers().

La idea es hacer un join de los Rangos con Trabajoempleados, pero
eso después de cambiar los rangos (si horaini>horafin se suma 1 dia
a horafin) y de partir los registros TrabajoEmpleados que abarcan
mas de un día en varios registros. Para esto último es para lo que
uso la función Numbers().

A las fechas que vienen de Rangos se le suma la diferencia en
dias de las fechas en TrabajoEmpleados con la fecha cero y así
se puede ver si hay solapamientos con el periodo en TrabajoEmp.
Si estos rangos se solapan (join), el solapamiento en minutos
es lo que se suma en un total por rango.

La cosa funciona, pero tiene el mismo problema que tu consulta
con el último rango del dia si este no termina exactamente en
00:00. (Como el ejemplo que he puesto con 23:00-01:00)

Saludos,
Carlos

[
CREATE FUNCTION [dbo].[Numbers] (@min INT, @max INT, @step INT)
RETURNS @numbers TABLE (number int)
AS
BEGIN
IF @step <= 0
RETURN
WHILE @min <= @max
BEGIN
INSERT @numbers VALUES (@min)
SET @min = @min + @step
END
RETURN
END
select
left(convert(varchar, R.horaini, 8), 5) + '-' +
left(convert(varchar, R.horafin, 8), 5) as Rango,
isnull(sum(datediff(
mi,
case when t.horain >= dateadd(day ,datediff(day, 0,
T.horain),r.horaini)
then t.horain
else dateadd(day ,datediff(day, 0, T.horain),r.horaini)
end,
case when t.horaout <= dateadd(day ,datediff(day, 0,
T.horain),r.horafin)
then t.horaout
else dateadd(day ,datediff(day, 0, T.horain),r.horafin)
end
)),0)/60.0 as TotalHoras
from
(select
horaini,
case when horaini>horafin then dateadd(day,1,horafin)
else horafin
end as horafin
from rangos) R
left join
(select codempleado,
fecha,
case when n.number>0 and n.number <> datediff(day,horain,horaout)
then dateadd(day, datediff(day,0,horain),n.number)
else horain
end as horain,
case when n.number<datediff(day,horain,horaout)
then dateadd(day, datediff(day,0,horaout),n.number)
else horaout
end as horaout
from trabajoempleados join dbo.numbers(0,10,1) N
on datediff(day, horain, horaout) >= N.number) T
on t.horaout >= dateadd(day ,datediff(day, 0, T.horain),r.horaini)
and
t.horain <= dateadd(day ,datediff(day, 0, T.horain), r.horafin)
group by r.horaini,r.horafin
order by R.horaini
-

Hola Alejandro,

On 19 jul, 20:43, Alejandro Mesa
wrote:

Carlos,

> Aun así, si cambio (por ejemplo) los rangos

> 23:00-00:00
> 00:00-01:00

> por uno solo:

> 23:00-01:00

> ya no funciona (da 0 horas en ese rango).

Pero esto es un rango especial que abarca horas de el dia sgte, por lo que
la logica que hemos de usar a de cambiar. Reconozco que es un problema
interesante.

> Con:

> ON b.h >= DATEPART(hour, r.[HORAINI])
> AND b.h < DATEPART(hour, r.[HORAFIN]) +
> CASE WHEN r.HORAINI > r.HORAFIN THEN 24 ELSE 0 END

> no depende de que el rango termine exactamente a las 00:00 horas.

Interesante propuesta, me gusta.

> De todas formas esta soluciónn dá 1 hora en ese rango, pero
> (con los datos ejemplo de nuestro amigo) tienen que ser 2 horas.
> Quizás tu puedas ver donde está la otra hora. :-)

Ya me estoy poniendo viejo, no veo por que a de dar ser 2. La unica fila que
incluye mas de un dia es:

INSERT INTO TRABAJOEMPLEADOS(CODEMPLEADO,FECHA,HORAIN,HORAOUT)
VALUES(1,'16/7/2008','16/7/2008 18:00','17/7/2008 01:15')

y el resto de las filas no incluye 23:00 -- 00:00.

Bueno, pues está claro se han trabajado 2 horas en ese rango no?
(entre las 23:00 y 01:00)

Con los rangos originales ( 23:00-00:00 y 00:00-01:00 ) nos dá una
hora en cada rango que vienen de ese mismo registro. Si en vez
de esos dos rangos tenemos solo el rango 23:00-01:00 pues tendrán
que ser dos horas.

No lo he vuelto a mirar. Pero si tengo un momento 'fresco', que
yo ya me estoy poniendo viejo también :-), y si consigo dar
con la solución, la pondré aquí.

Pues (como dices tu) si es un poblema interesante. Sobre todo
para solucionarlo de una forma lo mas genérica posible como me
lo imagino yo.

Saludos 'viejo', :-)
Carlos

Carlos,

El rango 23 - 01 implica desde las las 11:00 pm de el dia anterior hasta la
1:00 am de el dia posterior. Eso no implica que 23 - 01 sea igual a las horas
trabajadas en el rango 23:00 mas las horas trabajadas en el rango 00:01,
puesto que al decir 00:01 no sabemos si es anterior o posterior a las 23
horas. Para el tipo de problema que planteas, el orden cronologico debe ser
importante, de lo contrario el valor de el rango 01:23 seria el mismo que el
valor de el rango 23:01.

Sabes que, ese problema se lo dejo a quien posteo el problema original.

AMB


"Carlos M. Calvelo" wrote:

Hola Alejandro,

On 19 jul, 20:43, Alejandro Mesa
wrote:
> Carlos,
>
> > Aun así, si cambio (por ejemplo) los rangos
>
> > 23:00-00:00
> > 00:00-01:00
>
> > por uno solo:
>
> > 23:00-01:00
>
> > ya no funciona (da 0 horas en ese rango).
>
> Pero esto es un rango especial que abarca horas de el dia sgte, por lo que
> la logica que hemos de usar a de cambiar. Reconozco que es un problema
> interesante.
>
> > Con:
>
> > ON b.h >= DATEPART(hour, r.[HORAINI])
> > AND b.h < DATEPART(hour, r.[HORAFIN]) +
> > CASE WHEN r.HORAINI > r.HORAFIN THEN 24 ELSE 0 END
>
> > no depende de que el rango termine exactamente a las 00:00 horas.
>
> Interesante propuesta, me gusta.
>
> > De todas formas esta soluciónn dá 1 hora en ese rango, pero
> > (con los datos ejemplo de nuestro amigo) tienen que ser 2 horas.
> > Quizás tu puedas ver donde está la otra hora. :-)
>
> Ya me estoy poniendo viejo, no veo por que a de dar ser 2. La unica fila que
> incluye mas de un dia es:
>
> INSERT INTO TRABAJOEMPLEADOS(CODEMPLEADO,FECHA,HORAIN,HORAOUT)
> VALUES(1,'16/7/2008','16/7/2008 18:00','17/7/2008 01:15')
>
> y el resto de las filas no incluye 23:00 -- 00:00.
>

Bueno, pues está claro se han trabajado 2 horas en ese rango no?
(entre las 23:00 y 01:00)

Con los rangos originales ( 23:00-00:00 y 00:00-01:00 ) nos dá una
hora en cada rango que vienen de ese mismo registro. Si en vez
de esos dos rangos tenemos solo el rango 23:00-01:00 pues tendrán
que ser dos horas.

No lo he vuelto a mirar. Pero si tengo un momento 'fresco', que
yo ya me estoy poniendo viejo también :-), y si consigo dar
con la solución, la pondré aquí.

Pues (como dices tu) si es un poblema interesante. Sobre todo
para solucionarlo de una forma lo mas genérica posible como me
lo imagino yo.

Saludos 'viejo', :-)
Carlos