Consulta rango de tiempos

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 17 aug, 17:04, Alejandro Mesa
wrote:

Hola Carlos,

Gracias, creo que es importante lo que comentastes.

Parece que no estás muy convencido. Quizás haya sido
demasiado breve.

Solamente chequeando durante la operacion de insert no es suficiente, se
debe tener algun mecanismo que chequee la solapacion (lo dije bien?)

Solapamiento. :-)

rangos a nivel de base de dato, puede ser un trigger u otro mecanismo.

Mira mi última reacción al OP.

A
proposito, hay una forma sugerida por un colega SQL Server MVP, Alexander
Kuznetsov, donde se hace uso de la denormalizacion para crear restricciones
de clave foranea autoreferenciales. Aca te paso el link, porque se que tu vas
a apreciar este articulo.

Denormalizing to enforce business rules: Part 1.http://sqlblog.com/blogs/alexander_...1/denor...

Mirándolo así por encima, con la respuesta que más me identifico
yo es con la de TroyK. :-) Pero esa ya es otra discusión.

Gracias por el enlace; lo miraré más detenidamente.

Saludos,
Carlos

Carlos,

Parece que no estás muy convencido. Quizás haya sido
demasiado breve.

Vaya impresion que he dado. Si estoy convencido, lo he usado anteriormente y
es por eso que use la palabra "importante". Se que he perdido mucho de el
idioma espaniol (teclado ingles-no internacional) pero creo que esa palabra
habla por si misma.


AMB


"Carlos M. Calvelo" wrote:

Hola Alejandro,

On 17 aug, 17:04, Alejandro Mesa
wrote:
> Hola Carlos,
>
> Gracias, creo que es importante lo que comentastes.

Parece que no estás muy convencido. Quizás haya sido
demasiado breve.

>
> Solamente chequeando durante la operacion de insert no es suficiente, se
> debe tener algun mecanismo que chequee la solapacion (lo dije bien?)

Solapamiento. :-)


> rangos a nivel de base de dato, puede ser un trigger u otro mecanismo.

Mira mi última reacción al OP.

> A
> proposito, hay una forma sugerida por un colega SQL Server MVP, Alexander
> Kuznetsov, donde se hace uso de la denormalizacion para crear restricciones
> de clave foranea autoreferenciales. Aca te paso el link, porque se que tu vas
> a apreciar este articulo.
>
> Denormalizing to enforce business rules: Part 1.http://sqlblog.com/blogs/alexander_...1/denor...
>

Mirándolo así por encima, con la respuesta que más me identifico
yo es con la de TroyK. :-) Pero esa ya es otra discusión.

Gracias por el enlace; lo miraré más detenidamente.

Saludos,
Carlos

On 18 aug, 14:57, Alejandro Mesa
wrote:

Carlos,

> Parece que no estás muy convencido. Quizás haya sido
> demasiado breve.

Vaya impresion que he dado. Si estoy convencido, lo he usado anteriormente y
es por eso que use la palabra "importante". Se que he perdido mucho de el
idioma espaniol (teclado ingles-no internacional) pero creo que esa palabra
habla por si misma.

Perdona Alejandro.
Me he liado con: *creo que es* importante.
También se puede interpretar como tu dices.

(Por cierto... en haber perdido soltura con el idioma, ya somos dos!)

Saludos,
Carlos

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.

- 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.

- 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.

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


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