none
Hacer una resta entre la fila inferior y la superior en una tabla de SQL Server RRS feed

  • Pregunta

  • Cordial saludo, estoy trabajado con Microsoft SQL Server management studio 2018 en idioma español, en Windows 10.

    Tengo una tabla con tres campos que son Fecha, equipo y Horas.

    Mi deseo es hacer una función o un procedimiento almacenado (el que mejor aplique) que haga lo siguiente:

    Si la fila inferior y superior tienen el mismo dato en el campo equipo, restar el campo Horas de la fila inferior con el de la fila superior.

    Si la fila inferior y superior tienen un dato diferente en el campo equipo, colocar cero.

    Agradezco su ayuda


    Germanq

    viernes, 24 de abril de 2020 19:16

Respuestas

  • Hola Germanq:

    estoy trabajado con Microsoft SQL Server management studio 2018 en idioma español

    Cuando se habla de consultas o similar, no es un dato importante, ya que el management studio es un cliente del motor sql que tu tengas. El motor es muy importante, porque es quien decidirá en parte las posibles instrucciones TSQL que se pueden utilizar.

    Arroja mucha más claridad saber la versión.

    Select @@Version

    Microsoft SQL Server 2019 (RTM-GDR) (KB4517790) - 15.0.2070.41 (X64) 
    Oct 28 2019 19:56:59 
    Copyright (C) 2019 Microsoft Corporation
    Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 17763: )
    (1 fila afectada)

    esta seria la tabla resultante

    Como ya se que dispones de una versión 2012 o superior, porque en otra pregunta del foro utilizaste try_convert, una posible solución sería.

    CREATE TABLE T_EQ
    			 (
    			 equipo VARCHAR(10)
    		   , fecha  DATE
    		   , horas  SMALLINT
    			 );
    GO
    
    SET DATEFORMAT DMY;
    
    INSERT INTO T_EQ(equipo
    			   , FECHA
    			   , horas)
    VALUES
    	   ( 'VD-302', '22/04/2020', 1300 ),
    	   ( 'VD-302', '23/04/2020', 1320 ),
    	   ( 'MA-05', '22/04/2020', 500 ),
    	   ( 'MA-05', '23/04/2020', 560 );

    Con la tabla creada:

    WITH CTE
    	 AS (SELECT T.EQUIPO
    			  , T.FECHA
    			  , T.HORAS
    			  , LAG(T.HORAS, 1, 0) OVER(PARTITION BY T.EQUIPO
    				ORDER BY T.FECHA DESC) AS ANT
    				FROM T_EQ AS T)
    	 SELECT C.EQUIPO
    		  , C.FECHA
    		  , C.HORAS
    		  , CASE
    				WHEN C.ANT = 0 THEN 0
    				ELSE C.ANT - C.HORAS
    			END AS DIFERENCIA
    			FROM CTE AS C
    	 ORDER BY EQUIPO DESC;

    Salida

    La sentencia funciona de la siguiente manera:

    La tabla de expresión común, nos genera utilizando la función de ventana lag en anterior resultado de la columna T.HORAS (y un cero cuando no hay anterior), volviendo a iniciar por cada equipo.

    SELECT T.EQUIPO
    			  , T.FECHA
    			  , T.HORAS
    			  , LAG(T.HORAS, 1, 0) OVER(PARTITION BY T.EQUIPO
    				ORDER BY T.FECHA DESC) AS ANT
    				FROM T_EQ AS T


    Por tanto a la salida de la tabla de expresión común, nos queda, evaluar si la columna Ant tiene un 0, mediante un case.

    Mi deseo es hacer una función o un procedimiento almacenado

    Depende de cuál sea tú objetivo. Puedes crear una función tipo table in-line, o crear una vista. También puede ser un procedure, pero a priori no parece que sea lo necesario.

    CREATE VIEW dbo.vwResultados AS WITH CTE AS (SELECT T.EQUIPO , T.FECHA , T.HORAS , LAG(T.HORAS, 1, 0) OVER(PARTITION BY T.EQUIPO ORDER BY T.FECHA DESC) AS ANT FROM T_EQ AS T) SELECT C.EQUIPO , C.FECHA , C.HORAS , CASE WHEN C.ANT = 0 THEN 0 ELSE C.ANT - C.HORAS END AS DIFERENCIA FROM CTE AS C; GO

    Select *

    From dbo.vwResultados

    O como función table in-line.

    CREATE FUNCTION dbo.fnResultados (@EQUIPO VARCHAR(10))
    RETURNS TABLE
    RETURN 
    (
    WITH CTE
    	 AS (SELECT T.EQUIPO
    			  , T.FECHA
    			  , T.HORAS
    			  , LAG(T.HORAS, 1, 0) OVER(PARTITION BY T.EQUIPO
    				ORDER BY T.FECHA DESC) AS ANT
    				FROM T_EQ AS T
    				WHERE T.equipo = @EQUIPO
    				)
    	 SELECT C.EQUIPO
    		  , C.FECHA
    		  , C.HORAS
    		  , CASE
    				WHEN C.ANT = 0 THEN 0
    				ELSE C.ANT - C.HORAS
    			END AS DIFERENCIA
    			FROM CTE AS C)
    GO

    Para un uso por equipo y así poder utilizarla en otras consultas

    SELECT DISTINCT T.equipo, fn.*
    FROM T_EQ T CROSS APPLY dbo.fnResultados(t.equipo) fn
    WHERE T.equipo = 'MA-05'
    

    LAG

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

    Tablas de expresión común (CTE)

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

    sábado, 25 de abril de 2020 5:36

Todas las respuestas

  • Hola German David Quinchia Zapata

     

    Gracias por levantar tu consulta en los foros de MSDN. Con respecto a la misma, vamos a darte seguimiento e investigaremos para buscar la mejor respuesta para ti.

     
    Podrias especificarme un poquito mas los casos?


    En un caso asi, Busca Rojo y resta el inferior con el superior, pero como quieres que te deje el resultado?

    O mas bien quieres unificar esa fila y dejar solo una?

    Si puedes tambien detallar un poco el caso donde son diferentes los equipos:

    Aca indicas que deseas ponerle cero a los valores distintos consecutivos en equipo, por ende Azul y rojo ambos quedarian en cero cierto?

    Estamos en busca de la mejor respuesta para ti

    Gracias por usar los foros de MSDN.

     

    Luis Diego Mora

     ____

     

    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.

    viernes, 24 de abril de 2020 20:21
    Moderador
  • Hola Luis diego, luego de hacer la resta, se deberia generar una tabla donde se vean las restas.

    esta seria la tabla original

    equipo fecha horas
    VD-302 22/04/2020 1300
    VD-302 23/04/2020 1320
    MA-05 22/04/2020 500
    MA-05 23/04/2020 560

    y esta seria la tabla resultante

    equipo fecha horas  diferencia
    VD-302 22/04/2020 1300 0
    VD-302 23/04/2020 1320 20
    MA-05 22/04/2020 500 0
    MA-05 23/04/2020 560 60

    En este ejemplo, la fila 1 da cero porque no tiene hacia arriba con quien comparar,se compara la fila 2 y 1 y da 20 (se hace la resta porque son el mismo equipo), luego se compara la fila 3 y 2 y da cero (por no son el mismo equipos, luego se compara la 4 y 3 y da 60 (se hace la resta porque son el mismo equipo)

    Espero esta vez haya sido mas clara mi solicitud.


    Germanq


    viernes, 24 de abril de 2020 22:10
  • Hola Germanq:

    estoy trabajado con Microsoft SQL Server management studio 2018 en idioma español

    Cuando se habla de consultas o similar, no es un dato importante, ya que el management studio es un cliente del motor sql que tu tengas. El motor es muy importante, porque es quien decidirá en parte las posibles instrucciones TSQL que se pueden utilizar.

    Arroja mucha más claridad saber la versión.

    Select @@Version

    Microsoft SQL Server 2019 (RTM-GDR) (KB4517790) - 15.0.2070.41 (X64) 
    Oct 28 2019 19:56:59 
    Copyright (C) 2019 Microsoft Corporation
    Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 17763: )
    (1 fila afectada)

    esta seria la tabla resultante

    Como ya se que dispones de una versión 2012 o superior, porque en otra pregunta del foro utilizaste try_convert, una posible solución sería.

    CREATE TABLE T_EQ
    			 (
    			 equipo VARCHAR(10)
    		   , fecha  DATE
    		   , horas  SMALLINT
    			 );
    GO
    
    SET DATEFORMAT DMY;
    
    INSERT INTO T_EQ(equipo
    			   , FECHA
    			   , horas)
    VALUES
    	   ( 'VD-302', '22/04/2020', 1300 ),
    	   ( 'VD-302', '23/04/2020', 1320 ),
    	   ( 'MA-05', '22/04/2020', 500 ),
    	   ( 'MA-05', '23/04/2020', 560 );

    Con la tabla creada:

    WITH CTE
    	 AS (SELECT T.EQUIPO
    			  , T.FECHA
    			  , T.HORAS
    			  , LAG(T.HORAS, 1, 0) OVER(PARTITION BY T.EQUIPO
    				ORDER BY T.FECHA DESC) AS ANT
    				FROM T_EQ AS T)
    	 SELECT C.EQUIPO
    		  , C.FECHA
    		  , C.HORAS
    		  , CASE
    				WHEN C.ANT = 0 THEN 0
    				ELSE C.ANT - C.HORAS
    			END AS DIFERENCIA
    			FROM CTE AS C
    	 ORDER BY EQUIPO DESC;

    Salida

    La sentencia funciona de la siguiente manera:

    La tabla de expresión común, nos genera utilizando la función de ventana lag en anterior resultado de la columna T.HORAS (y un cero cuando no hay anterior), volviendo a iniciar por cada equipo.

    SELECT T.EQUIPO
    			  , T.FECHA
    			  , T.HORAS
    			  , LAG(T.HORAS, 1, 0) OVER(PARTITION BY T.EQUIPO
    				ORDER BY T.FECHA DESC) AS ANT
    				FROM T_EQ AS T


    Por tanto a la salida de la tabla de expresión común, nos queda, evaluar si la columna Ant tiene un 0, mediante un case.

    Mi deseo es hacer una función o un procedimiento almacenado

    Depende de cuál sea tú objetivo. Puedes crear una función tipo table in-line, o crear una vista. También puede ser un procedure, pero a priori no parece que sea lo necesario.

    CREATE VIEW dbo.vwResultados AS WITH CTE AS (SELECT T.EQUIPO , T.FECHA , T.HORAS , LAG(T.HORAS, 1, 0) OVER(PARTITION BY T.EQUIPO ORDER BY T.FECHA DESC) AS ANT FROM T_EQ AS T) SELECT C.EQUIPO , C.FECHA , C.HORAS , CASE WHEN C.ANT = 0 THEN 0 ELSE C.ANT - C.HORAS END AS DIFERENCIA FROM CTE AS C; GO

    Select *

    From dbo.vwResultados

    O como función table in-line.

    CREATE FUNCTION dbo.fnResultados (@EQUIPO VARCHAR(10))
    RETURNS TABLE
    RETURN 
    (
    WITH CTE
    	 AS (SELECT T.EQUIPO
    			  , T.FECHA
    			  , T.HORAS
    			  , LAG(T.HORAS, 1, 0) OVER(PARTITION BY T.EQUIPO
    				ORDER BY T.FECHA DESC) AS ANT
    				FROM T_EQ AS T
    				WHERE T.equipo = @EQUIPO
    				)
    	 SELECT C.EQUIPO
    		  , C.FECHA
    		  , C.HORAS
    		  , CASE
    				WHEN C.ANT = 0 THEN 0
    				ELSE C.ANT - C.HORAS
    			END AS DIFERENCIA
    			FROM CTE AS C)
    GO

    Para un uso por equipo y así poder utilizarla en otras consultas

    SELECT DISTINCT T.equipo, fn.*
    FROM T_EQ T CROSS APPLY dbo.fnResultados(t.equipo) fn
    WHERE T.equipo = 'MA-05'
    

    LAG

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

    Tablas de expresión común (CTE)

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

    sábado, 25 de abril de 2020 5:36
  • Gracias Javi, aprendi la funcion LAG la cual dio respuesta a mi solicitud.

    Germanq

    lunes, 27 de abril de 2020 14:11