none
CONSULTA PARA CORTES DE SERVICIO RRS feed

  • Pregunta

  • Estimados

    mi BD esta en sql server 2008 r2

    dos tablas Clientes (IdCli, Nombre, Direccion,Linea) , Pagos (IdPago, idcli(hereda de Clientes), FechaPago(dateTime), Regcontrol)

    deseo saber una consulta para que me reporte a los clientes que se les corta por falta de pago

    Ej

    clientes

    idcli |nombre      |direccion|linea

    1     juan perez   av a         1

    2     goni torres   av b         1

    3     lili Aripse      av c          2

    4     tom cruise    av d         2

    5    simon perez  av e         3

    Pagos

    idpago  idcli            fechapago RegControl

    1            1              01/02/2017

    2             1             02/03/2017

    3             1              03/04/2017

    4             2              01/02/2017 

    5              2             01/03/2017

    6             3              01/01/2017

    7              3             02/02/2017

    8               4           01/01/2017

    9               5           01/04/2017

    como estamos a Mayo tendria que mostrar los siguientes ID 2, 3, 4  se salvan 1 y 5

    por favor alguna respuesta doy 5 estrellas

    atte weimar

    miércoles, 10 de mayo de 2017 16:04

Respuestas

  • Recupera la máxima fecha de pago y sobre ese dato evalúa los pagos realizados anteriores al mes pasado, por ejemplo:

    SELECT
        c.IdCli, c.Nombre, p.FechaPago AS [UltimoPago]
    FROM
        Clientes c
        INNER JOIN 
        (
    	   SELECT IdCli, MAX(FechaPago) AS [FechaPago] FROM Pagos GROUP BY IdCli
        ) p ON (c.IdCli = p.IdCli)
    	   AND p.FechaPago < CONVERT(date, 
    		  DATEADD(MONTH, -1, DATEADD(DAY, -DAY(GETDATE()) + 1, GETDATE())))
    GO

    [...] por favor alguna respuesta doy 5 estrellas

    Descuida, este foro no es un lugar donde se espere "recompensa".


    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.

    • Propuesto como respuesta Joyce_ACModerator miércoles, 10 de mayo de 2017 21:18
    • Marcado como respuesta Weimaram jueves, 11 de mayo de 2017 14:08
    miércoles, 10 de mayo de 2017 17:28

Todas las respuestas

  • Recupera la máxima fecha de pago y sobre ese dato evalúa los pagos realizados anteriores al mes pasado, por ejemplo:

    SELECT
        c.IdCli, c.Nombre, p.FechaPago AS [UltimoPago]
    FROM
        Clientes c
        INNER JOIN 
        (
    	   SELECT IdCli, MAX(FechaPago) AS [FechaPago] FROM Pagos GROUP BY IdCli
        ) p ON (c.IdCli = p.IdCli)
    	   AND p.FechaPago < CONVERT(date, 
    		  DATEADD(MONTH, -1, DATEADD(DAY, -DAY(GETDATE()) + 1, GETDATE())))
    GO

    [...] por favor alguna respuesta doy 5 estrellas

    Descuida, este foro no es un lugar donde se espere "recompensa".


    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.

    • Propuesto como respuesta Joyce_ACModerator miércoles, 10 de mayo de 2017 21:18
    • Marcado como respuesta Weimaram jueves, 11 de mayo de 2017 14:08
    miércoles, 10 de mayo de 2017 17:28
  • nadie de aquí contesta por las estrellas. Tiene pinta de que esto es un trabajo de estudiantes, si es así, mi respuesta no te servirá para nada, probablemente. pero vamos la idea fundamental si. Hay que hacer un left join para obtener los meses donde alguien no está. Por tanto no ha pagado.

    El listado que proporciona el siguiente script dice desde el 1 de enero hasta el mes actual, todos los meses y clientes que no han pagado, el 1 no pago enero, el 5 tampoco, así que no se salva ni el tato,  Ninguno ha pagado el mes actual, tampoco se salvan. Abril está pagado como tu bien dices por 1 y 5 y no por 2,3y 4.

    SET DATEFORMAT DMY;
    set language spanish
    go
    DECLARE
      @clientes TABLE
    (
      IdCli     INT,
      Nombre    VARCHAR(100),
      Direccion VARCHAR(100),
      Linea     INT);
    
    DECLARE
      @pagos TABLE
    (
      idpago    INT,
      idcli     INT,
      fechapago DATE);
    
    INSERT INTO @clientes
    VALUES
    (
      1, 'juan perez ', '  av a       ', 1),
    (
      2, 'goni torres  ', ' av b         ', 1),
    (
      3, 'lili Aripse    ', '  av c       ', 2),
    (
      4, 'tom cruise   ', ' av d        ', 2),
    (
      5, 'simon perez  ', 'av e        ', 3);
      insert into @pagos values
      (1       ,     1              ,'01/02/2017'),
    (2          ,   1             ,'02/03/2017'),
    (3          ,   1            ,'03/04/2017'),
    (4          ,   2              ,'01/02/2017'),  
    (5          ,    2             ,'01/03/2017'),
    (6          ,   3              ,'01/01/2017'),
    (7          ,    3             ,'02/02/2017'),
    (8          ,     4           ,'01/01/2017'),
    (9          ,     5           ,'01/04/2017')
    
    declare @meses table (mesint int );
    with mycte as (
    select cast('01/01/2017' as date) as fecha
    union all
    select dateadd(month,1,fecha) from mycte where fecha<dateadd(month,-1,getdate()))
    select * from 
     (select year(fecha)*100+MONTH(fecha) mes,idcli from mycte cross join @clientes ) m left join @pagos p on m.mes = year(p.fechapago)*100+MONTH(p.fechapago) and m.IdCli=p.idcli
     where p.idcli is null; 
    


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    miércoles, 10 de mayo de 2017 17:37
    Moderador
  • Saludos,

    Acorde a tu escenario pienso que debería de haber otro campo que almacene el periodo al que corresponde el pago, que sucede si un cliente hace el pago antes de la fecha de vencimiento o incluso hace pagos después, viendo eso te bastaría filtrar los registros para un periodo YYYYMM al cual corresponde el pago, ya que así como esta solo funcionaría para algunos registros no todos los escenarios. 


    Ayacucho - Perú
    Recuerda si mi solución atiende tu consulta por favor márcala como útil y como respuesta.

    http://litigiouslobo.blogspot.com/
    El Blog de Steve Morrison

    miércoles, 10 de mayo de 2017 19:46
  • estimado gracias de antemano y si aumento un campo mespago tipo datetime como quedaria la consulta???

    Pagos

    idpago  idcli            fechapago           mesPago

    1            1              01/02/2017         01/01/2017

    2             1             02/03/2017          01/03/2017

    3             1              03/04/2017          01/03/2017

    4             2              01/02/2017          01/01/2017

    5              2             01/03/2017          01/03/2017

    6             3              01/01/2017           etc

    7              3             02/02/2017

    8               4           01/01/2017

    9               5           01/04/2017

    Gracias por tu pronta respuesta

    miércoles, 10 de mayo de 2017 23:05
  • Borrón y cuenta nueva.

    Una operación de pago se produce a razón de una deuda, es decir, nadie paga por algo que no debe y en ese sentido lo correcto es que agregues al modelo una tabla que determine la deuda del cliente. ¿Y la tabla [Pagos]?, pues la tabla [Pagos] sustenta la amortización de la deuda, no basta con una columna [mesPago].

    - Tablas

    CREATE TABLE Clientes (IdCliente int, Nombre varchar(100), Direccion varchar(100), Linea int);
    CREATE TABLE Deuda (IdDeuda int, IdCliente int, Monto decimal(9,2), FechaVencimiento date);
    CREATE TABLE Pagos (IdPago int, IdDeuda int, Amortizacion decimal(9,2), FechaPago date);

    - ¿Qué clientes no pagaron?, pues todos aquellos cuya fecha de vencimiento sea anterior a hoy y cuya sumatoria de amortizaciones sean menores a la deuda total, por ejemplo:

    SELECT
        *
    FROM
        Clientes c
        INNER JOIN Deuda d ON (c.IdCliente = d.IdCliente)
    WHERE
        d.FechaVencimiento < CONVERT(date, GETDATE())
        AND d.Monto < (SELECT SUM(p.Amortizacion) FROM Pagos p WHERE p.IdDeuda = d.IdDeuda)
    GO


    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.
    jueves, 11 de mayo de 2017 0:03
  • Como punto adicional, puedes agregar a la tabla [Deuda] una columna que contenga la diferencia del monto y la sumatoria de los pagos realizados, algo como [Saldo], con ello la expresión de filtro quedaría simple a condición de tener el valor de la columna siempre actualizado:

    WHERE
        d.FechaVencimiento < CONVERT(date, GETDATE()) AND d.Saldo > 0

    En lo personal no me gusta replicar valores si los mismos los puedo obtener de manera simple.


    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.
    jueves, 11 de mayo de 2017 0:09
  • simplemente tienes que cambiar el uso que hago de fechapago por mes pago, lo que importa es si el mes está pagado, no cuando se pagó.

    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    jueves, 11 de mayo de 2017 6:02
    Moderador
  • Muchas Gracias

    realmente me salvaste, me ayudo mucho tu respuesta, en efecto no es para todos los escenarios pero ya con eso me ayuda bastante.

    Gracias nuevamente

    atte Weimar

    jueves, 11 de mayo de 2017 14:08
  • gracias mil, una buena manera de mejorar y aprender ya que no todos somos genios, si fuera asi no habria necesidad de foros.

    un saludo cordial

    gracias 

    atte.

    jueves, 11 de mayo de 2017 14:10
  • estimados

    aun con el problema este de los cortes, como haria para hacer una diferencia entre la ulrima fecha de pago y la fecha del sistema, lo cual me de un escalar que si es 2 estsos son los cortes mas o menos la pensaba asi.

    ya al parecer no puedo crear otra tabla pero con lo que tengo debe haber una solucion

    ejemplo si el cliente su ultimo pago fue el 23/03/2017 a la fecha actual es 19/05/2017 pasaron 55 dias o casi EN ESCALAR 2

    entonces entra en corte pero, si hubiera pagado el 1 3/04/2017 fecha actual es 19/05/2017 36 dias no entra en corte

    mas o menos la idea seria marzo corresponde al 3 mayo al 5 5-3 = 2 corte pago abril 4-5=1 se salva

    ayuda por favor

    Gracias mil

    sábado, 20 de mayo de 2017 2:03