Buenos días a todos,

Quiero saber cada instancia de ausencia, donde una instancia es cualquier día o conjunto de días consecutivos, entre dos puntos de fecha. Tuve una amplia ayuda sobre esto en mi pregunta original y tengo un código de trabajo para todo excepto la parte "Entre dos fechas". Espero que alguien pueda ayudarme a agregar el elemento "entre dos fechas" al código de trabajo.

Pregunta de ejemplo:
¿Cuántas instancias y días totales de ausencia han tenido todos entre el 01/03/16 y el 19/01/16?

Datos de muestra:

|IDRef  |RecordDate  |Racf  |Type  |LengthOfAbsence 
|1788  |04-Jan-16  |Bob  |Sickness |420
|1789  |04-Jan-16  |Jill  |Sickness |420 
|1790  |05-Jan-16  |Bob  |Sickness |420 
|1791  |17-Jan-16  |Jill|Sickness |420  
|1792  |18-Jan-16  |Bob  |Sickness |420   

Salida esperada:

| Racf  |Total Days  |Instances
|Bob  | 3  |2  
|Jill  |2  |2

Código de trabajo:

SELECT Absence.Racf, Count(Absence.RecordDate) AS CountOfRecordDate
FROM Absence LEFT JOIN (select Racf, [RecordDate]+IIf(Weekday([RecordDate],7)=1,2,1) as RecordDate2 from Absence) AS t2 ON (Absence.RecordDate = t2.RecordDate2) AND (Absence.Racf = t2.Racf)
WHERE (((t2.RecordDate2) Is Null))
GROUP BY Absence.Racf;

Mi intento más reciente de modificarlo solo se ve entre dos fechas (esto no causa un error pero devuelve valores inesperados / incorrectos):

Sql = "SELECT Absence.Racf, Count(Absence.RecordDate) AS CountOfRecordDate "
Sql = Sql & "FROM Absence LEFT JOIN (select Racf, [RecordDate]+IIf(Weekday([RecordDate],7)=1,2,1) as RecordDate2 from Absence where [RecordDate] BETWEEN #" & sFromDate & "# AND #" & sToDate & "#) AS t2 ON (Absence.RecordDate = t2.RecordDate2) AND (Absence.Racf = t2.Racf) "
Sql = Sql & "WHERE (((t2.RecordDate2) Is Null)) AND [t2.RecordDate2] BETWEEN #" & sFromDate & "# AND #" & sToDate & "# "
Sql = Sql & "GROUP BY Absence.Racf;"

Pregunta original aquí: Cuenta instancias de fechas consecutivas para nombres asociados (VBA, SQL)

Cualquier ayuda con este último paso sería muy apreciada, una explicación de por qué funciona tampoco estaría de más, ¡ya que no tengo idea!

1
Snayff 5 abr. 2017 a las 13:54

2 respuestas

La mejor respuesta

Aquí está mi nuevo código de trabajo que probé en Access pero aún no en una macro llamada desde Excel

SELECT Absence.Racf, Count(Absence.RecordDate) AS CountOfRecordDate, Absence.RecordDate
FROM Absence LEFT JOIN [select Racf, [RecordDate]+IIf(Weekday([RecordDate],7)=1,2,1) as RecordDate2 from Absence]. AS t2 ON (Absence.Racf = t2.Racf) AND (Absence.RecordDate = t2.RecordDate2)
WHERE (((t2.RecordDate2) Is Null))
GROUP BY Absence.Racf, Absence.RecordDate
HAVING (((Absence.RecordDate)>#1/1/2016# And (Absence.RecordDate)<#10/11/2016#));

La razón por la que un intervalo de fechas no funcionó en la cláusula WHERE fue que la fecha aquí siempre es NULL, por lo que nunca obtienes ningún registro.

Igualmente podría usar una declaración ENTRE en lugar de> y <.

Lamento que esta no sea la solución ordenada que estaba buscando, pero esto va más allá de lo que puede hacer fácilmente en una sola consulta en Access. La salida se ve así

enter image description here

Lo que te sugiero que hagas (si imaginas esto para varios usuarios) es usar una tabla dinámica para resumir los resultados para cada usuario.

Pero también puedes probar esto

SQL = "select racf, count(racf) AS Instances from ("
SQL = SQL & "SELECT Absence.Racf, Count(Absence.RecordDate) AS CountOfRecordDate,Absence.RecordDate"
SQL = SQL & " FROM (Absence LEFT JOIN (select Racf, RecordDate+IIf(Weekday([RecordDate],7)=1,2,1) as RecordDate1 from Absence) AS t1 ON (Absence.RecordDate = t1.RecordDate1) AND (Absence.Racf = t1.Racf))"
SQL = SQL & " WHERE ((t1.RecordDate1) Is Null) "
SQL = SQL & " GROUP BY Absence.Racf,Absence.RecordDate"
SQL = SQL & " HAVING (((Absence.RecordDate)>#1/1/2016# And (Absence.RecordDate)<#10/11/2016#))"
SQL = SQL & ") AS T0 GROUP BY RACF"

EDITAR

Estoy seguro de que al reflexionar, aunque es correcto, esto se puede simplificar porque el GROUP BY interno no sirve para ningún propósito útil: la cláusula HAVING debe colocarse fuera de T0 como una cláusula WHERE.

Debe tener un aspecto como este

SQL = "select racf, count(racf) AS Instances from ("
SQL = SQL & "SELECT Absence.Racf, Absence.RecordDate"
SQL = SQL & " FROM (Absence LEFT JOIN (select Racf, RecordDate+IIf(Weekday([RecordDate],7)=1,2,1) as RecordDate1 from Absence) AS t1 ON (Absence.RecordDate = t1.RecordDate1) AND (Absence.Racf = t1.Racf))"
SQL = SQL & " WHERE ((t1.RecordDate1) Is Null) "
SQL = SQL & ") AS T0 WHERE (((RecordDate)>#1/1/2016# And (RecordDate)<#10/11/2016#))"
SQL = SQL & "GROUP BY RACF"

EDITAR

Ahora he probado esto y funciona, pero una fecha como # 11/11/2016 # es ambigua, aunque mi ubicación es el Reino Unido, parecía interpretarse como el 11 de octubre de 2016.

Sugerir usar en su lugar

Datevalue(""10 November 2016"")

Donde las comillas deben duplicarse porque aparecen en una cadena entre comillas.

1
Tom Sharpe 24 abr. 2017 a las 11:35

Gracias a las respuestas de Tom finalmente llegué allí.

Para recuperar instancias de fechas individuales o consecutivas, dentro de un rango de fechas, esto es lo que funcionó:

  • Creó una nueva tabla para contener las fechas requeridas.
  • Ejecute una consulta contenida en Access, desde Excel para agregar los registros requeridos, entre los puntos de fecha, en la nueva tabla.
  • Ejecute una consulta Seleccionar desde Excel para devolver las instancias, excepto los domingos.

Para ejecutar la consulta desde Excel (db ya definido como OpenDatabase):

db.QueryDef.Execute

Añadir consulta:

    INSERT INTO AbsenceInstances ( Racf, RecordDate )
    SELECT Absence.Racf, Absence.RecordDate
    FROM Absence
    WHERE ((Absence.RecordDate)>=#01/01/2017# And (Absence.RecordDate)<=#28/03/2017#);

Seleccionar consulta:

Sql = "SELECT AbsenceInstances.Racf, Count(AbsenceInstances.RecordDate) AS CountOfRecordDate"
Sql = Sql & " FROM AbsenceInstances LEFT JOIN (select Racf, [RecordDate]+IIf(Weekday([RecordDate],7)=1,2,1) as RecordDate2 from AbsenceInstances) AS t2 ON (AbsenceInstances.RecordDate = t2.RecordDate2) AND (AbsenceInstances.Racf = t2.Racf)"
Sql = Sql & " WHERE (((t2.RecordDate2) Is Null))"
Sql = Sql & " GROUP BY AbsenceInstances.Racf;"

No puedo agradecer lo suficiente a Tom por su ayuda. Espero que este resumen sea útil para cualquier otra persona en el futuro.

1
Snayff 18 abr. 2017 a las 11:56