listado segun determinadas fechas

[carlosdr]

Hola a todos tengo una dudilla que no se cómo sacar, a ver si me podéis echar una mano aunque sea al cuello :P

Como comente en otro tema tengo una base de datos en mysql, con una tabla donde guardo el estado de cada alumno es decir ( dni (clave), fechaest(clave), tipoestado)

En dni, se guarda el dni de cada alumno

En fechaest ,se guarda la fecha en la cual cambió de estado (para tener un control de la temporalidad)

En tipoestado, se guarda el estado perteneciente a esa fecha ( ALTA o BAJA)
Queda una tabla de este tipo

12345678Y 2011-05-02 ALTA
87654321B 2011-06-01 ALTA
12345678Y 2011-08-01 BAJA
87654321B 2011-07-01 BAJA
12345678Y 2012-05-07 ALTA

En otro lugar tengo una tabla la cual guarda los pagos (dni (clave),mespagado(clave), importe,fechapago ,comentario )

Queda una tabla de este tipo

12345678Y MAYO11 999 2011-05-03 PAGO MES MAYO
12345678Y JUNIO11 999 2011-06-05 PAGO MES JUNIO
87654321B JUNIO11 999 2011-06-02 PAGO MES JUNIO
12345678Y MAYO12 999 2012-05-10 PAGO MES MAYO

Si os fijáis 12345678Y no ha pagado el mes de julio el cual estuvo dado de alta, y el cual le falta por pagar.

De momento tengo una vista la cual me dice el estado actual de cada alumno y mediante ella puedo saber actualmente su situación, pero esta me fallaría ya que 12345678Y se dio de baja entre los meses 2011-08-01 y 2012-04-30 por lo tanto estos meses no los tiene que pagar.

La idea sería sacar un listado de todas las deudas

GRACIAS!!!

[quimfv]

Complicado.... esperemos a ver si alguien te da una mejor solución....

Pero yo modificaria el concepto de la tabla pagos y lo cambiaria por recibos (o cuotas) y en esta tabla, mes a mes el dia 1 insertaria las cuotas pendientes de pagar de todos los que esten de alta ese mes (usando la vista que te dice si alguien esta de alta o no ) y en el momento que hagan el pago o se den de baja marcaria esa cuota como pagada o anulada por baja. Tambien habria que pensar una solución para las altas que se den despues del dia uno.....

Asi seria facil de obtener los pendientes de pago.

Para corregir los datos que tienes actualmente podrias hacer ese proceso mes a mes retrospectivamente y si no esta el pago añadir el pendiente del mes en cuestion.... para ello deberias modificar la vista de estado para que te de el estado en una fecha determinada...

[dontexplain]

SELECT * FROM al WHERE NOT EXISTS (SELECT * FROM pagos WHERE pagos.fecha = al.fecha)

Selecciona de la tabla de alumnos aquellos registros que no existan cuya alguna fecha coincida en la primera tabla con la tabla de pagos, o sea, aquello que no esté en los dos sitios (no esté pagado).

En una subconsulta simple.

Un saludo.

[pcid]

Talvez esta vista te pueda ayudar:

Código SQL:

CREATE TABLE  fdw_example4_alumno_altasbajas (  
  DNI VARCHAR(20) NOT NULL,
  FechaEst DATE NOT NULL,
  TipoEstado VARCHAR(10) NOT NULL,
  PRIMARY KEY (DNI, FechaEst)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
 
CREATE TABLE fdw_example4_alumno_pago(
  DNI VARCHAR(20) NOT NULL,
  MesPagado VARCHAR(20) NOT NULL,
  Importe DECIMAL(16,2) NOT NULL,
  FechaPago DATE NOT NULL,
  Comentario VARCHAR(100) NOT NULL,  
  PRIMARY KEY (DNI, MesPagado)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
 
 
CREATE VIEW v_fdw_example4_alumno_altasbajas_aux AS
SELECT a.DNI, a.FechaEst, a.TipoEstado, MIN(b.FechaEst) AS Siguiente
        FROM fdw_example4_alumno_altasbajas a LEFT OUTER JOIN fdw_example4_alumno_altasbajas b ON b.DNI = a.DNI AND b.FechaEst > a.FechaEst
        WHERE a.TipoEstado = 'ALTA'
        GROUP BY a.DNI, a.FechaEst;
 
CREATE VIEW v_fdw_example4_alumno_altasbajas AS
SELECT x.DNI, x.FechaEst, x.TipoEstado,
    CASE isnull(x.Siguiente) WHEN 1 THEN date_format(now(), '%Y-%m-%d') ELSE x.Siguiente END AS FechaSiguiente,
    CASE isnull(x.Siguiente) WHEN 1 THEN 'SIGUE DE ALTA' ELSE y.TipoEstado END AS EstadoSiguiente
FROM v_fdw_example4_alumno_altasbajas_aux x
 LEFT OUTER JOIN fdw_example4_alumno_altasbajas y  ON y.DNI = x.DNI AND y.FechaEst = x.Siguiente;
 
 
INSERT INTO fdw_example4_alumno_altasbajas (DNI, FechaEst, TipoEstado) VALUES
('12345678Y', '2011-05-02', 'ALTA'), 
('87654321B', '2011-06-01', 'ALTA'), 
('12345678Y', '2011-08-01', 'BAJA'), 
('87654321B', '2011-07-01', 'BAJA'), 
('12345678Y', '2012-05-07', 'ALTA');
 
 
INSERT INTO fdw_example4_alumno_pago (DNI, MesPagado, Importe, FechaPago, Comentario) VALUES 
('12345678Y', 'MAYO11', 999, '2011-05-03', 'PAGO MES MAYO'), 
('12345678Y', 'JUNIO11', 999, '2011-06-05', 'PAGO MES JUNIO'), 
('87654321B', 'JUNIO11', 999, '2011-06-02', 'PAGO MES JUNIO'), 
('12345678Y', 'MAYO12', 999, '2012-05-10', 'PAGO MES MAYO');
commit;
 
SELECT * FROM v_fdw_example4_alumno_altasbajas;
 

Lo que hace es mostrarte en una "tabla" las altas con su correspondiente baja. Si "sigue de alta", en la columna FechaSiguiente aparecerá la fecha actual y en EstadoSiguiente aparecerá "SIGUE DE ALTA".

Entonces, tu listado de "pendientes de pago" tendrá como filtrarse por los períodos en los que el alumno ha estado de alta con un join a esta vista.

Lo que no veo claro es como sabes que meses son los que deberían ser pagados. Es decir, si la alta es el 30 de Julio, ¿se cobra Julio?; si la baja es el 1/Agosto, ¿Agosto también se paga? ¿y si fuese el 5? ¿Todos los meses existentes entre una fecha de alta y una de baja son cobrables? ¿No tienen vacaciones o todos los alumnos los ponen de baja ese período de tiempo?

Te sugiero que tu tabla de pagos la conviertas en una simple cuenta corriente con registros a favor y en contra del alumno. Una vez cada mes generas todos los cargos que correspondan a los alumnos que están de alta. En caso de que exista un cargo que no es correcto (por ejemplo, que la baja la hace el 5 de Agosto y se genero cargo el día 1 y la entidad para la que trabajas no quiere cobrarle Agosto a ese alumno), el sistema puede dar la opción a aplicar un "abono" a favor del alumno.

Para automatizar el proceso de generación de cargos, lo puedes poner en una tarea en el servidor que se ejecute una vez al mes y listo. O bien, condenas a la secretaria a hacer click en un botón cada mes. ;)

[quimfv]

Interesante lo dicho por pcid pero la solucion que te da al final en esencia es la misma que te daba yo ....

En cuanto a la solucion de dontexplain no es aplicable ya que no se puede relacionar la fecha de alta con la de pago....o si se puede pero la coincidencia de fechas solo te solucionaria el tema del primer pago.

[dontexplain]

Iniciado por quimfv

Interesante lo dicho por pcid pero la solucion que te da al final en esencia es la misma que te daba yo ....

En cuanto a la solucion de dontexplain no es aplicable ya que no se puede relacionar la fecha de alta con la de pago....o si se puede pero la coincidencia de fechas solo te solucionaria el tema del primer pago.

He testado la consulta y devuelve varios registros que corresponden a los registros que no corresponden en ambas tablas.

De todas formas, era algo ilustrativo, la consulta requeriría varios ajustes como agregar un condicional para aquellos que cumplen la condición de ALTA, entre otros ajustes; ordenar los ajustes por usuario, etc.

Y también se pueden usar funciones de manejo de fechas para ajustar las fechas.

Un saludo.

[carlosdr]

Muchas gracias a todos por la colaboración!

Estoy mirando todas las respuestas y pcid me abrió los ojos un poco, por el tema de en que día exacto se da la baja o alta.

"Lo que no veo claro es como sabes que meses son los que deberían ser pagados. Es decir, si la alta es el 30 de Julio, ¿se cobra Julio? si la baja es el 1/Agosto, ¿Agosto también se paga? ¿y si fuese el 5?"

Julio no se cobraría (vamos a ser buenos jeje) pero si el alta es el 15 ya si implica la mitad del pago, y por supuesto si en agosto se da de baja el 1 no se cobra, pero si la baja es el 15 sí.

"¿Todos los meses existentes entre una fecha de alta y una de baja son cobrables? "

Según sea el día que cause la baja.

Si la baja es entre el 1 y el 15 ese mes no se cobraría, y si es entre el 15 al 30 ese mes seria la mitad

"¿No tienen vacaciones o todos los alumnos los ponen de baja ese período de tiempo?"

La escuela cierra un mes, en el cual se cobra la mitad. Pero en el caso de que no se cobrara ese mes, los alumnos tendrían que seguir dados de alta (según la tabla de recibos este mes de vacaciones ¿no se generarían los recibos?)
Los alumnos se dan de alta o baja cuando ellos creen oportuno, si por ejemplo un alumno no puede acudir dos meses por temas de trabajo, este, será dado de baja y en su reincorporación se le dará de nuevo el alta. Pero no en el tiempo que este cerrada la escuela.

"Te sugiero que tu tabla de pagos la conviertas en una simple cuenta corriente con registros a favor y en contra del alumno. Una vez cada mes generas todos los cargos que correspondan a los alumnos que están de alta. En caso de que exista un cargo que no es correcto (por ejemplo, que la baja la hace el 5 de Agosto y se genero cargo el día 1 y la entidad para la que trabajas no quiere cobrarle Agosto a ese alumno), el sistema puede dar la opción a aplicar un "abono" a favor del alumno."

SI!! Tome la idea de hacer una tabla recibos, mejor que la de pagos, por el tema de la cuota que puede variar según el alumno y según la fecha.


¿Como cargarías los alumnos de alta en la tabla recibos? ¿Mediante un disparador?


¿Si la baja se dio el día 5 de Agosto y no se quiere cobrar ese mes como quedaría registrado? Si se realiza el abono a favor del alumno, este sale como si hubiera abonado el mes, o con abono te refieres a eliminar el registro (una vez abonada la cantidad)?

¿Me podríais poner un ejemplo de como seria la tabla recibos y del disparador?

A ver si logro sacar la tablita esta!! Me trae de cabeza!

GRACIASS!!

[pcid]

Si por disparador entendemos un "trigger", no. No sería con un disparador.

Sería con un procedimiento que tendría que ejecutarse una vez al mes... Asumiendo que los cargos se hacen "todos a la una" una vez al mes.

Con SQL Server recuerdo que se podían poner tareas a ejecutar cada mes.

Se pueden poner tareas sobre un sistema operativo... al menos en Linux estoy seguro -porque alguna vez las he creado yo- y en Windows Server me extrañaría que eso no exista.

Si no puedes hacerlo con un procedimiento en la base de datos, puedes poner una aplicación a ejecutar una vez al mes con un montoncito de comandos SQL. E igual, se puede poner en una tarea en el servidor, ya sea sobre la base de datos -si es que MySQL permite hacerlo- o sobre el sistema operativo. Y en el peor de los casos, mediante un click de algún usuario que nunca olvide que tiene que generar los cargos de los alumnos en la base de datos cada mes.

Por "tabla de recibos", no tengo claro a que te refieres. Yo te mencionaba una tabla para llevar una cuenta corriente. Esto es, por cada alumno una lista de cargos y abonos.

Los cargos son "las mensualidades que debe pagar" y los abonos son "los pagos recibidos".

Es la misma idea que utiliza una cuenta de ahorros en un banco.

Esta tabla, podría tener estos campos: TABLA_CARGOABONO (Id_Item, DNI, Fecha, Id_TipoOperacion, Monto)

Ese "Id_TipoOperacion" debería estar en otra tabla con estos campos: TABLA_TIPOOPERACION (Id_TipoOperacion, Descripcion, Operacion)
Donde "Operacion" podría contener un "+" o un "-".

Según el tipo de operación, al momento de calcular si el alumno debe o le deben, la sumatoria iría en función del campo "Operacion" del tipo de operación.

La tabla de tipos de operacion debe tener al menos dos registros:
1, Cargo, -
2, Abono, +

Ejemplo* de cálculo de saldos por alumno:

Código SQL:

SELECT a.DNI, SUM( CASE b.Operacion WHEN '+' THEN 1 ELSE -1 END * a.Monto) AS Saldo
FROM TABLA_CARGOABONO a INNER JOIN TABLA_TIPOOPERACION b ON b.Id_TipoOperacion = a.Id_TipoOperacion
GROUP BY a.DNI;
 

En este ejemplo, si ese saldo es negativo para un alumno, ese alumno debe dinero a.. je, "al colegio"?


Esta vez te he puesto un ejemplo sin probarlo porque me estoy muriendo de sueño... pero estoy casi seguro de que funciona. Si falla será por error "ortográfico - MySQL".

-------------------------------------------------

Por cierto, algo que si deberías hacer es cambiar ese DNI que vas arrastrando por todos lados... Es un dato alfanumérico que ocupa más espacio y que provoca búsquedas más lentas. Lo suyo, sería tener una tabla con llave primara númerica en donde el DNI sea un campo más de la tabla. Con un índice único si queres evitar los duplicados, pero la llave primaria debería ser númerica puesto que esa es la que iras arrastrando a todas las tablas que hagan referencia a los alumnos.

Aunque claro, si no tenes muchos miles de registros y/o muchos usuarios consultando la base de datos al mismo tiempo, no me hagas mucho caso... Algún poco caso, pero no mucho caso... je je. Lo siento, cuando estoy casi dormido realmente me pongo gracioso y me río de mí. Conmigo, pero de mí.

[pcid]

Iniciado por quimfv

Interesante lo dicho por pcid pero la solucion que te da al final en esencia es la misma que te daba yo ....

Pues... estoy de acuerdo en que la esencia era darle en la torre a la tabla de pagos de carlosdr.

Pero el resto, puedo asegurarte que no te entendí del todo bien si lo que sugerías era lo mismo.

En fin.

El programador durmiente, ahora si que se despide.

[quimfv]

La diferencia entre tu tabla de cargos y abonos y mi tabla de recibos o cuotas es que yo le propongo una tabla con un solo regitro tanto para el cargo como para el abono

TABLA_CARGOABONO (Id_Item, DNI, Fecha, Id_TipoOperacion, Monto)

TABLA_CUOTAS (Id_Item, DNI, Mes, Id_TipoOperacion, Monto, FechaPago, ImportePago, Nota)

Mientras fecha pago es nula la quota esta pendiente de pago. ImportePago, Nota serian opcionales si se preve pagos parciales, en este caso da mas facilidad tu solución. Tambien se podria modificar el monto si hay una baja a mitad de mes.

En cuanto a la necesidad de generar las quotas o los cargos mes a mes son exáctamente iguales.

Pero yo modificaria el concepto de la tabla pagos y lo cambiaria por recibos (o cuotas) y en esta tabla, mes a mes el dia 1 insertaria las cuotas pendientes de pagar de todos los que esten de alta ese mes (usando la vista que te dice si alguien esta de alta o no ) y en el momento que hagan el pago o se den de baja marcaria esa cuota como pagada o anulada por baja. Tambien habria que pensar una solución para las altas que se den despues del dia uno.....

Te sugiero que tu tabla de pagos la conviertas en una simple cuenta corriente con registros a favor y en contra del alumno. Una vez cada mes generas todos los cargos que correspondan a los alumnos que están de alta. En caso de que exista un cargo que no es correcto (por ejemplo, que la baja la hace el 5 de Agosto y se genero cargo el día 1 y la entidad para la que trabajas no quiere cobrarle Agosto a ese alumno), el sistema puede dar la opción a aplicar un "abono" a favor del alumno.

A eso me referia por "en esencia", para poder listar facilmente los pendiente de pago antes hay que generarlos.

[pcid]

Después de dormir ya se ve todo más claro....

Con esa tabla de cuotas, donde si fechapago es nulo es porque no hay pago, ¿qué se haría si el alumno hace un pago incompleto?

Podría pensarse en una tabla de pagos que haga referencia a la cuota pendiente por pagar. Y en la tabla de cuotas poner valor en fechapago hasta que los pagos registrados completen el total de la cuota referenciada.

Pero entonces, que pasa con un pago que cubre más de una cuota?

¿Habría que registrar más de un pago para que cada uno de ellos referencie a la cuota correspondiente?

Realmente, creo que se complica un tanto manejarlo así. Una tabla de cargos y abonos creo que es más simple de llevar.

[quimfv]

...Tambien habria que pensar una solución para las altas que se den despues del dia uno.....

Mientras fecha pago es nula la quota esta pendiente de pago. ImportePago, Nota serian opcionales si se preve pagos parciales, en este caso da mas facilidad tu solución. Tambien se podria modificar el monto si hay una baja a mitad de mes.

Ok, estamos de acuerdo en que hay que generar los pendientes de pago de alguna manera, no...y este era el error fundamental en el diseño inicial.

Pero entonces, que pasa con un pago que cubre más de una cuota?

¿Habría que registrar más de un pago para que cada uno de ellos referencie a la cuota correspondiente?

Todo depende de las reglas de negocio pero si creo que puede ser importante poder informar al alumno que meses debe mas que solo con un saldo.

La idea era orientar de forma abierta puesto que desconozco las reglas de su negocio, y sobre todo no quisiera entrar en ninguna polémica ni ofender a nadie.

[carlosdr]

Cada día me gusta mas preguntar las dudas por aquí!!

Pero entonces, que pasa con un pago que cubre más de una cuota?
¿Habría que registrar más de un pago para que cada uno de ellos referencie a la cuota correspondiente?

El pago se realiza solamente en una sola cuota mensual.. no hace falta complicar mucho


¿Que pensáis de esta solución, basada en las vuestras?

Cada mes se realiza la carga de los alumnos dados de alta.

Hasta este punto todo perfecto! Ahora viene lo entretenido

TABLA_RECIBOS(Id_Item, DNI, Fecha_generacion,fecha_pago, Monto, Descripcion, Operacion)

Id_item seria un autonumerico como clave, o la concatenación de dni-fecha.

En fecha_generación se guardarían todos marcados a día 1 de cada mes y en fecha_pago se guardaría el día exacto del cobro de este recibo .

A la hora de cargar los pagos cada mes el campo operación seria “–“ (no se realizo el cobro al alumno).

Cuando se realice el cobro del recibo operación pasara a “+” (se realizo el cobro al alumno) , se modificara el monto a la cantidad deseada ( todo se paga en una cuota) y la fecha_pago.

Por lo general, cuando un alumno se da de baja, y según que día, el monto seria distinto o directamente podríamos eliminar el registro ya que no se efectuara ese cobro nunca, de esta manera eliminamos los recibos de los alumnos que están de baja ( sin necesidad de hacerle un abono)
Si un alumno se da de alta antes del día 15, que se genere un registro nuevo en la tabla recibos.

GRACIASSS!

[pcid]

Iniciado por quimfv

Ok, estamos de acuerdo en que hay que generar los pendientes de pago de alguna manera, no...y este era el error fundamental en el diseño inicial.

Todo depende de las reglas de negocio pero si creo que puede ser importante poder informar al alumno que meses debe mas que solo con un saldo.

La idea era orientar de forma abierta puesto que desconozco las reglas de su negocio, y sobre todo no quisiera entrar en ninguna polémica ni ofender a nadie.

Tenes toda la razón con eso de que todo depende de las reglas del negocio. También depende de que datos quieren poder mostrarle a los alumnos y el cómo quieren poder mostrarlos.

Una consulta de los pagos pendientes a partir de una tabla como la que propuse es posible hacerla... Agregado algún campo o generando la consulta desde una aplicación... o en un procedimiento almacenado... En un solo SELECT creo que la cosa se complicaría. Al menos "pensándolo de lejos" creo que se complicaría.

También estoy de acuerdo en tu buena, sana y cortés postura de no buscar polémicas ni ofender a nadie. Pero un debate o discusión profesional no implica tener que llegar a estados iracundos, furiosos o rabiosos... Creo firmemente que lo que se consigue en un foro como este -aparte de soluciones puntuales- es un buen intercambio de ideas. A veces alguien sale con una idea espectacular. A veces uniendo/acoplando las ideas de más de uno se consiguen ideas aún mejores para resolver los problemas.

Al final de cuentas, en este caso, es a carlosdr a quién le toca decidir que hacer.

[pcid]

Iniciado por carlosdr

¿Que pensáis de esta solución, basada en las vuestras?

Pues... primero, una llave obtenida de concatenar el dni con la fecha es una locura... La llave primaria sobre correlativo único se ve más saludable. Si quieres evitar duplicidad de registros por alumno y fecha, pues una llave primaria compuesta de "dni y fecha" o un índice único sobre esos campos (te recuerdo que te mencioné hace unos días que yo cambiaría al DNI por un correlativo de alumno para evitar arrastrar una cadenota de carácteres por todos lados).

Segundo... pues, como bien decía quimfv, todo depende de las reglas del negocio. Así que si todo lo que necesitas "saber" después del ingreso de datos lo consigues con la estructura que estás pensando, pues ya has encontrado la solución a tu problema.

Si te sirve, yo lo que acostumbro cuando me toca resolver problemas de estos es dibujarlo. Un diagrama. Algo parecido a un diagrama de flujo, pero sin llegar a tanto minúsculo detalle. Una imagen siempre dice más que mil palabras.

¡Saludos!

[carlosdr]

Gracias por las soluciones que me ofrecisteis ya lo tengo todo más claro!!

Un trigger para cuando se inserte un alumno nuevo, el cual cree o no el registro en la tabla recibo (según la fecha).

Y por otro lado, para las bajas otro trigger, el cual según la fecha de la baja elimine o pida el monto del registro.

Por otro lado la carga de los alumnos con el procedimiento, este se ejecuta el dia uno de cada mes.

TABLA_RECIBOS(Id_Item, DNI, Fecha_generacion,fecha_pago, Monto, Descripcion, Operacion).

CREATE PROCEDURE pa_carga_alumnos()
BEGIN
INSERT INTO recibos(DNI, ¡¡¡¡Fecha_generacion, Operación!!!!!) SELECT DNI FROM estado WHERE estado = ‘ALTA’
END

En el procedimiento si os dais cuenta no se pueden meter los datos como la fecha “CURDATE()” o el – para los campos fecha de generación y para operación.

¿Cómo podría generar el procedimiento para que se insertara con estos valores?

¿Tendría que crear otro trigger para la hora de insertarlos me establezca los valores?

Aun no me puse con los otros trigger de altas y bajas supongo que no serán muy difíciles lo que parece que más se me complica es el tema de la fecha... (Según qué fecha, que haga una cosa u otra..)

Muchísimas gracias a todos por las ayudas, la verdad que esto lo estudie hace un tiempo y de no cogerlo se me fue olvidando, me estáis salvando la vida!!! Y más con esta parte de la base de datos que es más delicada GRACIASSS!!