none
Sumar los registros de una columna hasta cumplir una condición RRS feed

  • Pregunta

  • Cordial saludo, primero comparto datos importantes de las herramientas que uso:

    Cliente: Microsoft SQL Server management studio 2018 en idioma español

    Motor: Microsoft SQL Server 2016 (SP2-GDR) (KB4532097) - 13.0.5102.14 (X64)   Dec 31 2019 22:39:35   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

    Sistema operative: Windows 10.

    Tengo la siguiente tabla:

    Equipo

    Fecha

    Tiquet

    Galones

    Horas trabajadas

    VD-217

    27/02/2020

    700012

    50

    20

    VD-217

    01/03/2020

    700013

    30

    15

    VD-217

    03/03/2020

    700014

    43

    17

    VD-217

    05/03/2020

    700015

    30

    11

    VD-318

    07/03/2020

    700016

    10

    10

    VD-318

    09/03/2020

    700017

    30

    10

    VD-318

    11/03/2020

    700018

    45

    16

    VD-318

    13/03/2020

    700019

    50

    18

    Deseo validarle a cada fila si la división entre Galones y Horas trabajadas es <= 2.5, si esto sucede, colocar en otra fila el valor obtenido y en otra fila el numero de Tiquet de la fila.

    Si la división es > 2.5, validar que el equipo de la fila superior sea el mismo y sumar la fila superior a Galones y a Horas trabajadas y luego volver a realizar la división. Si la división sigue siendo > 2.5, sumarle la fila superior siguiente a Galones y a Horas trabajadas. Si la división sigue siendo > 2.5, sumarle la fila superior siguiente a Galones y a Horas trabajadas (osea hacer con las tres filas superiores) si no se logra obtener una división <= 2.5 colocar en otra fila el valor obtenido y en otra fila un comentario de que no se obtuvo un valor <= 2.5

    Coloco la tabla que deseo obtener con las dos columnas adicionales

    Equipo

    Fecha

    Tiquet

    Galones

    Horas trabajadas

    Tiquet donde es < 2.5

    Valor obtenido

    VD-217

    27/02/2020

    700012

    60

    20

    No se obtuvo tiquet

    2.5

    VD-217

    01/03/2020

    700013

    30

    15

    700013

    2.00

    VD-217

    03/03/2020

    700014

    43

    17

    700013

    2.28

    VD-217

    05/03/2020

    700015

    30

    11

    700013

    2.45

    VD-318

    07/03/2020

    700016

    10

    10

    700016

    1.00

    VD-318

    09/03/2020

    700017

    30

    10

    700016

    2.00

    VD-318

    11/03/2020

    700018

    45

    16

    700016

    2.36

    VD-318

    13/03/2020

    700019

    50

    18

    700016

    2.50

    Atento a sus recomendaciones.


    Germanq

    lunes, 27 de abril de 2020 23:22

Respuestas

  • Hola German David Quinchia Zapata:

    Te voy a dar una posible solución al problema.

    create table gal (Equipo varchar(6), Fecha date, Tiquet int, Galones tinyint, Horas tinyint)
    go
    DELETE FROM GAL
    Set language spanish;
    insert into gal (Equipo, Fecha, Tiquet, Galones, Horas)
    values
    ('VD-217','27/02/2020',700012,50,20),
    ('VD-217','01/03/2020',700013,30,15),
    ('VD-217','03/03/2020',700014,43,17),
    ('VD-217','05/03/2020',700015,30,11),
    ('VD-318','07/03/2020',700016,10,10),
    ('VD-318','09/03/2020',700017,30,10),
    ('VD-318','11/03/2020',700018,45,16),
    ('VD-318','13/03/2020',700019,50,18);
    GO
    
    
    

    Lo primero es que el escenario no esta claro, porque en la primera tabla en la primera linea, pones galones 50 y en la tabla de resultado pones 60.

    Además al no poner los tipos de datos, los resultados pueden ser diferentes. No obstante, a efectos didácticos es lo mismo.

    WITH CTE
    	 AS (
    SELECT gal.Equipo
    	 , gal.Fecha
    	 , gal.Tiquet
    	 , gal.Galones
    	 , gal.Horas
    	 , LAG(GALONES, 1, 0) OVER(PARTITION BY GAL.EQUIPO
    						  ORDER BY EQUIPO, TIQUET  ) AS GALANT1
    	 , LAG(GALONES, 2, 0) OVER(PARTITION BY GAL.EQUIPO
    						  ORDER BY EQUIPO, TIQUET ) AS GALANT2
    	 , LAG(GALONES, 3, 0) OVER(PARTITION BY GAL.EQUIPO
    						  ORDER BY EQUIPO, TIQUET ) AS GALANT3
    	 , LAG(Horas, 1, 0) OVER(PARTITION BY GAL.EQUIPO
    						ORDER BY EQUIPO, TIQUET ) AS HorAnt1
    	 , LAG(Horas, 2, 0) OVER(PARTITION BY GAL.EQUIPO
    						ORDER BY EQUIPO, TIQUET ) AS HorAnt2
    	 , LAG(Horas, 3, 0) OVER(PARTITION BY GAL.EQUIPO
    						ORDER BY EQUIPO, TIQUET ) AS HorAnt3
    	 , LAG(TIQUET, 1, 0) OVER(PARTITION BY GAL.EQUIPO
    						 ORDER BY EQUIPO, TIQUET ) AS TIQUET1
    	 , LAG(TIQUET, 2, 0) OVER(PARTITION BY GAL.EQUIPO
    						 ORDER BY EQUIPO, TIQUET ) AS TIQUET2
    	 , LAG(TIQUET, 3, 0) OVER(PARTITION BY GAL.EQUIPO
    						 ORDER BY EQUIPO, TIQUET ) AS TIQUET3
    	   FROM gal), Calculos As(
    	 SELECT CTE.Equipo
    		  , CTE.Fecha
    		  , CTE.Tiquet
    		  , CTE.Galones
    		  , CTE.Horas
    		  , CASE WHEN CTE.Galones * 1.0 / CTE.HORAS >= 2.5 THEN 
    				CASE WHEN(CTE.Galones + CTE.GALANT1) * 1.0 / (CTE.HORAS + CTE.HorAnt1) >= 2.5 THEN 
    					CASE WHEN(CTE.Galones + CTE.GALANT1 + CTE.GALANT2) * 1.0 / (CTE.HORAS + CTE.HorAnt1 + CTE.HorAnt2) >= 2.5 THEN 
    						CASE WHEN (CTE.Galones + CTE.GALANT1 + CTE.GALANT2 + CTE.GALANT3) * 1.0 / (CTE.HORAS + CTE.HorAnt1 + CTE.HorAnt2 + CTE.HorAnt3) >= 2.5	THEN 
    						 '0'
    						ELSE TIQUET3
    						END
    						ELSE	TIQUET2
    						END
    							 ELSE tiquet1 
    						END
    							 ELSE Tiquet
    			END  AS [Tiq]
    		  , CASE WHEN CTE.Galones * 1.0 / CTE.HORAS >= 2.5 THEN 
    				CASE WHEN(CTE.Galones + CTE.GALANT1) * 1.0 / (CTE.HORAS + CTE.HorAnt1) >= 2.5 THEN 
    					CASE WHEN(CTE.Galones + CTE.GALANT1 + CTE.GALANT2) * 1.0 / (CTE.HORAS + CTE.HorAnt1 + CTE.HorAnt2) >= 2.5 THEN 
    						CASE WHEN (CTE.Galones + CTE.GALANT1 + CTE.GALANT2 + CTE.GALANT3) * 1.0 / (CTE.HORAS + CTE.HorAnt1 + CTE.HorAnt2 + CTE.HorAnt3) >= 2.5	THEN 
    							2.5
    						ELSE (CTE.Galones + CTE.GALANT1 + CTE.GALANT2 + CTE.GALANT3) * 1.0 / (CTE.HORAS + CTE.HorAnt1 + CTE.HorAnt2 + CTE.HorAnt3) 
    						END
    						ELSE	(CTE.Galones + CTE.GALANT1 + CTE.GALANT2) * 1.0 / (CTE.HORAS + CTE.HorAnt1 + CTE.HorAnt2)
    						END
    							 ELSE(CTE.Galones + CTE.GALANT1) * 1.0 / (CTE.HORAS + CTE.HorAnt1)
    						END
    							 ELSE CTE.GALONES * 1.0 / CTE.HORAS
    			END AS [Valor obtenido]
    			FROM CTE
    			)
    			select c.Equipo
    			, Format(c.fecha,'dd/MM/yyyy') as Fecha
    			, c.Tiquet
    			, c.Galones
    			, c.Horas
    			, case when c.[Tiq] = '0' then 'No se obtuvo tiquet' 
    				else cast(c.[Tiq] as varchar(10)) end as [Tiquet donde es < 2.5]
    			, cast (c.[Valor obtenido] as decimal (10,2)) as [Valor obtenido]
    			from Calculos c

    Si te fijas en la query, hay 3 partes diferenciadas. En la primera el cte, obtenemos los valores de las filas, y como columnas adicionales, obtenemos el resultado de la fila anterior para la columna expresada, de manera que por ejemplo GalAnt1 tiene el valor de galones para la fila anterior, siempre dentro del mismo equipo. y así sucesivamente.

    En el conjunto 2, leemos este y hacemos una serie de case encadenados. Cuando se cumpla la condición entramos en el siguiente case, cuando se cumpla al siguiente, cuando se cumpla al siguiente, y en el sino de cada uno de ellos obtenemos la respuesta, por los valores que tenían las columnas para las funciones lag.

    En la salida, formateamos y damos orden a los resultados.

    No se de donde sacas el tiquet de la linea 8 y tampoco me encaja el 2.45 de la linea del tiquet 15, salvo que la suma te de exactamente 2.5 (y por tipos de datos, o conversiones o simplemente porque no tenemos los mismos datos yo lo resuelvo como sin ticket)

    Tablas de expresión común

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

    Lag

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

    Espero te ayude.

    martes, 28 de abril de 2020 3:08

Todas las respuestas

  •   

    Gracias por levantar tu consulta en los foros de MSDN. Entendimos su pregunta y vamos a darle seguimiento para buscar la mejor repuesta pertinente al caso.  

    Cualquier duda referente a productos Microsoft, puedes consultarnos. Es un gusto informarte. 

    Gracias por usar los foros de MSDN.   

    Oystein Edwards 

     ____________________________ 

      

    Por favor recuerde "Marcar como respuesta" las respuestas que hayan resuelto su problema, es una forma común de reconocer a aquellos que han ayudado, y hace que sea más fácil para los otros visitantes encontrar la solución más tarde.  

    Microsoft ofrece este servicio de forma gratuita, con la finalidad de ayudar a los usuarios y la ampliación de la base de datos de conocimientos relacionados con los productos y tecnologías de Microsoft.   

    Este contenido es proporcionado "tal cual" y no implica ninguna responsabilidad de parte de Microsoft. 

    martes, 28 de abril de 2020 1:07
    Moderador
  • Hola German David Quinchia Zapata:

    Te voy a dar una posible solución al problema.

    create table gal (Equipo varchar(6), Fecha date, Tiquet int, Galones tinyint, Horas tinyint)
    go
    DELETE FROM GAL
    Set language spanish;
    insert into gal (Equipo, Fecha, Tiquet, Galones, Horas)
    values
    ('VD-217','27/02/2020',700012,50,20),
    ('VD-217','01/03/2020',700013,30,15),
    ('VD-217','03/03/2020',700014,43,17),
    ('VD-217','05/03/2020',700015,30,11),
    ('VD-318','07/03/2020',700016,10,10),
    ('VD-318','09/03/2020',700017,30,10),
    ('VD-318','11/03/2020',700018,45,16),
    ('VD-318','13/03/2020',700019,50,18);
    GO
    
    
    

    Lo primero es que el escenario no esta claro, porque en la primera tabla en la primera linea, pones galones 50 y en la tabla de resultado pones 60.

    Además al no poner los tipos de datos, los resultados pueden ser diferentes. No obstante, a efectos didácticos es lo mismo.

    WITH CTE
    	 AS (
    SELECT gal.Equipo
    	 , gal.Fecha
    	 , gal.Tiquet
    	 , gal.Galones
    	 , gal.Horas
    	 , LAG(GALONES, 1, 0) OVER(PARTITION BY GAL.EQUIPO
    						  ORDER BY EQUIPO, TIQUET  ) AS GALANT1
    	 , LAG(GALONES, 2, 0) OVER(PARTITION BY GAL.EQUIPO
    						  ORDER BY EQUIPO, TIQUET ) AS GALANT2
    	 , LAG(GALONES, 3, 0) OVER(PARTITION BY GAL.EQUIPO
    						  ORDER BY EQUIPO, TIQUET ) AS GALANT3
    	 , LAG(Horas, 1, 0) OVER(PARTITION BY GAL.EQUIPO
    						ORDER BY EQUIPO, TIQUET ) AS HorAnt1
    	 , LAG(Horas, 2, 0) OVER(PARTITION BY GAL.EQUIPO
    						ORDER BY EQUIPO, TIQUET ) AS HorAnt2
    	 , LAG(Horas, 3, 0) OVER(PARTITION BY GAL.EQUIPO
    						ORDER BY EQUIPO, TIQUET ) AS HorAnt3
    	 , LAG(TIQUET, 1, 0) OVER(PARTITION BY GAL.EQUIPO
    						 ORDER BY EQUIPO, TIQUET ) AS TIQUET1
    	 , LAG(TIQUET, 2, 0) OVER(PARTITION BY GAL.EQUIPO
    						 ORDER BY EQUIPO, TIQUET ) AS TIQUET2
    	 , LAG(TIQUET, 3, 0) OVER(PARTITION BY GAL.EQUIPO
    						 ORDER BY EQUIPO, TIQUET ) AS TIQUET3
    	   FROM gal), Calculos As(
    	 SELECT CTE.Equipo
    		  , CTE.Fecha
    		  , CTE.Tiquet
    		  , CTE.Galones
    		  , CTE.Horas
    		  , CASE WHEN CTE.Galones * 1.0 / CTE.HORAS >= 2.5 THEN 
    				CASE WHEN(CTE.Galones + CTE.GALANT1) * 1.0 / (CTE.HORAS + CTE.HorAnt1) >= 2.5 THEN 
    					CASE WHEN(CTE.Galones + CTE.GALANT1 + CTE.GALANT2) * 1.0 / (CTE.HORAS + CTE.HorAnt1 + CTE.HorAnt2) >= 2.5 THEN 
    						CASE WHEN (CTE.Galones + CTE.GALANT1 + CTE.GALANT2 + CTE.GALANT3) * 1.0 / (CTE.HORAS + CTE.HorAnt1 + CTE.HorAnt2 + CTE.HorAnt3) >= 2.5	THEN 
    						 '0'
    						ELSE TIQUET3
    						END
    						ELSE	TIQUET2
    						END
    							 ELSE tiquet1 
    						END
    							 ELSE Tiquet
    			END  AS [Tiq]
    		  , CASE WHEN CTE.Galones * 1.0 / CTE.HORAS >= 2.5 THEN 
    				CASE WHEN(CTE.Galones + CTE.GALANT1) * 1.0 / (CTE.HORAS + CTE.HorAnt1) >= 2.5 THEN 
    					CASE WHEN(CTE.Galones + CTE.GALANT1 + CTE.GALANT2) * 1.0 / (CTE.HORAS + CTE.HorAnt1 + CTE.HorAnt2) >= 2.5 THEN 
    						CASE WHEN (CTE.Galones + CTE.GALANT1 + CTE.GALANT2 + CTE.GALANT3) * 1.0 / (CTE.HORAS + CTE.HorAnt1 + CTE.HorAnt2 + CTE.HorAnt3) >= 2.5	THEN 
    							2.5
    						ELSE (CTE.Galones + CTE.GALANT1 + CTE.GALANT2 + CTE.GALANT3) * 1.0 / (CTE.HORAS + CTE.HorAnt1 + CTE.HorAnt2 + CTE.HorAnt3) 
    						END
    						ELSE	(CTE.Galones + CTE.GALANT1 + CTE.GALANT2) * 1.0 / (CTE.HORAS + CTE.HorAnt1 + CTE.HorAnt2)
    						END
    							 ELSE(CTE.Galones + CTE.GALANT1) * 1.0 / (CTE.HORAS + CTE.HorAnt1)
    						END
    							 ELSE CTE.GALONES * 1.0 / CTE.HORAS
    			END AS [Valor obtenido]
    			FROM CTE
    			)
    			select c.Equipo
    			, Format(c.fecha,'dd/MM/yyyy') as Fecha
    			, c.Tiquet
    			, c.Galones
    			, c.Horas
    			, case when c.[Tiq] = '0' then 'No se obtuvo tiquet' 
    				else cast(c.[Tiq] as varchar(10)) end as [Tiquet donde es < 2.5]
    			, cast (c.[Valor obtenido] as decimal (10,2)) as [Valor obtenido]
    			from Calculos c

    Si te fijas en la query, hay 3 partes diferenciadas. En la primera el cte, obtenemos los valores de las filas, y como columnas adicionales, obtenemos el resultado de la fila anterior para la columna expresada, de manera que por ejemplo GalAnt1 tiene el valor de galones para la fila anterior, siempre dentro del mismo equipo. y así sucesivamente.

    En el conjunto 2, leemos este y hacemos una serie de case encadenados. Cuando se cumpla la condición entramos en el siguiente case, cuando se cumpla al siguiente, cuando se cumpla al siguiente, y en el sino de cada uno de ellos obtenemos la respuesta, por los valores que tenían las columnas para las funciones lag.

    En la salida, formateamos y damos orden a los resultados.

    No se de donde sacas el tiquet de la linea 8 y tampoco me encaja el 2.45 de la linea del tiquet 15, salvo que la suma te de exactamente 2.5 (y por tipos de datos, o conversiones o simplemente porque no tenemos los mismos datos yo lo resuelvo como sin ticket)

    Tablas de expresión común

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

    Lag

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

    Espero te ayude.

    martes, 28 de abril de 2020 3:08
  • Hola Javi, muchas gracias por la respuesta, la cual me dio claridad y me permite entender el funcionamiento de las tablas en sql server, ya que veo que todo los datos necesarios para los cálculos se deben poner en una misma fila.

    Un compañero me dijo que eso también se podía hacer de forma recursiva. Aun no tengo claro el concepto pero procedo a preguntarte por esa forma.


    Germanq

    martes, 28 de abril de 2020 13:37
  • Si se puede utilizar la recursividad, pero es algo que si puedes, tienes que evitar.

    Me explico, un poco más con detalle. La recursividad es algo que se utiliza en lenguajes procedimentales, y funciona muy bien. En Sql server como cualquier otro lenguaje de base de datos, son lenguajes declarativos, donde le dices al motor lo que quieres, y el lo ejecuta "como le parece".

    Esto es yo declaro que quiero una Select con varias columnas mezclando varias tablas con tales restricciones, pero yo no se como lo va a ejecutar. ¿Porque no lo se?. Sencillamente, porque depende de la cantidad de información que tenga cada tabla, entre otros muchos factores. Y en un servidor puede tener x, pero en otro tiene "millones".

    Volviendo un poco al tema, sobre la recursividad. Es muy cara, en procesamiento, y los lenguajes de consultas, están orientados a conjuntos, por tanto aunque se puede hacer, y funciona muy bien, no es el propósito.

    Ocurre exactamente lo mismo que con los bucles o cursores.

    Se pueden hacer....SI....se pueden evitar, casi siempre, y es lo más recomendable.

    Un ejemplo de recursividad.

    https://javifer2.wordpress.com/2018/12/20/with-cte-tablas-de-expresion-comun-recursividad-3/

    martes, 28 de abril de 2020 13:55
  • Javi, muchas gracias realmente me queda mas que claro.

    Germanq

    martes, 28 de abril de 2020 15:24
  • De nada, un placer
    martes, 28 de abril de 2020 15:45