Consulta rango de tiempos

Carlos,

El link que te pase sobre un articulo de Alex Kuznetsov no es el correcto.
Este es el link que queria que vieras.

Storing intervals of time with no overlaps.
http://sqlblog.com/blogs/alexander_...rlaps.aspx


AMB


"Carlos M. Calvelo" wrote:

Hola kojikabutosv,

On 17 aug, 17:54, kojikabutosv wrote:
> Gracias Alejandro y Carlos voy a estudiar lo que me plantean, según
> entiendo puedo meter este código en una función verdad? para que me
> devuelva un valor para saber si existe o no.
>

Si. Como ya he sugerido se puede poner en una función y usar
esa función en un constraint (check).

Como ya me he metido en el hilo y parece que no ha quedado muy claro,
pues mejor explicarlo bien. Además esta pregunta sobre control de
solapamientos suele reaparecer de vez en cuando y quizás valga la
pena montar el caso poco a poco con explicaciones.

Bien. Pues obviamente empezamos con una tabla, digamos
'PeriodosTrabajadosPorEmpleado', que muy originalmente llamanos T :-)
para la que necesitamos los siguientes columnas: codemp, horainicio,
horafin. Y queremos que para el mismo empleado los períodos
(horainicio..horafin) no se solapen.

Tenemos: T {codeemp, horainicio, horafin}

Veamos que restricciones tenemos:
1. horainicio debe ser menor o igual a horafin
2. La combinación {codemp, horainicio} es única (clave natural).
3. La combinación {codemp, horafin} es única (otra clave natural).
4. Periodos (horainicio..horafin) del mismo empleado no se solapan.

Como ya explicó Alejando y teniendo en cuenta la restrición 1.
(horainicio <= horafin), dos registros r1 y r2 del mismo empleado
NO se solapan si

r1.fin < r2.horainicio OR r1.horanicio > r2.horafin

Entonces SÍ se solapan cuando se cumple la negación de lo anterior:

NOT (r1.fin < r2.horainicio OR r1.horanicio > r2.horafin)

que es lo mismo que

r1.fin >= r2.horainicio AND r1.horanicio <= r2.horafin

Aunque esta última condición se puede enteder en si misma, si no
se entiende pues se puede derivar así. Además se puede controlar
enumerando todos los tipos de solapamientos posibles que uno se
pueda imaginar y controlando que es así para todos los casos.

Consideramos ahora restricción 2. y 3. Tendríamos que definir una
restrinción UNIQUE para las dos (dos claves, vamos), pero si ya
tenemos restricción 1. y 4. (no hay solapamientos) entonces 2. y
3. ya se cumplen! Dos registros del mismo codemp con la misma
horainicio o la misma horafin se solaparían. Se pueden definir
índices con esos atributos por otra razones, pero no hace falta
que sean 'unique' porque eso es implícito a como hemos definido
el 'no solaparse'.

Cuidado: como hemos definido el solapamiento arriba, si para el
mismo codemp tenemos un registro con horafin igual a la horainicio
de otro registro, entonces se solapan. Podríamos haber considerado
que eso no es solapamiento, y entonces si necesitaríamos definir
explícitamente una de las dos claves. La otra sería implícita.

Bien, hasta aquí lo lógico. Ahora vamos a definir la tabla y,
como restricción para el solapamiento, un check que utiliza
una función.

Lo que sí conviene, como veremos mas tarde, es añadir otro
atributo (tu registroid) que lógicamente no es necesario como
ya hemos visto (ya tenemos claves!) y utilizarlo como clave
sucedánea. Vamos, un primary key sin mas significado que ese:
diferenciar un registro de otro con solo una columna.


Creamos la tabla (para las horas usamos datetime!):
CREATE TABLE T
(
registroid INT NOT NULL IDENTITY(1,1),
codemp INT NOT NULL,
horainicio DATETIME NOT NULL,
horafin DATETIME NOT NULL,


CONSTRAINT T_PK PRIMARY KEY (registroid),
CONSTRAINT T_iniLEfin CHECK (horainicio <= horafin)

)

Creamos la función:
CREATE FUNCTION existeSolap (@regid INT, @cemp INT,
@hini DATETIME, @hfin DATETIME)
RETURNS BIT -- o un INT
AS
/* precondition: @hini <= @hfin (otro constraint) */
BEGIN
IF EXISTS
( SELECT * FROM T
WHERE codemp = @cemp AND
registroid <> @regid AND
horafin >= @hini AND horainicio <= @hfin
)
RETURN 1 -- se solapan
RETURN 0 -- no se solapan
END

Añadimos ahora la restricción de solapamiento:
ALTER TABLE T
ADD CONSTRAINT T_SeSolapa
CHECK(dbo.existeSolap(registroid, codemp, horainicio, horafin) = 0)

Como ves en el check se la pasan los nuevos valores de los campos a
la función, tanto para inserts como para updates.
Como había comentado antes, es importante saber que registro es el
que estamos actualizando (update) para así comparar los valores
que se están pasando con todos los demás registros, pero no con
el mismo registro con los valores anteriores (registroid <> @regid).
Si no hicieramos eso no podríamos hacer un update solo porque el
nuevo período se podría solapar con la versión anterior del mismo
registro. Por las mismas razones registroid no debería ser
'updatable', para poder identificar siempre un registro con su
versión
anterior.

Bueno, trata ahora de hacer inserts y updates que se solapen (o no)
con los dos siguientes registros:

insert T (codemp,horainicio,horafin)
values (1,'19000101 08:00:00.000','19000101 08:59:59.997')
insert T (codemp,horainicio,horafin)
values (1,'19000101 10:00:00.000','19000101 10:59:59.997')


Se ha hecho un poco largo, pero era también para dejar ver como
todo está interrelacionado. Por ejemplo como una restricción puede
implicar otras sin tener que implementarlas explícitamente.

Bueno, aquí lo dejo. Otro ejercicio sería tratar de hacer lo
mismo, pero con triggers. Tendrías que considerar los mismos
problemas, pero haciendo joins entre las tablas T, inserted y
deleted en los triggers.

Saludos,
Carlos

Hola Alejandro,

On 18 aug, 15:22, Alejandro Mesa
wrote:

Carlos,

Estupenda la respuesta; Creo que el OP quedara mucho mas claro esta vez.

Todavia SQL Server necesita mejorar un poquito como RDBMS, pues carece de
algunas facilidades que pueden hacer nuestras vidas un poquito mas facil.

Ejemplo:

- Deferred constraint checking (chequear la restriccion cuando la
transaccion se graba y no antes)

Las restricciones se chequean en medio de la transacciones y no al final,
por lo que si queremos, por ejemplo, intercambiar la hora de inicio final de
los dos registros que insertastes, tendriamos que hacerlo moviendo un
registro hacia un rango no existente, cambiar el segundo hacia el rango que
usaba el primero, luego cambiar el rango de el primero que movimos hacia el
rango que usaba el segundo. Esto implicaria tres operaciones de actualizacion.

El 'deferred checking' es necesario, pero no debería ser a nivel
de transacción. Cada insert, update o delete se puede interpretar
como la asignación de otro valor a la tabla (entendiendo la tabla
como variable y el conjunto de registros en un determinado momento,
su valor actual.) Lo que necesitamos es 'multiple assignment'.
Entonces el control de las restricciones se hace al final de cada
operación (digamos al encontrar un punto y coma), pero una
operación puede estan compuesta de varias (separadas por comas).
O sea: 'multiple assignment'.

- asserts

Las restricciones por ejemplo CHECK, no pueden usar data de otras tablas.
Por ejemplo, si queremos que un cierto valor no sea mayor a la suma de los
valores de una cierta columna de un grupo de filas en otra tabla.

Claro. Lo de la funcion (o recurrir a triggers) para controlar los
solapamientos es una ñapa porque en el check no podemos expresar
una expresión arbitrariamente compleja, como por ejemplo:

CHECK(
NOT EXISTS
( SELECT * FROM T
WHERE T.codemp = NEW.codemp AND
T.registroid <> NEW.registroid AND
T.horafin >= NEW.horafin AND
T.horainicio <= NEW.horafin
)
)

La sintaxis me la invento yo, pero se entiende la idea.

- Ultimo pero no menos importante es que no tenemos tipo de dato intervalo
de tiempo.

Esta teoria la puedes leer aqui. Creo que seria de mucha utilidad tener este
tipo de data en SQL Server.

Lo cual tampoco sería problema pudiendo definir tipos de datos
con sus operaciones (también arbitrariamente complejos).

TYPE PERIODO
{
TIME horainicio,
TIME horafin

CONSTRAINT horainicio <= horafin
}

OPERATION OVERLAPS (p1:PERIODO, p2:PERIODO):BOOLEAN
{
}


y ahora definir columnas de tipo PERIODO y el CHECK vendría a dar
algo como:

CHECK(
NOT EXISTS
( SELECT * FROM T
WHERE OVERLAPS(T.periodo, NEW.periodo)
)
)

o algo así! :-) Solo trato de comunicar la idea.

Publications of Richard Snodgrass (Developing Time-Oriented Database
Applications in SQL)http://www.cs.arizona.edu/people/rt...tions.html

Gracias Alejandro. Ya lo conocía.
El pdf ya lleva tiempo en mi disco duro. :-)

Saludos,
Carlos

Hola Alejandro,

On 18 aug, 16:15, Alejandro Mesa
wrote:

Carlos,

El link que te pase sobre un articulo de Alex Kuznetsov no es el correcto.
Este es el link que queria que vieras.

Storing intervals of time with no overlaps.http://sqlblog.com/blogs/alexander_...8/stori...

Este ya lo veo más interesante. En realidad está montando
un linked list de periodos !

Saludos,
Carlos

Si a?n te sirve:

http://spreadsheets.google.com/ccc?key
qhA2aO9RdwgdG1fWEtpSnlQMGJMd3NrYllULXBEaGc&hl=es



kojikabutosv wrote:

Consulta rango de tiempos
15-ago-09

He estado tratando de implementar una consulta pero no ya no doy "bola",
como puedo saber si un rango de horas dadas se encuentran en la base de
datos, por ejemplo

en la tabla tengo

registroid codemp horainicio horafina

1 20 08:00 09:0
2 21 13:00 14:0

pero quiero hacer la consulta que si yo envi

para el empleado 20 si yo le envio 08:30 a 9:00 me devuelva que ya
existe ese horario ocupado, pero si le devuelvo 09:10 a 10:00 que
aparezca disponible

agradezco mucho su ayuda puesto que trato con varios between pero no me
devuelve los datos correctos

Saludos,

EggHeadCafe - Software Developer Portal of Choice
License ASP.NET Applications - Desaware vs Microsoft SLP
http://www.eggheadcafe.com/tutorial...icati.aspx