none
Problema a sumar RRS feed

  • Pregunta

  • Hola a todos 

    Tengo el siguiente caso , lo que pasa es que construyo el query dinamicamente y las columnas finales por igual :

     DECLARE @cols NVARCHAR (MAX)
    DECLARE @colsWithNoNulls    NVARCHAR(MAX)

          SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(semana) 
                FROM #TODO
    GROUP BY semana
               -- WHERE Active = 1 AND StateId IS NOT NULL
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')

        SET @colsWithNoNulls = STUFF(
                (
                    SELECT  ',ISNULL(' + QUOTENAME(semana) + ', ''OK'') ' + QUOTENAME(semana)
                    FROM #TODO
                   -- WHERE Active = 1 AND StateId IS NOT NULL
        GROUP BY semana
        ORDER BY semana ASC
                    FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')

        

    DECLARE @query NVARCHAR(MAX)
            SET @query = '  
          SELECT A.*, ISNULL(P2.Semanas_OK,0) Semanas_OK , ISNULL(P2.Semanas_Con_Falla,0) AS               Semanas_Con_Falla , P2.Impacto_Economico_Acomulado
        FROM 
       (         
      SELECT Cliente, Sucursal, Equipo ,id,Location, ' + @colsWithNoNulls + '
    FROM 
       (
    SELECT  idCliente, Sucursal, Equipo ,id, Location, semana, FV 
       FROM #TODO
    ) x
    PIVOT 
    (
    MAX(FV)
    FOR [semana] IN (' + @cols + ')
    ) p   
      )A

      INNER JOIN #PART2 P2 ON ( A.id= P2.id AND A.Location = P2.Location)



         EXEC SP_EXECUTESQL  @query

    El problema radica en SEMANAS_OK  ( columna ) y semanas Semanas_Con_Falla ( columna ) , me suma mal ya que no tengo el control de cuantas columnas tengo que sumar , lo podría hacer de una manera manual pero no es el caso de poner 52 semanas y sumar cada una de ellas , mi pregunta es ? existe una manera automática de así decirlo para que sumara todos los OK de todas las columnas que lo tengas y así mismo las columnas que tengas diferente de OK 

    

    Algún comentario que tengas al respecto o sugerencia a resolver dicha problemática se los agradecería 


    Pregunta


    • Editado Dave2203 domingo, 22 de marzo de 2020 4:53
    domingo, 22 de marzo de 2020 4:52

Respuestas

  • Hola Dave2203:

    Sin cambiar la manera en la que tienes la query, las semanas, las puedes calcular de una manera simple en otro conjunto correlativo, de manera que las tengas todas, y luego llenas los datos por estas.

    WITH CTE
    	 AS (SELECT A.Cliente
    			  , A.Sucursal
    			  , A.Equipo
    			  , A.id
    			  , A.Location
    			  , VECES
    			  , SEMANA
    			  , Problema
    			  , idProblema
    			  , Impacto_Economico_Acomulado
    				FROM
    		 (
    			 SELECT A.Cliente
    				  , id
    				  , Location
    				  , Problema
    				  , semana
    				  , COUNT(Problema) VECES
    				  , SUM(Impacto_Economico_Acomulado) AS Impacto_Economico_Acomulado
    				  , 7 AS OK
    				  , Sucursal
    				  , Equipo
    				  , idProblema
    					FROM
    			 (
    				 SELECT Cliente
    					  , id
    					  , Location
    					  , idProblema
    					  , Problema
    					  , Impacto_Economico_Acomulado
    					  , fecha
    					  , DATEPART(wk, fecha) AS semana
    					  , Sucursal
    					  , Equipo
    						FROM Data.dbo.Data_Problemas
    			 ) A
    					WHERE idVBox = 2017
    					GROUP BY A.Cliente
    						   , id
    						   , Location
    						   , Problema
    						   , Semana
    						   , Sucursal
    						   , Equipo
    						   , idProblema
    		 ) A),
    	 topten
    	 AS (SELECT t.Cliente
    			  , t.Sucursal
    			  , t.Equipo
    			  , t.id
    			  , t.Location
    			  , t.VECES
    			  , t.SEMANA
    			  , t.Problema
    			  , t.idProblema
    			  , t.Impacto_Economico_Acomulado
    				FROM CTE t
    					 INNER JOIN
    		 (
    			 SELECT MAX(C.VECES) AS Veces
    				  , semana
    				  , Location
    				  , Id
    					FROM CTE C
    					GROUP BY semana
    						   , Location
    						   , Id
    		 ) C ON C.Veces = t.VECES
    				AND t.semana = C.semana
    				AND t.Location = C.Location
    				AND t.id = C.id)
    	, diferentesSemanas As(
    		Select Semana From topten
    		group by semana 
    	)
    	 SELECT A.Cliente
    		  , A.Sucursal
    		  , A.Equipo
    		  , A.id
    		  , A.Location
    		  , A.VECES
    		  , A.semana
    		  , A.idProblema
    		  , CASE
    				WHEN FV = 'OK' THEN 0
    			  ELSE A.Impacto_Economico_Acomulado
    			END Impacto_Economico_Acomulado
    		  , FV
    	 INTO #TODO
    			FROM
    	 (
    		 SELECT topten.Cliente
    			  , topten.Sucursal
    			  , topten.Equipo
    			  , topten.id
    			  , topten.Location
    			  , topten.VECES
    			  , topten.SEMANA
    			  , topten.Problema
    			  , topten.idProblema
    			  , topten.Impacto_Economico_Acomulado
    			  , topten.[*]
    			  , CASE
    					WHEN VECES < 4 THEN 'OK'
    					   ELSE Problema
    				END FV
    			  , d.semana
    				FROM DiferentesSemanas d left join topten on d.semana = topten.semana
    	 ) A;

    Sin entrar a valorar la lógica de la query, ni como optimizarla. Fíjate que antes de resolver el cte, he introducido una simple query sobre topten, que devuelve todas las semanas (aquí si quieres eliminar alguna, o establecer algún tipo de restricción o case, puedes hacerlo para que solo te traiga las semanas que necesites). Luego la salida de la tabla de expresión común en su conjunto derivado a, hacemos un left join con topTen, y como diferentesSemanas, las tiene todas, ya puedes tener los registros necesarios, para luego trabajarlos.

    Espero te ayude

    • Propuesto como respuesta Óscar NavarroModerator lunes, 23 de marzo de 2020 14:14
    • Votado como útil Dave2203 lunes, 23 de marzo de 2020 14:27
    • Marcado como respuesta Dave2203 martes, 24 de marzo de 2020 21:49
    domingo, 22 de marzo de 2020 21:32
    • Marcado como respuesta Dave2203 martes, 24 de marzo de 2020 21:49
    martes, 24 de marzo de 2020 20:56

Todas las respuestas

  • Hola Dave2203:

    me suma mal

    Es casi imposible, que te sume mal, porque no hay ninguna función sum dentro del código que has expuesto.

    Como no has contado de que va, ni que tienes, ni el origen de datos, ni la salida esperada, parece bastante difícil ofrecerte ayuda. Aunque a priori lo más simple es la columna fv

    DECLARE @cols NVARCHAR (MAX)
    DECLARE @colsWithNoNulls    NVARCHAR(MAX)
    
    SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(semana) 
                FROM #TODO
    			GROUP BY semana
               -- WHERE Active = 1 AND StateId IS NOT NULL
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'');
    
    SET @colsWithNoNulls = STUFF(
           (
            SELECT  ',ISNULL(' + QUOTENAME(semana) + ', ''OK'') ' + QUOTENAME(semana)
            FROM #TODO
                   -- WHERE Active = 1 AND StateId IS NOT NULL
    		GROUP BY semana
    	    ORDER BY semana ASC
                    FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'');
    
    DECLARE @query NVARCHAR(MAX)
            SET @query = '  
          SELECT A.*, ISNULL(P2.Semanas_OK,0) Semanas_OK , ISNULL(P2.Semanas_Con_Falla,0) AS 
    	                Semanas_Con_Falla , P2.Impacto_Economico_Acomulado
        FROM 
    		(         
    		SELECT Cliente, Sucursal, Equipo ,id,Location, ' + @colsWithNoNulls + '
    		FROM 
    			(
    			SELECT  idCliente, Sucursal, Equipo ,id, Location, semana, FV 
    			FROM #TODO
    			) x
    			PIVOT 
    			(
    			SUM(FV)
    				FOR [semana] IN (' + @cols + ')
    			) p   
    		)A
    		INNER JOIN #PART2 P2 ON ( A.id= P2.id AND A.Location = P2.Location);
    ';
    
    EXEC SP_EXECUTESQL  @query


     

    domingo, 22 de marzo de 2020 6:25
  • Que tal Javi

    Muy cierto no puse el origen de los datos aqui el origen de datos 

    ;WITH CTE AS
    (

    SELECT  A.Cliente, A.Sucursal, A.Equipo ,A.id, A.Location ,VECES,SEMANA, Problema, idProblema, Impacto_Economico_Acomulado
      FROM 
    (

    SELECT A.Cliente, id, Location ,Problema, semana, COUNT( Problema ) VECES, SUM(Impacto_Economico_Acomulado) AS Impacto_Economico_Acomulado, 7 AS OK, Sucursal, Equipo, idProblema
      FROM  
    (
    SELECT Cliente, id, Location ,idProblema, Problema, Impacto_Economico_Acomulado, fecha, DATEPART( wk, fecha) AS semana, Sucursal, Equipo
      FROM [Data].[dbo].[Data_Problemas]
    )A
    WHERE idVBox = 2017
    GROUP BY  A.Cliente, id, Location , Problema, Semana, Sucursal, Equipo,idProblema
    )A

    ), topten AS
    (

       SELECT t.*
    FROM CTE t
      INNER JOIN 
      (
    SELECT MAX(C.VECES) AS Veces, semana , Location, Id
       FROM CTE C
       GROUP BY semana, Location, Id
      )C
      ON (  C.Veces = t.VECES  AND t.semana = C.semana AND t.Location = C.Location AND t.id = C.id )

    )

    SELECT A.Cliente, A.Sucursal, A.Equipo, A.id, A.Location, A.VECES, A.semana, A.idProblema, CASE WHEN FV = 'OK' THEN 0 ELSE A.Impacto_Economico_Acomulado END Impacto_Economico_Acomulado, FV
      INTO #TODO
    FROM 
    (
    SELECT * , CASE WHEN VECES < 4 THEN 'OK' ELSE Problema END FV
    FROM topten
    )A

    Este ultimo pedazo del query es cuando digo que si el FV < 4 debe de poner OK de lo controrario pone un problema , el problema de aqui es que no todas las semanas hay un problema en este caso no hay datos de la semana 5 , pero si de la 6 a la 12 , y no tengo la forma de rellenar aquellas semanas que no hay datos por eso hice lo del 

     SET @colsWithNoNulls = STUFF(
                (
                    SELECT  ',ISNULL(' + QUOTENAME(semana) + ', ''OK'') ' + QUOTENAME(semana)
                    FROM #TODO
                   -- WHERE Active = 1 AND StateId IS NOT NULL
        GROUP BY semana
        ORDER BY semana ASC
                    FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')

     tengo otro query que hago el conteo aparte de los OK y NO OK por así llamarlos , donde cuento cuantas semanas tiene el OK y cuantas NO 

    SELECT T.id,
           T.Location,
           SUM ( T.Impacto_Economico_Acomulado ) AS Impacto_Economico_Acomulado, 
           SUM ( CASE WHEN T.FV = 'OK' OR T.FV IS NULL THEN 1 END ) AS 'Semanas_OK',
       SUM ( CASE WHEN T.FV != 'OK' THEN 1 END ) AS 'Semanas_Con_Falla'
      INTO #PART2
      FROM #TODO T

    Después hago lo del query dinámico 

    DECLARE @query NVARCHAR(MAX)
            SET @query = '  
    		      SELECT A.*, ISNULL(P2.Semanas_OK,0) Semanas_OK , ISNULL(P2.Semanas_Con_Falla,0) AS Semanas_Con_Falla , P2.Impacto_Economico_Acomulado
    			    FROM 
    				   (         
    						  SELECT idCliente, Sucursal, Equipo ,idVBox, numLocation, ' + @colsWithNoNulls + '
    							FROM 
    							   (
    									 SELECT  idCliente, Sucursal, Equipo ,idVBox, numLocation, semana, FV 
    									   FROM #TODO
    								 ) x
    								 PIVOT 
    								 (
    									 MAX(FV)
    									 FOR [semana] IN (' + @cols + ')
    								) p   
    					  )A
    
    					  INNER JOIN #PART2 P2 ON ( A.idVBox = P2.idVBox AND A.numLocation = P2.numLocation)
    					 ' 
    					
    					    
         EXEC SP_EXECUTESQL  @query
    Pero el problema surge que no cuento aquellas semanas que vienen de la variable @colsWithNoNulls.

    Por eso mi pregunta que si existe alguna función que cuente todas columnas OK ya que como las hago dinámicas no tengo la manera de contar las columnas que vienen de la variable @colsWithNoNulls.  Si tiene algun sugerencia de como hacerlo o otra manera de desarrollar se los agradeceria.


    Pregunta

    domingo, 22 de marzo de 2020 19:02
  • Hola Dave2203:

    Sin cambiar la manera en la que tienes la query, las semanas, las puedes calcular de una manera simple en otro conjunto correlativo, de manera que las tengas todas, y luego llenas los datos por estas.

    WITH CTE
    	 AS (SELECT A.Cliente
    			  , A.Sucursal
    			  , A.Equipo
    			  , A.id
    			  , A.Location
    			  , VECES
    			  , SEMANA
    			  , Problema
    			  , idProblema
    			  , Impacto_Economico_Acomulado
    				FROM
    		 (
    			 SELECT A.Cliente
    				  , id
    				  , Location
    				  , Problema
    				  , semana
    				  , COUNT(Problema) VECES
    				  , SUM(Impacto_Economico_Acomulado) AS Impacto_Economico_Acomulado
    				  , 7 AS OK
    				  , Sucursal
    				  , Equipo
    				  , idProblema
    					FROM
    			 (
    				 SELECT Cliente
    					  , id
    					  , Location
    					  , idProblema
    					  , Problema
    					  , Impacto_Economico_Acomulado
    					  , fecha
    					  , DATEPART(wk, fecha) AS semana
    					  , Sucursal
    					  , Equipo
    						FROM Data.dbo.Data_Problemas
    			 ) A
    					WHERE idVBox = 2017
    					GROUP BY A.Cliente
    						   , id
    						   , Location
    						   , Problema
    						   , Semana
    						   , Sucursal
    						   , Equipo
    						   , idProblema
    		 ) A),
    	 topten
    	 AS (SELECT t.Cliente
    			  , t.Sucursal
    			  , t.Equipo
    			  , t.id
    			  , t.Location
    			  , t.VECES
    			  , t.SEMANA
    			  , t.Problema
    			  , t.idProblema
    			  , t.Impacto_Economico_Acomulado
    				FROM CTE t
    					 INNER JOIN
    		 (
    			 SELECT MAX(C.VECES) AS Veces
    				  , semana
    				  , Location
    				  , Id
    					FROM CTE C
    					GROUP BY semana
    						   , Location
    						   , Id
    		 ) C ON C.Veces = t.VECES
    				AND t.semana = C.semana
    				AND t.Location = C.Location
    				AND t.id = C.id)
    	, diferentesSemanas As(
    		Select Semana From topten
    		group by semana 
    	)
    	 SELECT A.Cliente
    		  , A.Sucursal
    		  , A.Equipo
    		  , A.id
    		  , A.Location
    		  , A.VECES
    		  , A.semana
    		  , A.idProblema
    		  , CASE
    				WHEN FV = 'OK' THEN 0
    			  ELSE A.Impacto_Economico_Acomulado
    			END Impacto_Economico_Acomulado
    		  , FV
    	 INTO #TODO
    			FROM
    	 (
    		 SELECT topten.Cliente
    			  , topten.Sucursal
    			  , topten.Equipo
    			  , topten.id
    			  , topten.Location
    			  , topten.VECES
    			  , topten.SEMANA
    			  , topten.Problema
    			  , topten.idProblema
    			  , topten.Impacto_Economico_Acomulado
    			  , topten.[*]
    			  , CASE
    					WHEN VECES < 4 THEN 'OK'
    					   ELSE Problema
    				END FV
    			  , d.semana
    				FROM DiferentesSemanas d left join topten on d.semana = topten.semana
    	 ) A;

    Sin entrar a valorar la lógica de la query, ni como optimizarla. Fíjate que antes de resolver el cte, he introducido una simple query sobre topten, que devuelve todas las semanas (aquí si quieres eliminar alguna, o establecer algún tipo de restricción o case, puedes hacerlo para que solo te traiga las semanas que necesites). Luego la salida de la tabla de expresión común en su conjunto derivado a, hacemos un left join con topTen, y como diferentesSemanas, las tiene todas, ya puedes tener los registros necesarios, para luego trabajarlos.

    Espero te ayude

    • Propuesto como respuesta Óscar NavarroModerator lunes, 23 de marzo de 2020 14:14
    • Votado como útil Dave2203 lunes, 23 de marzo de 2020 14:27
    • Marcado como respuesta Dave2203 martes, 24 de marzo de 2020 21:49
    domingo, 22 de marzo de 2020 21:32
  • Hola javi

    ya intente lo que menciono , pero no me sale el resultado deseado, ya mire lo del CTE de diferentesSemanas ( Aqui es donde trae las semanas ) , se supone que en el query de 

    SELECT topten.Cliente
    			  , topten.Sucursal
    			  , topten.Equipo
    			  , topten.id
    			  , topten.Location
    			  , topten.VECES
    			  , topten.SEMANA
    			  , topten.Problema
    			  , topten.idProblema
    			  , topten.Impacto_Economico_Acomulado
    			  , topten.[*]
    			  , CASE
    					WHEN VECES < 4 THEN 'OK'
    					   ELSE Problema
    				END FV
    			  , d.semana
    				FROM DiferentesSemanas d left join topten on d.semana = topten.semana

    me debe de traer las semanas que tienen datos y las que no tienen también que no ? por eso es el LEFT JOIN, pero en este caso no se que esta pasando que solo me trae los que hay coincidencia en la semana 

    Ejemplo tengo las semana 5,6,7,8,9,10,11,12 pero solo me trae datos de la semana 6 a la 12 que es la que tiene datos y la 5 no me trae nada porque no tiene , pero que no me traer la 5 y me debe de aparecer en null los resultados por eso lo del LEFT JOIN 


    Pregunta

    lunes, 23 de marzo de 2020 14:27
    • Marcado como respuesta Dave2203 martes, 24 de marzo de 2020 21:49
    martes, 24 de marzo de 2020 20:56
  • Gracias Javi por la respuesta , me dio una idea como hacerlo 

    Pregunta

    martes, 24 de marzo de 2020 21:50