** NORMAS DEL FORO **
Inicio del foro Inicio del foro > Access y VBA > Access y VBA
  Mensajes nuevos Mensajes nuevos RSS - Agrupar SQL con varios criterios
  Preguntas frecuentes Preguntas frecuentes  Buscar en el foro   Eventos   Registro Registro  Iniciar sesion Iniciar sesion

Tema cerradoAgrupar SQL con varios criterios

 Responder Responder Página  12>
Autor
Mensaje
galceran01 Ver desplegable
Nuevo
Nuevo


Unido: 16/Mayo/2018
Localización: Valencia
Estado: Sin conexión
Puntos: 13
Enlace directo a este mensaje Tema: Agrupar SQL con varios criterios
    Enviado: 17/Mayo/2018 a las 01:31
Hola a todos, tengo una aplicación que gestiona expedientes en la que hay una tabla "historial" que guarda información sobre las actuaciones que se llevan a cabo sobre cada expediente.



En el ejemplo las 3 primeras entradas son del mismo expediente ya que tienen la misma referencia, el estado indica una acción en el expediente (1 se inicia, 2 en trámite y 5 finalizado -hay más posibles estados, pero 5 siempre es finalizado-),  usuario indica quien realizo la gestión y la fecha, pues eso la fecha en que se hizo.

Aquí va la pregunta: Necesito obtener la referencia de todos los expedientes que hace más de 90 días que "no se mira nadie", o sea que no ha cambiado su último estado en 90 días o más y por supuesto en el resultado no tienen que aparecer las archivadas (estado 5).

Espero haberme explicado bien, imagino que debe ser una consulta SQL muy simple, pero hoy tengo un dia ofuscado y no doy con la solución.

Muchas gracias de antemano y saludos. 

Arriba
emiliove Ver desplegable
Moderador
Moderador


Unido: 16/Junio/2009
Localización: Mexico
Estado: Sin conexión
Puntos: 4512
Enlace directo a este mensaje Enviado: 17/Mayo/2018 a las 02:14
Hola galceran01
Si haces una consulta como esta:

SELECT * FROM historial WHERE (((ESTADO)<>5) AND ((FECHA)<Now()-90));

Saludos.
Arriba
galceran01 Ver desplegable
Nuevo
Nuevo


Unido: 16/Mayo/2018
Localización: Valencia
Estado: Sin conexión
Puntos: 13
Enlace directo a este mensaje Enviado: 17/Mayo/2018 a las 04:36
Gracias emiliove, pero no és lo que estoy buscando tu consulta me devuelve todas las entradas de hace más de 90 dias, pero yo quiero que me devuelva solo una por expediente y si han transcurrido mas de 90 días desde la última entrada.

Ejemplo: Entré una entrada nueva (ref-001) hace 120 días (estado 1)
              un gestor se la miró hace 100 días (ref-001) y la puso en tramite  (estado 2)  
              alguien la volvió a mirar hace 20 dias (ref-001) y la puso en espera consulta legal  (estado 3)

(todo esto se ha guardado en el histórico, o sea tengo 3 entradas con la misma referencia, 3 fechas distintas y 3 estados distintos, ninguno estado es el 5 de expediente finalizado)

Si lanzamos la consulta, esta entrada no debería salir, ya que la última actuación que se hizo sobre ella fue hace tan sólo 20 días.

Sin embargo, tu consulta me devolvería la actuación primera (hace 120 días i estado 1) y la segunda (hace 100 días i estado 2). 

Yo lo que busco es la referencia (sin repeticiones)  de todas las entradas en las que haya pasado más de 90 días desde la última actuación, para generar un informe de alerta . 


Gracias de todos modos por tu interés.
Arriba
Mihura Ver desplegable
Administrador
Administrador
Avatar

Unido: 06/Mayo/2005
Localización: España
Estado: Sin conexión
Puntos: 10149
Enlace directo a este mensaje Enviado: 17/Mayo/2018 a las 09:00
En la consulta propuesta por Emiliove añádele:

 ...  GROUP BY Referencia



Jesús Mansilla Castells.
Saludos desde Móstoles.

Access Aplicaciones
Tecsys.es
Arriba
galceran01 Ver desplegable
Nuevo
Nuevo


Unido: 16/Mayo/2018
Localización: Valencia
Estado: Sin conexión
Puntos: 13
Enlace directo a este mensaje Enviado: 17/Mayo/2018 a las 12:00
Gracias por tu respuesta Mihura, ya lo había probado pero no me funciona, lo último que he probado:

SELECT referencia, Max(fecha) AS MáxFecha
FROM historial
WHERE (fecha<Date()-90) AND estado<>5
GROUP BY referencia;

Pero me devuelve la entrada más antigua que su estado no es 5, aunque tenga otra entrada del mismo expediente con estado 5. Si existe una anotación con estado 5 no deberia salir ninguna de e.
sa referencia ya que estado 5 indica expediente finalizado/archivado.

Espero que me esteis entendiendo, porque cada vez me lio más.

Alguien (en persona) me ha sugerido una subconsulta, pero no tengo claro cómo hacerla.

Gracias por responder.
Arriba
Mihura Ver desplegable
Administrador
Administrador
Avatar

Unido: 06/Mayo/2005
Localización: España
Estado: Sin conexión
Puntos: 10149
Enlace directo a este mensaje Enviado: 17/Mayo/2018 a las 12:07
Es que le estás diciendo que no te muestre las de estado 5:

WHERE ......   estado <> 5


Jesús Mansilla Castells.
Saludos desde Móstoles.

Access Aplicaciones
Tecsys.es
Arriba
galceran01 Ver desplegable
Nuevo
Nuevo


Unido: 16/Mayo/2018
Localización: Valencia
Estado: Sin conexión
Puntos: 13
Enlace directo a este mensaje Enviado: 17/Mayo/2018 a las 13:42
Hola Mihura, tienes razón, pero és que si quito  estado <> 5 me saldran todas las que su última entrada fue hace más de 90 dias, incluidas las archivadas (estado 5) que no deberian salir nunca.

Gracias por dedicarme tu tiempo
Arriba
mounir Ver desplegable
Colaborador
Colaborador


Unido: 09/Febrero/2009
Localización: Asturias-España
Estado: Sin conexión
Puntos: 4576
Enlace directo a este mensaje Enviado: 17/Mayo/2018 a las 14:23
Hola!

Tú lo que quieres es que no salgan los que tienen el estado 5, con fecha mayor de 90 días y la ultima fecha o el estado mas alto, es así?

Intenta hacerlo con Top 1 y ordenando de forma descendente el estado o la fecha, de esta forma saldría el estado mayor y solo en registro de cada agrupamiento.

Sería mas o menos así: SELECT Top 1 referencia, Max(fecha) AS MáxFecha, Last(estado) As Ultimoestado
FROM historial
WHERE (fecha<Date()-90) AND estado<>5
GROUP BY referencia
ORDER BY estado DESC;
Un Saludo.
Arriba
Mihura Ver desplegable
Administrador
Administrador
Avatar

Unido: 06/Mayo/2005
Localización: España
Estado: Sin conexión
Puntos: 10149
Enlace directo a este mensaje Enviado: 17/Mayo/2018 a las 15:00
Me parece que no van los tiros por ahí ...

Para eliminar aquellos que tengan estado 5 hay que hacer algo similar a:

SELECT ...
WHERE Referencia NOT IN (SELECT Referencia FROM Historial WHERE estado = 5) AND .....




Jesús Mansilla Castells.
Saludos desde Móstoles.

Access Aplicaciones
Tecsys.es
Arriba
emiliove Ver desplegable
Moderador
Moderador


Unido: 16/Junio/2009
Localización: Mexico
Estado: Sin conexión
Puntos: 4512
Enlace directo a este mensaje Enviado: 17/Mayo/2018 a las 15:32
Me parece que es como dice Jesús, pero para que yo pueda entenderlo mejor, de la lista que pusiste en tu primer intervención si hacemos la consulta no te mostraría datos ¿verdad? pues todas las referencias  tienen un 5.

Y otra cosa el titulo no cumple con las normas del foro puedes modificarlo por favor.

Ya comentas.
Arriba
galceran01 Ver desplegable
Nuevo
Nuevo


Unido: 16/Mayo/2018
Localización: Valencia
Estado: Sin conexión
Puntos: 13
Enlace directo a este mensaje Enviado: 17/Mayo/2018 a las 18:11
Hola intento responder a todos los que me estais intentando ayudar:

Mounir:
El estado es una referencia a una tabla externa y 5 no es el valor más alto, en la actualidad el más alto es 9 y la aplicación ofrece la posibilidad de añadir más, lo que si es seguro que el estado 5 es expediente finalizado/archivado. Por lo que no acabo de ver que funcione tu solución, de todas maneras cuando tenga un momento lo probaré. Gracias.

Mihura:  
Esta mañana he estado trasteando un rato y he hecho algo parecido: hacer una select que me devuelva la referencia de todas las archivadas (estado5) y usarla en otra consulta con un NOT IN como haces tu, no he podido comprobar del todo si funcionaba porque lo he tenido que dejar por atender a otros asuntos, si he visto que era muy lento y parece ser que se aproxima a lo que quiero, aunque da referencias duplicadas, seguiré explorando esta via y informaré. Gracias también

Emiliove:
Efectivamente, la lista que puse al inicio es una captura de los primeros registros de la tabla con datos reales, por tanto son expedientes muy antiguos (del 2015)  y efectivamente, todos estan archivados (estado 5) no pensé en ello en el momento de hacer la captura. Si puedo, luego  subiré una captura con los últimos del mes de mayo. 
Referente al título y las normas del foro, lo siento mucho, si me indicas un posible título lo cambio gustosamente, la verdad és que cuando realicé la pregunta no se me ocurría que título poner, soy muy torpe para estas cosas.

A todos en general:
Lo que intento hacer es que el programa cada viernes a las 3 de la madrugada lance un proceso que localice todos los expedientes que no estén archivados (que no estén en estado 5) y que llevan más de 3 meses sin que se los mire nadie y  enviar un correo a un responsable para que no se olviden de ellos.

El programa es una aplicación web, està hecho con Visual Studio y la Base de Datos es Access, todo me funciona correctamente excepto la dichosa consulta SQL. 

Gracias a todos.


Arriba
pitxiku Ver desplegable
Asiduo
Asiduo
Avatar

Unido: 27/Septiembre/2017
Localización: En mi casa
Estado: Sin conexión
Puntos: 418
Enlace directo a este mensaje Enviado: 17/Mayo/2018 a las 19:29
Una duda: si un expediente ha sido cerrado (estado = 5), ¿puede volver a mirarse y cambiar el estado a 2, por ejemplo?

Y si este caso puede darse, si el cambio a 2 fue hace 200 días, ¿debe aparecer en la consulta?
Arriba
pitxiku Ver desplegable
Asiduo
Asiduo
Avatar

Unido: 27/Septiembre/2017
Localización: En mi casa
Estado: Sin conexión
Puntos: 418
Enlace directo a este mensaje Enviado: 17/Mayo/2018 a las 19:54
Mientras respondes a mi duda, a ver si te sirve esto:

1. Suponiendo una tabla llamada HISTORIAL, con los siguientes registros, entiendo que sólo debe aparecer el expediente AAAA en la consulta.:

HISTORIAL
ID REFERENCIA FECHA USUARIO ESTADO
718 5B80 15/10/2015 15:05:18 15 5
622 5B80 06/10/2015 0:09:12 15 2
621 5B80 02/10/2015 9:41:49 17 1
3984 8BC4 05/06/2017 17:33:29 15 5
99999999 AAAA 01/01/2018 999 1
99999998 BBBB 05/05/2018 999 1

2. Para esto, primero localizo la última fecha de cada expediente con la consulta de totales que estabais barajando:

SELECT HISTORIAL.REFERENCIA, Max(HISTORIAL.FECHA) AS MáxDeFECHA
FROM HISTORIAL
GROUP BY HISTORIAL.REFERENCIA
HAVING (((Max(HISTORIAL.FECHA))<Now()-90));

Y que devuelve los siguientes registros:

HISTORIAL_FECHA
REFERENCIA MáxDeFECHA
5B80 15/10/2015 15:05:18
8BC4 05/06/2017 17:33:29
AAAA 01/01/2018

3. Y ahora, uso esta consulta y la misma tabla de historial:

SELECT HISTORIAL.REFERENCIA, HISTORIAL.ESTADO
FROM HISTORIAL INNER JOIN HISTORIAL_FECHA ON (HISTORIAL.FECHA = HISTORIAL_FECHA.MáxDeFECHA) AND (HISTORIAL.REFERENCIA = HISTORIAL_FECHA.REFERENCIA)
WHERE (((HISTORIAL.ESTADO)<>5));

Que me devuelve sólo el expediente AAAA:

HISTORIAL_NO_5
REFERENCIA ESTADO
AAAA 1

Por supuesto, verifica si todo es correcto con los datos reales. Y como decía antes, si puede pasar de estado 5 a 2 (o el que sea), ver si se debe mostrar o no ese expediente.
Arriba
galceran01 Ver desplegable
Nuevo
Nuevo


Unido: 16/Mayo/2018
Localización: Valencia
Estado: Sin conexión
Puntos: 13
Enlace directo a este mensaje Enviado: 17/Mayo/2018 a las 21:29
Hola Pitxiku, gracias por tu respuesta, ahora mismo no lo puedo mirar porque te escribo desde un ordenador que no es el mio y no tengo acceso al programa. Ya lo comprovaré en cuanto pueda.

Referente a si un expediente archivado puede volver a abrirse efectivamente se puede, al hacerlo se crea una entrada nueva en el historial con la misma referencia de expediente y se le asigna el estado 9, de esta manera queda constancia que el usuario X reabrió el expediente en una fecha determinada (de hecho la tabla historial tiene más campos, como por ejemplo uno OBSERVACIONES donde se indica el motivo de cada acción que se lleva a cabo, pero eso para mi pregunta es irrelevante).

La verdad  es que la aplicación es bastante extensa, es como una especie de gestor documental, en el resto de la aplicación los expedientes cuya última anotación se ha marcado con estado 5 desaparecen.
Hay una opción consultar archivados que muestra y permite consultar toda la vida del expediente desde que se creó hasta que se archivó. También permite reactivarlo (lo que he explicado antes), como la última entrada en el historial ya no tiene estado 5, sino 9, vuelve a reaparecer en la aplicación. 

Perdona el rollo que he pegado.

Arriba
pitxiku Ver desplegable
Asiduo
Asiduo
Avatar

Unido: 27/Septiembre/2017
Localización: En mi casa
Estado: Sin conexión
Puntos: 418
Enlace directo a este mensaje Enviado: 18/Mayo/2018 a las 19:39
Como buscamos la última fecha, y de esas no tomamos las que no tengan el estado 5, entiendo que esos expedientes reabiertos deben aparecer (tienen el 9). Si no se quiere que aparezcan, siempre se pueden recuperar por otro lado (consulta que devuelva expedientes con estado=9), y "restarlos" de la consulta final.
Arriba
 Responder Responder Página  12>
  Compartir tema   

Ir al foro Permisos de foro Ver desplegable