Datediff - calcular días

[mgd]

Hola como están?
intento crear una consulta en la cual seleccionar los pacientes que tienen cuenta corriente y su fecha de ultima entrega es, en dias, mayor a x cantidad de dias.
Ademas de esto necesito saber que cuentas no tienen entregas realizadas.
La consulta que utilizo y funciona bien es esta:

Código SQL:

SELECT pacientes.nombre paciente, cuenta.codcuenta, cuentahistoria.fecha, datediff(curdate(), cuentahistoria.fecha) AS dias
FROM cuenta INNER JOIN pacientes ON pacientes.codpaciente = cuenta.codpaciente
                        INNER JOIN cuentahistoria ON cuentahistoria.codcuenta = cuenta.codcuenta
WHERE cuentahistoria.estado = 'ALTA' AND cuentahistoria.movimiento = 'E' AND datediff(curdate(), cuentahistoria.fecha) > 10
GROUP BY cuenta.codcuenta
ORDER BY dias DESC
 

El problema es que necesito que cuando hago el datediff el valor de cuentahistoria.fecha sea la fecha de esa cuenta mas alta, es decir la ultima fecha de entrega y no tengo idea como hacerlo...
Ademas necesito saber también quienes no han realizados entregas (cuentahistoria.movimiento = 'E')

Muchas gracias

[gnzsoloyo]

Es posible que esto funcione, aunque habría que ver qué significa "movimiento" en esa tabla, para saber si hay que sumarlo.

Código MySQL:

SELECT 
    P.nombre paciente, 
    C.codcuenta, 
    CH.fecha, 
    datediff(curdate(), CH.fecha) dias
FROM 
    cuenta C 
    INNER JOIN pacientes P ON P.codpaciente = P.codpaciente
    INNER JOIN 
        (SELECT codcuenta, MAX(fecha) fecha, estado, movimiento 
        FROM cuentahistoria 
        GROUP BY codcuenta) CH ON C.codcuenta = CH.codcuenta
WHERE 
    CH.estado = 'ALTA' AND 
    CH.movimiento = 'E' AND 
    datediff(curdate(), CH.fecha) > 10
GROUP BY C.codcuenta
ORDER BY dias DESC;
 

[mgd]

Tu consulta me ayudo mucho, cambie algunas cositas para que quede como necesitaba.

Código SQL:

SELECT 
         P.nombre paciente, 
         C.codcuenta, 
    C.debe,
        CH.fecha, 
        DATEDIFF(CURDATE(), CH.fecha) dias
FROM 
        cuenta C 
INNER JOIN pacientes P ON P.codpaciente = C.codpaciente
INNER JOIN 
                (SELECT codcuenta, MAX(fecha) fecha, estado, movimiento 
                FROM cuentahistoria 
        WHERE movimiento = 'E' AND estado = 'ALTA'
                GROUP BY codcuenta) CH ON C.codcuenta = CH.codcuenta
WHERE 
       DATEDIFF(CURDATE(), CH.fecha) > 11 AND c.debe > 0
GROUP BY C.codcuenta
ORDER BY dias DESC;
 

movimiento = 'E' es la forma de saber si el movimiento es una entrega

Ahora el tema es: como hago para saber en esta misma consulta si un paciente con cuenta corriente no tiene entregas realizadas...

Pense utilizar un OR en datediff del where pero la verdad que no va

[gnzsoloyo]

Podemos aprovechar el LEFT JOIN, pero tienes que tener en cuenta que si no tiene entregas, al menos dos de los campos no pueden figurar en el SELECT porque darían NULL y en el WHERE no pueds consultar por fechas que estén relacionadas a esa tabla.
Sería algo como esto:

Código MySQL:

SELECT 
    P.nombre paciente, 
    C.codcuenta, 
    C.debe
FROM 
    cuenta C 
    INNER JOIN pacientes P ON P.codpaciente = C.codpaciente
    LEFT JOIN 
        (SELECT codcuenta, MAX(fecha) fecha, estado, movimiento 
        FROM cuentahistoria 
        WHERE movimiento = 'E' AND estado = 'ALTA'
        GROUP BY codcuenta) CH ON C.codcuenta = CH.codcuenta
WHERE 
    CH.codcuenta IS NULL
GROUP BY C.codcuenta
ORDER BY dias DESC;
 

[mgd]

si pero no me sirve esa sentencia, ya veré
alomejor hago 2 consultas
gracias

[gnzsoloyo]

Iniciado por mgd

si pero no me sirve esa sentencia, ya veré
alomejor hago 2 consultas
gracias

Explícate por qué no te sirve la sentencia. Con decir que no te sirve la sentencia no me dices nada.
¿Cuál es el problema?

[mgd]

No es que no me sirva perdón, me exprese mal.
La segunda parte de lo que necesito (cuentas a las que nunca se hayan hecho entregas) no puedo o no encuentro la manera de agregarlo a la primer parte de lo que necesito(cuentas cuya ultima entrega sea mayor en días que x cantidad de días)...
Es decir que las dos cosas juntas no van en la misma consulta, por lo menos yo no pude lograrlo y por eso digo de hacer 2 consultas...

[gnzsoloyo]

En realidad, se puede, lo que sucede es que como se tata de datos excluyentes (lo que me dice que estás tratando de sacar un reporte entero en una sola consulta), la consulta se vuelve algo más compleja.
Podrías probar simplemente:

Código MySQL:

SELECT 
         P.nombre paciente, 
         C.codcuenta, 
         C.debe,
         IFNULL(CH.fecha, '') fecha, 
         IFNULL(DATEDIFF(CURDATE(), CH.fecha), 0) dias
FROM 
        pacientes P 
        INNER JOIN cuenta C ON P.codpaciente = C.codpaciente
        LEFT JOIN 
                (SELECT codcuenta, MAX(fecha) fecha, estado, movimiento 
                FROM cuentahistoria 
                WHERE movimiento = 'E' AND estado = 'ALTA'
                GROUP BY codcuenta) CH ON C.codcuenta = CH.codcuenta
WHERE 
       (DATEDIFF(CURDATE(), CH.fecha) > 11 AND c.debe > 0) OR CH.codcuenta IS NULL
GROUP BY C.codcuenta
ORDER BY dias DESC;
 

Esto es un mix entre ambas cosas. La idea es que devuelva los registros relacionados ente PACIENTE y CUENTA, y también con MOVIMIENTO, aunque sea NULL en este caso, pero si es NULL, los últimos dos campos del SELECT se convierten en vacío ('') y cero (=, ya que no existen datos que cumplan la condición.
Esta consulta se ordenará de tal forma que aquellos paciente que no tienen correspondencias en MOVIMIENTO aparezcan al final.

[mgd]

excelente, también había imaginado hacerlo parecido a lo que decís pero no había tenido éxito
Muchas gracias, funciona bárbaro

[gnzsoloyo]