CONSULTA PARA CORTES DE SERVICIO

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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.

Answer 6

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.

Answer 7

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

Answer 8

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

Answer 9

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.

Answer 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