none
Consulta para contar los dias consecutivos RRS feed

  • Pregunta

  • ¡Buen día! tengo una duda ya he investigado y no encuentro nada concreto. Tengo mi tabla Tienda con los siguientes campos: id_control, id_producto, producto, id_cliente, fecha_ingreso. Esta tabla es para llevar registro de clientes frecuentes ya que se le da 2 días tolerancia a las personas para que paguen, si se pasan de esos 2 días, se les cobrara 5 pesos por día excedido.

    En mi tabla tengo registros del producto diario para llevar un control sobre el estatus, es decir si ya se pago o no. Es una tabla en la cual se hace corte por semana para saber en esa semana cuanto se le cobrara de mas al cliente por los productos que se ha llevado y el tiempo que ha tardado en pagar. En la tabla se muestra el registro de un cliente en especifico.

    Lo que quiero lograr es hacer una consulta que sume las fechas que sean consecutivas.



    • Editado sistemashz lunes, 10 de agosto de 2020 17:00
    lunes, 10 de agosto de 2020 17:00

Respuestas

  • Hola sistemashz:

    Tú escenario:

    create table controles (id_control int, id_producto int, producto varchar(100), id_cliente int, Fecha_ingreso date)
    go
    
    Set dateformat dmy;
    insert into controles (id_control, id_producto, producto, id_cliente, Fecha_ingreso)
    values
    (1,2,'Refresco',3,'03/05/2020'),
    (2,2,'Refresco',3,'04/05/2020'),
    (3,5,'Galletas',3,'03/05/2020'),
    (4,5,'Galletas',3,'04/05/2020'),
    (5,5,'Galletas',3,'05/05/2020'),
    (6,5,'Galletas',3,'06/05/2020'),
    (7,5,'Galletas',3,'07/05/2020'),
    (8,7,'Atun',3,'07/05/2020'),
    (9,7,'Atun',3,'08/05/2020'),
    (10,9,'Queso',3,'07/05/2020');
    go

    La primera premisa es que tu SQL Server tiene que ser superior a 2008

    Por el escenario que has puesto, la solución más simple es utilizar la función de ventana row_number, que nos permite numerar filas, pero no creo que este sea el resultado esperado.

    -- Código 1.0

    with cte
    	 as (Select *
    			  , ROW_NUMBER() over(partition by id_producto
    				order by fecha_ingreso) as sig
    				from controles)
    	 select *
    			from cte;

    Row_number

    https://javifer2.wordpress.com/2019/11/11/row-number-numerar-filas/

    Ahora bien, hay no hay saltos, de fechas que es lo que pareces indicar en el texto.

    Código 2.0

    Para esta solución ya necesitamos SQL Server 2012

    delete from controles;
    -- ahora cargamos una fila 8 galletas con un salto de fecha.
    Set dateformat dmy;
    insert into controles (id_control, id_producto, producto, id_cliente, Fecha_ingreso)
    values
    (1,2,'Refresco',3,'03/05/2020'),
    (2,2,'Refresco',3,'04/05/2020'),
    (3,5,'Galletas',3,'03/05/2020'),
    (4,5,'Galletas',3,'04/05/2020'),
    (5,5,'Galletas',3,'05/05/2020'),
    (6,5,'Galletas',3,'06/05/2020'),
    (7,5,'Galletas',3,'07/05/2020'),
    (8,5,'Galletas',3,'09/05/2020'),
    (9,7,'Atun',3,'07/05/2020'),
    (10,7,'Atun',3,'08/05/2020'),
    (11,9,'Queso',3,'07/05/2020');
    go

    La solución es un problema conocido en SQL como finding gap and islands, que puedes encontrar de diferentes formas.

    ;with cte as (
    	Select *, 
    	CASE WHEN DATEDIFF(
    				  DAY, 
    				  LAG(fecha_ingreso) OVER(
    						PARTITION BY id_producto 
    						ORDER BY fecha_ingreso)
    				, fecha_ingreso) = 1 
    		 THEN 0 
    		 ELSE 1 
    	END AS grp_helper
    	
    	from controles 
    	) , cte2 AS (
    		SELECT
    		*,
    		SUM(grp_helper) OVER(
    				PARTITION BY id_producto
    				ORDER BY fecha_ingreso
    				ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    							) as grp
    	FROM Cte)
    	select id_control
    		 , id_producto
    		 , producto
    		 , id_cliente
    		 , Fecha_ingreso
    		 , ROW_NUMBER() over(partition by id_producto, grp order by Fecha_ingreso) as conteo
    		 from cte2 

    Para entender como funciona la consulta, tienes que ir entendiendo lo que hace cada tabla de expresión común.

    Esto lo puedes ir desgranando por bloques. Por ejemplo, si "pillas" el primer cte y ejecutas una select contra el

    ;with cte as (
    	Select *, 
    	CASE WHEN DATEDIFF(
    				  DAY, 
    				  LAG(fecha_ingreso) OVER(
    						PARTITION BY id_producto 
    						ORDER BY fecha_ingreso)
    				, fecha_ingreso) = 1 
    		 THEN 0 
    		 ELSE 1 
    	END AS grp_helper
    	
    	from controles 
    	) select * from cte

    Utilizamos las funciones de ventana lag para obtener el resultado del registro anterior, y con la función datediff sobre esta nos devuelve si el anterior es null entonces un 1 y si el anterior es 1 dia antes que el actual un 0.

    Luego se suman los grupos, y se obtiene el resultado.

    Es un tema complicado a nivel de SQL. Ya comentas como vas con el.

    Lag/Lead

    https://javifer2.wordpress.com/2018/09/11/lag-y-lead-siguiente-y-anterior/

    Tablas de expresión común

    https://javifer2.wordpress.com/2018/12/18/with-cte-tablas-de-expresion-comun-1/

    Tablas de expresión común correlativas

    https://javifer2.wordpress.com/2018/12/18/with-cte-tablas-de-expresion-comun-2-correlativos/

    • Marcado como respuesta sistemashz martes, 11 de agosto de 2020 14:46
    martes, 11 de agosto de 2020 4:09
  • Hola. veo que ya tienes una respuesta, si tienes alguna otra consulta no dudes en usar los foros de MSDN.
    jueves, 13 de agosto de 2020 16:04
    Moderador

Todas las respuestas

  • Hola:

    Podrías mejorar el escenario pegando una pequeña tabla con datos de ejemplo y el resultado esperado?

    Es que no se entiende del todo lo de las sumas.

    Gracias

    lunes, 10 de agosto de 2020 17:55
  • Una disculpa, seria esta tabla

    lunes, 10 de agosto de 2020 18:28
  • Hola:

    No tienes porqué disculparte.

    Por otro lado. Que resultado esperarías con esos datos?

    lunes, 10 de agosto de 2020 18:39
  • Gracias!
    Busco que me cuente los dias, es decir la fecha si es consecutiva vaya sumando y cuando haya cambio de fecha empiece otra vez:

    lunes, 10 de agosto de 2020 19:26
  • Hola sistemashz:

    Tú escenario:

    create table controles (id_control int, id_producto int, producto varchar(100), id_cliente int, Fecha_ingreso date)
    go
    
    Set dateformat dmy;
    insert into controles (id_control, id_producto, producto, id_cliente, Fecha_ingreso)
    values
    (1,2,'Refresco',3,'03/05/2020'),
    (2,2,'Refresco',3,'04/05/2020'),
    (3,5,'Galletas',3,'03/05/2020'),
    (4,5,'Galletas',3,'04/05/2020'),
    (5,5,'Galletas',3,'05/05/2020'),
    (6,5,'Galletas',3,'06/05/2020'),
    (7,5,'Galletas',3,'07/05/2020'),
    (8,7,'Atun',3,'07/05/2020'),
    (9,7,'Atun',3,'08/05/2020'),
    (10,9,'Queso',3,'07/05/2020');
    go

    La primera premisa es que tu SQL Server tiene que ser superior a 2008

    Por el escenario que has puesto, la solución más simple es utilizar la función de ventana row_number, que nos permite numerar filas, pero no creo que este sea el resultado esperado.

    -- Código 1.0

    with cte
    	 as (Select *
    			  , ROW_NUMBER() over(partition by id_producto
    				order by fecha_ingreso) as sig
    				from controles)
    	 select *
    			from cte;

    Row_number

    https://javifer2.wordpress.com/2019/11/11/row-number-numerar-filas/

    Ahora bien, hay no hay saltos, de fechas que es lo que pareces indicar en el texto.

    Código 2.0

    Para esta solución ya necesitamos SQL Server 2012

    delete from controles;
    -- ahora cargamos una fila 8 galletas con un salto de fecha.
    Set dateformat dmy;
    insert into controles (id_control, id_producto, producto, id_cliente, Fecha_ingreso)
    values
    (1,2,'Refresco',3,'03/05/2020'),
    (2,2,'Refresco',3,'04/05/2020'),
    (3,5,'Galletas',3,'03/05/2020'),
    (4,5,'Galletas',3,'04/05/2020'),
    (5,5,'Galletas',3,'05/05/2020'),
    (6,5,'Galletas',3,'06/05/2020'),
    (7,5,'Galletas',3,'07/05/2020'),
    (8,5,'Galletas',3,'09/05/2020'),
    (9,7,'Atun',3,'07/05/2020'),
    (10,7,'Atun',3,'08/05/2020'),
    (11,9,'Queso',3,'07/05/2020');
    go

    La solución es un problema conocido en SQL como finding gap and islands, que puedes encontrar de diferentes formas.

    ;with cte as (
    	Select *, 
    	CASE WHEN DATEDIFF(
    				  DAY, 
    				  LAG(fecha_ingreso) OVER(
    						PARTITION BY id_producto 
    						ORDER BY fecha_ingreso)
    				, fecha_ingreso) = 1 
    		 THEN 0 
    		 ELSE 1 
    	END AS grp_helper
    	
    	from controles 
    	) , cte2 AS (
    		SELECT
    		*,
    		SUM(grp_helper) OVER(
    				PARTITION BY id_producto
    				ORDER BY fecha_ingreso
    				ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    							) as grp
    	FROM Cte)
    	select id_control
    		 , id_producto
    		 , producto
    		 , id_cliente
    		 , Fecha_ingreso
    		 , ROW_NUMBER() over(partition by id_producto, grp order by Fecha_ingreso) as conteo
    		 from cte2 

    Para entender como funciona la consulta, tienes que ir entendiendo lo que hace cada tabla de expresión común.

    Esto lo puedes ir desgranando por bloques. Por ejemplo, si "pillas" el primer cte y ejecutas una select contra el

    ;with cte as (
    	Select *, 
    	CASE WHEN DATEDIFF(
    				  DAY, 
    				  LAG(fecha_ingreso) OVER(
    						PARTITION BY id_producto 
    						ORDER BY fecha_ingreso)
    				, fecha_ingreso) = 1 
    		 THEN 0 
    		 ELSE 1 
    	END AS grp_helper
    	
    	from controles 
    	) select * from cte

    Utilizamos las funciones de ventana lag para obtener el resultado del registro anterior, y con la función datediff sobre esta nos devuelve si el anterior es null entonces un 1 y si el anterior es 1 dia antes que el actual un 0.

    Luego se suman los grupos, y se obtiene el resultado.

    Es un tema complicado a nivel de SQL. Ya comentas como vas con el.

    Lag/Lead

    https://javifer2.wordpress.com/2018/09/11/lag-y-lead-siguiente-y-anterior/

    Tablas de expresión común

    https://javifer2.wordpress.com/2018/12/18/with-cte-tablas-de-expresion-comun-1/

    Tablas de expresión común correlativas

    https://javifer2.wordpress.com/2018/12/18/with-cte-tablas-de-expresion-comun-2-correlativos/

    • Marcado como respuesta sistemashz martes, 11 de agosto de 2020 14:46
    martes, 11 de agosto de 2020 4:09
  • Hola. veo que ya tienes una respuesta, si tienes alguna otra consulta no dudes en usar los foros de MSDN.
    jueves, 13 de agosto de 2020 16:04
    Moderador