[SOLUCIONADO] conflicto con agrupar columnas con dos fechas distintas

[enniogt]

Hola buena noche, primero que nada, en serio que el curso y sus ejemplos explicativos son muy buenos, ayudan a mejorar el rendimiento y entender mucho como funciona mejor las peticiones sql a la base.

hoy presento un problema que tengo, para ver si me pueden ayudar por favor, hace ya un buen rato cree una base con una tabla pagos así:

Código MySQL:

CREATE TABLE `pagos` (
`Id` int(9) unsigned NOT NULL AUTO_INCREMENT,
`MONTO` float(8,2) unsigned NOT NULL,
`MORA` float(5,2) DEFAULT '0.00',
`PAGO` float(8,2) DEFAULT '0.00',
`FECHA_PAGO` date DEFAULT NULL,
`BOLETA` int(10) unsigned DEFAULT NULL,
`FECHA_MORA` date DEFAULT NULL,
`USUARIO` varchar(20) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=56827
 

y ahora que intento sacar un consolidado por día no puedo resolver la consulta

lo que deseo es que me quede en un rango por ejemplo: del 01-11-2013 al 05-11-2013 el total pagado.

fecha | monto | mora | total
01-11-13 100.00 20.00 120.00
02-11-13 100.00 35.00 135.00
03-11-13 500.00 50.00 550.00
04-11-13 0.00 10.50 10.50
05-11-13 200.00 0.00 200.00

la siguiente consulta me muestra el total del monto

Código MySQL:

SELECT FECHA_PAGO, SUM(MONTO)
FROM pagos
WHERE (FECHA_PAGO BETWEEN '2013/11/01' AND '2013/11/05')
GROUP BY FECHA_PAGAR
 

y esta otra me muestra el total de mora

Código MySQL:

SELECT FECHA_MORA, SUM(MORA) 
FROM pagos
WHERE (FECHA_MORA BETWEEN '2013/11/01' AND '2013/11/05')
GROUP BY FECHA_MORA
 

pero cuando quiero convertirla en una sola no he podido, pues si coloco la fecha_pago las condiciones de rango y agrupación hacen que las moras salgan en la fecha del pago, y no en la verdadera fecha de la mora que esta guardada en fecha_mora, ya intente pero en verdad gracias por leerlo y si pueden ayudarme muy agradecido

espero haberme dado a entender, bendiciones

[jurena]

No lo he probado, pero intenta con esto:

Código MySQL:

SELECT SUM(t1.tot) total FROM  ((SELECT FECHA_PAGO fecha, SUM(MONTO) tot
    FROM pagos
    WHERE FECHA_PAGO BETWEEN '2013/11/01' AND '2013/11/05'
    GROUP BY FECHA_PAGAR)
       UNION ALL
    (SELECT FECHA_MORA, SUM(MORA)
    FROM pagos
    WHERE FECHA_MORA BETWEEN '2013/11/01' AND '2013/11/05'
    GROUP BY FECHA_MORA))t1
 

[enniogt]

Gracias por tu tiempo jurena, quizá no me di a entender, probé tu solución y me da el total de todo, pero lo necesito con las fechas y demás columnas así:

https://docs.google.com/drawings/d/1Nbj0Z0y-MGwxaYsrEB65_m-7CwIQjbnwQ12vuxlX-qo/pub?w=480

hice dos pruebas

Código MySQL:

#PRUEBA1
 
SELECT fecha_pagar, SUM(MONTO) MENSUAL,
 SUM(mora)  mora, SUM(pago) AS total FROM pagos WHERE fecha_pagar BETWEEN 
'2013/11/01' AND '2013/11/04' GROUP BY fecha_pagar ORDER BY fecha_pagar DESC
 
#PRUEBA2  FALTA EL TOTAL PERO SOLO ERA PRUEBA
 
SELECT   FECHA_PAGAR,
SUM(IF(FECHA_PAGAR BETWEEN '2013/11/01' AND '2013/11/05', MONTO,NULL)) "MENSUAL",
SUM(IF(FECHA_MORA BETWEEN '2013/11/01' AND '2013/11/05', MORA,NULL)) "MORA"
FROM       PAGOS  
WHERE FECHA_PAGAR BETWEEN '2013/11/01' AND '2013/11/05'
GROUP BY   FECHA_PAGAR ORDER BY FECHA_PAGAR
 

La segunda prueba es una transpuesta pero el error sigue, porque los 20 de mora en la primer tabla, realmente se pagaron en la fecha 2013-11-03 pero como selecciono fecha_pagar desde el inicio por ello lo agrupa en 04-11 que es del pago. es decir que debería haber quedado como la tabla 2.

gracias nuevamente por su tiempo

[jurena]

No lo he probado, pero intenta tú a ver qué sale. Observa que lo primero es sacar las distintas fechas mediante union, luego con left join uno a la misma tabla dos veces una por la fecha de pago y otra por la de mora.

Código MySQL:

SELECT t1.fecha, IFNULL(SUM(p.MONTO),'0') monto, IFNULL(SUM(p.MORA),'0') MORA FROM  ((SELECT DISTINCT FECHA_PAGO fecha
        FROM pagos
        WHERE FECHA_PAGO BETWEEN '2013/11/01' AND '2013/11/05')
           UNION
        (SELECT DISTINCT FECHA_MORA
        FROM pagos
        WHERE FECHA_MORA BETWEEN '2013/11/01' AND '2013/11/05'))t1 LEFT JOIN pagos p ON t1.fecha = p.FECHA_PAGO LEFT JOIN pagos p2 t1.fecha = p2.FECHA_MORA GROUP BY t1.fecha
 

[enniogt]

de nuevo gracias pero aun descuadra, te he tomado la idea, y he observado, que es por lo mismo: de p, jalamos el monto, y de p2 (hice el cambio) jalo las moras, porque si las selecciono de p, aparece en fecha equivocada, pero estoy analizando que me ha duplicado un monto donde hay fecha_mora únicamente y el total de ambas columnas si lo jalo de p, no cuadra.

asi lo tengo por el momento.

Código MySQL:

SELECT t1.fecha, IFNULL(SUM(p.MONTO),0) monti, 
IFNULL(SUM(p2.MORA),0) mori, IFNULL(SUM(p2.pago),0) tot  FROM  
((SELECT DISTINCT FECHA_PAGAR fecha
        FROM pagos
        WHERE FECHA_PAGAR BETWEEN '2013/11/01' AND '2013/11/05')
           UNION
        (SELECT DISTINCT FECHA_MORA fecha
        FROM pagos
        WHERE FECHA_MORA BETWEEN '2013/11/01' AND '2013/11/05'))t1  LEFT JOIN pagos p ON t1.fecha = p.FECHA_PAGAR LEFT JOIN pagos p2 ON t1.fecha = p2.FECHA_MORA GROUP BY t1.fecha ORDER BY t1.FECHA DESC
 

seguiré analizando por la noche y hablamos luego y gracias de nuevo por tu valioso tiempo

[jurena]

Nos has proporcionado el create table y el ejemplo de salida, pero no a partir de qué datos debe darse esa salida. Una exportación de algunos datos para insertar, de los que nos proporcionaras la salida esperada serían de gran utilidad. Pero hasta entonces prueba esto:

Código MySQL:

SELECT t1.fecha, IFNULL( SUM( p.PAGO ) , 0 ) pagi, IFNULL( SUM( p2.MORA ) , 0 ) mori, IFNULL( SUM( p.PAGO ) , 0 ) + IFNULL( SUM( p2.MORA ) , 0 ) toti
FROM (
(
 
SELECT DISTINCT FECHA_PAGO fecha
FROM pagos
WHERE FECHA_PAGO
BETWEEN '2013/11/01'
AND '2013/11/05'
)
UNION (
 
SELECT DISTINCT FECHA_MORA fecha
FROM pagos
WHERE FECHA_MORA
BETWEEN '2013/11/01'
AND '2013/11/05'
)
)t1
LEFT JOIN pagos p ON t1.fecha = p.FECHA_PAGO
LEFT JOIN pagos p2 ON t1.fecha = p2.FECHA_MORA
GROUP BY t1.fecha
ORDER BY t1.FECHA DESC
 

[gnzsoloyo]

Recapitulemos:

pero cuando quiero convertirla en una sola no he podido, pues si coloco la fecha_pago las condiciones de rango y agrupación hacen que las moras salgan en la fecha del pago, y no en la verdadera fecha de la mora que esta guardada en fecha_mora, ya intente pero en verdad gracias por leerlo y si pueden ayudarme muy agradecido

Básicamente lo que sucede es que tienes dos subconsultas incompatibles entre sí. Eso hace bastante complicado poder lograr un reporte consolidado de deudas, pagos y moras.

A mi entender, están bastante próximos, pero habría que tratar de simplificar la cosa, y yo lo haría con uso de IF, mas o menos así:

Código MySQL:

SELECT 
    SUM(IF(FECHA_PAGO BETWEEN '2013/11/01' AND '2013/11/05', monto, 0)) total_pagos,
    SUM(IF(FECHA_MORA BETWEEN '2013/11/01' AND '2013/11/05', mora, 0)) total_mora,
    SUM(monto+mora) total
FROM  pagos
WHERE 
    FECHA_PAGO BETWEEN '2013/11/01' AND '2013/11/05'
    OR
    FECHA_MORA BETWEEN '2013/11/01' AND '2013/11/05'
GROUP BY FECHA_PAGO;
 

una consulta así cumpliria sin problemas con los datos necesarios para crear esto:

lo que deseo es que me quede en un rango por ejemplo: del 01-11-2013 al 05-11-2013 el total pagado.

fecha | monto | mora | total
01-11-13 100.00 20.00 120.00
02-11-13 100.00 35.00 135.00
03-11-13 500.00 50.00 550.00
04-11-13 0.00 10.50 10.50
05-11-13 200.00 0.00 200.00

porque en ese ejemplo que diste, la fecha de mora es un problema interno del sistema, y no debe ser representado dentro del reporte.
Digo esto último porque lo que quieres saber en esa síntesis es lo pagado en cada deuda, en referencia a que lo que importa en ese caso es la deuda origen y su mora posible, y no a partir de cuándo se aplico la carga de mora.

Ahora bien, entendamos que en general el modelo de esa tabla no es muy correcto desde la optica de sistemas para administrar pagos y moras, porque hay un error conceptual: Pagos son todos los ingresos de importes abonados por cualquier medio aceptable, y por cualquier concepto. En ese sentido, una MORA, es un concepto de pago, aplicado sobre una DEUDA de cuenta, pero no es importe adeudado en cuenta.
Visto así, la tabla de deudas debería contener fecha de ingreso, importe y fecha de vencimiento.
La tabla de Pagos debería contener, por lo menos, id de la cuenta adeudada, importe abonado, fecha de pago y concepto. Y probablemente yo construiría una tabla de "conceptos de pago", para completar la idea y darle flexibilidad al sistema.

Con un modelo mejor armado, en otras palabras, tu consulta se volvería muchísimo más simple.

Espero que se entienda lo que intento plantear.

[enniogt]

buen día jurena te envio una pequeña copia de la tabla con datos sql acá,

[URL="https://skydrive.live.com/?cid=ee661027ae7f1fb8&id=EE661027AE7F1FB8%21107&au thkey=!"]https://skydrive.live.com/?cid=ee661027ae7f1fb8&id=EE661027AE7F1FB8%21107&au thkey=![/URL]

( y gracias moderador criollo por involucrarte pero la fecha es una columna importante que debe ser agregada)

Código HTML:

<iframe src="https://skydrive.live.com/embed?cid=EE661027AE7F1FB8&resid=EE661027AE7F1FB8%21107&authkey=AGx0kCwinjX4UYE" width="98" height="120" frameborder="0" scrolling="no"></iframe>
 

espero que hoy si el contexto ayude

descripcion campos:
pago: guarda total pagado
mora: se genera o re-calcula automáticamente, sube 10 cada 6 de mes con un sp.
fecha_pago: es la fecha ultima para pagar sin mora (por error el primer post se fue con esa fecha que no interesa en la consulta de ahorita, pero he hecho el cambio en cada una de sus sugerencias)
fecha_pagar: es la fecha en que se efectúa el pago normal o mensual,
fecha_mora: es la fecha en que se efectúa el pago de la mora únicamente.

avances: agregue un campo morapag, donde guarda la mora que ya fue pagada, porque el campo mora se genera o re-calcula automáticamente, excede la fecha limite de pago.

pensaba entonces que el total necesitará un if para saber si hay moras pagadas, que le reste al pago total la mora pagada, pero si no tiene moras pagadas que solo sume el campo pago, (esto para la columna total)

bueno sigo analizando con ustedes y muchas gracias por atender.

[jurena]

Yo, como @gnzsoloyo, también tengo mis dudas sobre la estructura de tu tabla. Pero antes te haré una pregunta sobre tus datos de salida

fecha | monto | mora | total
01-11-13 100.00 20.00 120.00
02-11-13 100.00 35.00 135.00
03-11-13 500.00 50.00 550.00
04-11-13 0.00 10.50 10.50
05-11-13 200.00 0.00 200.00

Cuando muestras la fecha, qué fecha es esa para cada columna. Dinos el campo correspondiente en la tabla para MONTO y para MORA. ¿Se trata de FECHA_PAGAR y FECHA_MORA, respectivamente.
¿Quieres sacar sumados los montos de cada fecha, dentro del rango de fechas buscadas, donde haya montos; y también las moras de cada fecha de ese mismo rango, sumadas por fecha, y luego la suma total, no?

[enniogt]

si así es como lo planteas, si escribo puedo confundir, tal como lo plateas y los campos son así.

fecha, todas las del rango
monto =la suma de todo lo pagado con fecha_pagar sobre el monto
mora = la suma de todo lo pagado con fecha_mora sobre morapag, (pues es la que ya esta pagada)
y el total siempre en su respectivo rango

pudieron descargar el ejemplo, los datos del ejemplo estan en el cliente 024

[jurena]

a ver si esta te vale:

Código MySQL:

SELECT t1.fecha, IFNULL(SUM( t1.monto ),0) monto, IFNULL(SUM( t1.mora ),0) mora, SUM( t1.monto ) + SUM( t1.mora ) TOTAL
FROM (
(
 
SELECT FECHA_PAGAR fecha, id, monto, '' mora
FROM pagos
WHERE FECHA_PAGAR
BETWEEN '2013/11/01'
AND '2013/11/05'
)
UNION ALL (
 
SELECT FECHA_MORA, id, '' monto, mora
FROM pagos
WHERE FECHA_MORA
BETWEEN '2013/11/01'
AND '2013/11/05'
)
)t1
GROUP BY t1.fecha
 

[enniogt]

gracias jurena, por todo tu tiempo, yo habia colocado morapag, por si no pagan la totalidad de la mora, sino una parte nomás, pero no se que dices, y eso de ' ' mora, cual es la idea exactamente, leeré gracias de nuevo

[jurena]

me traigo vacío como valor para esos campos en esos SELECT dentro del UNION ALL. De hecho, si aceptas NULL, quizás hubiera sido mejor traerse NULL. Creo que esta consulta sería mejor. De hecho, he corregido también la suma del total, para evitar que la suma de algo con NULL dé NULL. Ahí va la última

Código MySQL:

SELECT t1.fecha, IFNULL( SUM( t1.monto ) , 0 ) monto, IFNULL( SUM( t1.mora ) , 0 ) mora, IFNULL(SUM( t1.monto ),0) + IFNULL(SUM( t1.mora ),0) TOTAL
FROM (
(
 
SELECT FECHA_PAGAR fecha, id, monto, NULL mora
FROM pagos
WHERE FECHA_PAGAR
BETWEEN '2013/11/01'
AND '2013/11/05'
)
UNION ALL (
 
SELECT FECHA_MORA, id, NULL monto, mora
FROM pagos
WHERE FECHA_MORA
BETWEEN '2013/11/01'
AND '2013/11/05'
)
)t1
GROUP BY t1.fecha