Access / Visual basic: Calcular disponibilidad de los empleados

Cyttorak
09 de Septiembre del 2004
Hola

Tengo una BBDD en la que aparecen (entre otras) las siguientes tablas:

EMPLEADOS (con clave primaria DNI)
PARTICIPA_EN (con DNI como clave ajena para saber que empleado esta participando)
EMPLEADO_CONTRATO (con DNI como clave ajena para saber que empleado esta participando)

Tanto PARTICIPA_EN como EMPLEADO_CONTRATO tienen un campo Jornada que indica en la primera tabla las horas al dia que dedica a una "participacion" en particular, y en el segundo caso, el numero de horas diarias por las que esta contratado. Tambien tienen un campo Fecha_de_inicio y Fecha_de_fin para delimitar el periodo de la participacion y de la contratacion respectivamente.

Tengo un formulario donde el usuario introduce una fecha de inicio y otra de fin y se construye una select que obtiene a todos los empleados que dentro de ese periodo no estan participando en nada, es decir, estan libres.

La select es la siguiente:

Select distinct [EMPLEADOS].[NOMBRE_Y_APELLIDOS], [EMPLEADOS].[DNI], [EMPLEADOS].[COD_EMPRESA], [EMPLEADOS].[TLF_FIJO], [EMPLEADOS].[TLF_MOVIL], [EMPLEADOS].[CORREO_ELECTRONICO]
FROM
EMPLEADOS
Left JOIN
(
Select distinct [EMPLEADOS].[DNI]
FROM
EMPLEADOS
INNER JOIN
PARTICIPA_EN
ON (EMPLEADOS.DNI=PARTICIPA_EN.DNI)
WHERE
(
(PARTICIPA_EN.FECHA_DE_FIN BETWEEN #" & Me!Fecha_de_inicio & "# AND #" & Me!Fecha_de_fin & "#)
OR
(PARTICIPA_EN.FECHA_DE_INICIO BETWEEN #" & Me!Fecha_de_inicio & "# AND #" & Me!Fecha_de_fin & "#)
)
) As T2
On (EMPLEADOS.DNI=T2.DNI)
WHERE
(T2.DNI Is Null)

Como veis esta select no consulta EMPLEADO_CONTRATO para nada, y es que en realidad no esta bien, porque lo que necesitaria de verdad es que el usuario tambien diera cuantas horas de disponibilidad al dia necesita de manera que al buscar se mostraran a los empleados que cumpliesen lo seguiente:

[(Horas por dia del empleado contratadas dentro del intervalo de tiempo seleccionado) - (Suma de las horas que ya esta dedicando a otras "participaciones" en el dia mas ocupado que tenga dentro del intervalo de tiempo seleccionado)] > (horas por dia que se necesite que este libre)

Mi problema es que no se como plantear esto para sacar la informacion.
Ademas hay que mirarlo todo en conjunto, por ejemplo:
Si necesitamos a alguien disponible del dia 1 al dia 5 y aunque el empleado no tenga contrato del dia 1 al 5 si tiene uno del dia 1 al 2 y otro del 3 al 5 hay que tenerle en cuenta, y ademas como enc ada contrato puede tener una jornada distinta, a la hora de hacer la resta hay que tenerlo en cuetna para finalmente quedarse con la cota minima de disponibilidad. Lo mismo se puede decir de las participaciones, ademas estas pueden solaparse, asi que los dias que esta participando en mas de una cosa esta ocupado la suma de todas esas horas etc etc

Es posible que esto con SQL no se pueda hacer directamente y necesite codigo VBA pero en eso no estoy muy puesto.

¿Alguien puede ayudarme?
¿Alguna idea?


Se me ha ocurrido una cosa algo rebuscada (y que ademas no se como se implementaria) a ver que os parece (y a ver si me podeis decir como implementarla)

Podria hacer un INNER JOIN entre EMPLEADOS y EMPLEADO_CONTRATO (sobre DNI) de manera que tendria una tabla en que cada registro me daria un empleado con un contrato su duracion y la jornada contratada (si me equivoco decirmelo) (en principio voy a suponer que los contratos no se solapan en el tiempo, es decir, quiza uno acabe el dia 2 y al dia siguiente ya tenga otro, pero no uno empieza antes de que acabe el anteriro)

Luego crearia una tabla temporal en la que habria un registro para cada empleado de los anteriores y dia dentro del intervalo anterior y un campo extra con las horas disponibles que tiene ese dia (que em principio serian las horas contratadas por el contrato que este vigente ese dia)

Luego recorreria la tabla PARTICIPA_EN para por cada intervalo de participacion (de los que nos interesa, es decir, de los que caen en parte o totalmente dentro del de la busqueda) ir restando las horas ya dedicadas en la tabla temporal por dia.

Finalmente sacaria de la tabla temporarl la disponibilidad minima que ha quedado por empleado, y en el caso de ser mayor o igual a la pedida le sacaria como resultado.

¿Esto se puede hacer en SQL? ¿o en VBA? ¿como?

Cyttorak
09 de Septiembre del 2004
Mi primera idea ha sido esta:

Private Sub Comando17_Click()

Set db = CurrentDb

sqlEC = "Select EMPLEADOS.DNI, EMPLEADO_CONTRATO.COD_CONTRATO, EMPLEADO_CONTRATO.FECHA_DE_INICIO, EMPLEADO_CONTRATO.FECHA_DE_FIN, EMPLEADO_CONTRATO.JORNADA " & _
"FROM EMPLEADOS INNER JOIN EMPLEADOS_CONTRATO ON (EMPLEADOS.DNI=EMPLEADO_CONTRATO.CONTRATADO) " & _
"WHERE ((EMPLEADOS.ACTIVO = 0) AND " & _
"(EMPLEADO_CONTRATO.JORNADA >= " & Me!Jornada & ") AND " & _
"((EMPLEADO_CONTRATO.FECHA_DE_FIN BETWEEN #" & Me!Fecha_de_inicio & "# AND #" & Me!Fecha_de_fin & ") OR " & _
"(EMPLEADO_CONTRATO.FECHA_DE_INICIO BETWEEN #" & Me!Fecha_de_inicio & "# AND #" & Me!Fecha_de_fin & ") OR " & _
"((EMPLEADO_CONTRATO.FECHA_DE_INICIO < " & Me!Fecha_de_inicio & ") AND (EMPLEADO_CONTRATO.FECHA_DE_FIN < " & Me!Fecha_de_fin & ")))" & _
") " & _
"ORDER BY EMPLEADOS.DNI, EMPLEADO_CONTRATO.FECHA_DE_INICIO"

Set rsEC = db.OpenRecordset(sqlEC, dbOpenDynaset)

sqlPE = "Select PARTICIPA_EN.DNI, PARTICIPA_EN.FECHA_DE_INICIO, PARTICIPA_EN.FECHA_DE_FIN, PARTICIPA_EN.JORNADA " & _
"FROM PARTICIPA_EN " & _
"WHERE (PARTICIPA_EN.FECHA_DE_FIN BETWEEN #" & Me!Fecha_de_inicio & "# AND #" & Me!Fecha_de_fin & "#) OR (PARTICIPA_EN.FECHA_DE_INICIO BETWEEN #" & Me!Fecha_de_inicio & "# AND #" & Me!Fecha_de_fin & "#) " & _
"ORDER BY PARTICIPA_EN.DNI"

Set rsPE = db.OpenRecordset(sqlPE, dbOpenDynaset)

Dim dimension As Integer
dimension = Me!Fecha_de_fin - Me!Fecha_de_inicio + 1
Dim ocupado(dimension) As Integer

If Not (rsEC.BOF = True And rsEC.EOF = True) Then
rsEC.MoveFirst
antDNI = rsEC.Fields("DNI")
antFECH = Me!Fecha_de_inicio - 1
Do While Not rsEC.EOF
actFECH = rsEC.Fields("Fecha_de_inicio")
If ((antFECH + 1) < actFECH) Then
'Borrar al tio de los resultados posibles o apuntarle para borrarle luego
While ((Not rsEC.EOF) Or Not (antDNI = actDNI))
actDNI = rsEC.Fields("DNI")
actFECH = rsEC.Fields("Fecha_de_inicio")
rsEC.MoveNext
Loop
Else
indiceMenor = Me!Fecha_de_inicio
If actFECH >= Me!Fecha_de_inicio Then
indiceMenor = actFECH - Me!Fecha_de_inicio + 1
End If
indiceMayor = rsEC.Fields("Fecha_de_fin") - Me!Fecha_de_inicio + 1
For i = indiceMenor To indiceMayor
ocupado(i) = ocupado(i) + rsEC.Fields("Jornada")
'ocupado(i) = rsEC.Fields("Jornada")
Next
End If
If Not (rsEC.EOF = True) Then
If Not (antDNI = rsEC.Fields("DNI")) Then
If (antFECH < Me!Fecha_de_fin) Then
'Borrar al tio de los posibles resultados
Else
'Calcular disponibilidad y ver si es apto
End If
For i = 1 To dimension
ocupado(i) = 0
Next
End If
antDNI = rsEC.Fields("DNI")
antFECH = rsEC.Fields("Fecha_de_fin")
rsEC.MoveNext
End If
Loop
Else
MsgBox "No hay resultados"
End If
End Sub


Pero el problema (a parte de que no se si esta bien) es que no admite:

Dim dimension As Integer
dimension = Me!Fecha_de_fin - Me!Fecha_de_inicio + 1
Dim ocupado(dimension) As Integer

porque al declarar el array necesita que la dimension sea un valor constante, y no algo que se coje del usuario por medio del formularo.

¿Sabeis de alguna otra cosa que pueda usar para sustituir esto y que me haga la misma funcion que pretendia tener con el array?

¿O alguna otra solucion?

Por favor, algo de ayuda, que me estoy volviendo loco!!