none
Necesito ayuda con consulta en SQL Server RRS feed

  • Pregunta

  • ¡Hola! Necesito ayuda con una consulta en SQL Server Express (2014).

    Se que parte de esta pregunta ya fue formulada anteriormente y dejo su url para que se puedan orientar un poco; por si la información no es suficiente:

    Ayuda con una Consulta en SQL Server

    Resulta que ahora tengo la siguiente consulta:

    USE tracking SELECT Items.imei, Us.Nombres, Items.id_ruta AS Ruta, SUM(ruta.nro_items) as Bips, SUM(ruta.nro_items_ingresados) as 'Ejecutados', ((COUNT(nro_items_ingresados) * 100)/(SELECT COUNT(*) FROM ruta_actual)) AS 'Porcentaje %', COUNT(*) AS 'Total Ejecutados' FROM ruta_actual as ruta INNER JOIN usuarios AS Us ON Us.id_usuario = id_ruta INNER JOIN items_tracking AS Items ON Items.id_ruta = ruta.id_ruta INNER JOIN movil AS M ON M.id_usuario = Items.id_usuario WHERE ruta.nro_items_ingresados = 1

    GROUP BY Items.imei, Nombres, Items.id_ruta ORDER BY Items.id_ruta GO

    Me gustaría agregar una nueva columna la cual se llamase 'Total no Ejecutados' el cual consiste en contar la cantidad de registro totales de la columna nro_items_ingresados de la tabla 'ruta_actual' pero cuyo valor sea igual a 0, que es el que le corresponde, ya que para la columna 'Total Ejecutados' se contó los valores que fuesen igual a 1 como lo indica la clausula WHERE. He probado de distintas maneras agregando al WHERE:

    --... AND WHHERE nro_items_ingresados = 0

    --GROUP BY...

    -- (2da Manera)

    -- ... OR

    WHERE nro_items_ingresados = 0

    --GROUP BY...

    Pero de ese modo no funciona. Les muestro una imagen del resultado que debería dar, FIJENSE BIEN en la columna 'Total no Ejecutados'


    Esos son los valores que deben dar para esa columna, pero hasta el momento no he logrado que me funcione.

    NOTA: Ahí quite una columna para mostrar bien esos datos.

    Les dejo nuevamente el código que publiqué en un principio, ahora algo editado.

    USE tracking

    SELECT Items.imei, Us.Nombres, Items.id_ruta AS Ruta, SUM(ruta.nro_items) as Bips, SUM(ruta.nro_items_ingresados) as 'Ejecutados', ((COUNT(nro_items_ingresados) * 100)/(SELECT COUNT(*) FROM ruta_actual)) AS 'Porcentaje %', COUNT(*) AS 'Total Ejecutados',

    COUNT(ruta.nro_items_ingresados) AS 'Total no Ejecutados'

    FROM ruta_actual as ruta INNER JOIN usuarios AS Us ON Us.id_usuario = id_ruta INNER JOIN items_tracking AS Items ON Items.id_ruta = ruta.id_ruta INNER JOIN movil AS M ON M.id_usuario = Items.id_usuario WHERE nro_items_ingresados = 1 GROUP BY Items.imei, Nombres, Items.id_ruta ORDER BY Items.id_ruta GO

    Y eso, si me pudieran ayudar se los agradeceria mucho :D

    OJO. Por el momento las ultimas 2 columnas me entregan el mismo resultado, así que sé que el ultimo código que les proporcioné esta malo, por eso explique que lo estoy editando. GRACIAS.


    • Editado M4uriXD jueves, 5 de abril de 2018 18:58
    jueves, 5 de abril de 2018 18:57

Respuestas

  • Hola M4uriXD:

    En la opción de la subconsulta

    SELECT Items.imei, Us.Nombres, Items.id_ruta AS Ruta, 
    	SUM(ruta.nro_items) as Bips, SUM(ruta.nro_items_ingresados) as 'Ejecutados',
    	((COUNT(nro_items_ingresados) * 100)/(SELECT COUNT(*) FROM ruta_actual)) AS 'Porcentaje %',
    	COUNT(*) AS 'Total Ejecutados',
    	
    	(SELECT COUNT(*) 
    		FROM RUTA_ACTUAL R
    			INNER JOIN usuarios AS Us ON Us.id_usuario = id_ruta
    			INNER JOIN  items_tracking AS Items ON Items.id_ruta = ruta.id_ruta
    			INNER JOIN  movil AS M ON M.id_usuario = Items.id_usuario 
    		WHERE 
    			R.ID_RUTA = RUTA.ID_RUTA /*observa que la subconsulta interior se apoya en la consulta exterior */
    			AND R.NRO_ITEMS_INGRESADOS= 0
    	GROUP BY Items.imei, Nombres, Items.id_ruta
    
    )  AS SUBCONSULTACON_TOTALNO_EJECUTADOS 
    /*NOTA:
    Si tienes una consulta original que te da uno count(*) para un conjunto de datos.
    y en la misma, en una de las columnas, aplicas la misma consulta idéntica, pero
    le cambias el valor del where, te ofrece el resultado contrario.
    No es la forma óptima de realizarla, pero no dbe de ser diferente.
     */
    	
    /*cambiar este cero por uno o del reves*/
    
    
    
    	FROM ruta_actual as ruta 
    		INNER JOIN usuarios AS Us ON Us.id_usuario = id_ruta
    		INNER JOIN  items_tracking AS Items ON Items.id_ruta = ruta.id_ruta
    		INNER JOIN  movil AS M ON M.id_usuario = Items.id_usuario
    	WHERE 
    		ruta.nro_items_ingresados = 1
    /*cambiar este cero por uno o del reves */
    
    GROUP BY Items.imei, Nombres, Items.id_ruta
    ORDER BY Items.id_ruta
    GO
    

    Espero te ayude.

    • Marcado como respuesta M4uriXD viernes, 6 de abril de 2018 17:30
    viernes, 6 de abril de 2018 15:57

Todas las respuestas

  • Hola M4uriXD:

    Sigo sin ver claro tu esquema, pero te voy a dar dos posibilidades. La primera con una subconsulta, es más simple, pero su rendimiento siempre es inferior, y la segunda con una tabla derivada.

    No puedo afinar mucho más porque no veo el esquema.

    Espero que te sirva.

    /*OPCION 1 SUBCONSULTA */
    SELECT Items.imei, Us.Nombres, Items.id_ruta AS Ruta,  SUM(ruta.nro_items) as Bips, SUM(ruta.nro_items_ingresados) as 'Ejecutados',
     ((COUNT(nro_items_ingresados) * 100)/(SELECT COUNT(*) FROM ruta_actual)) AS 'Porcentaje %',  COUNT(*) AS 'Total Ejecutados',
     (SELECT COUNT(*) FROM RUTA_ACTUAL R WHERE R.ID_RUTA = RUTA.ID_RUTA AND R.NRO_ITEMS_INGRESADOS= 0)  AS SUBCONSULTACON_TOTALNO_EJECUTADOS
    
    FROM ruta_actual as ruta INNER JOIN usuarios AS Us ON Us.id_usuario = id_ruta
    
    INNER JOIN  items_tracking AS Items ON Items.id_ruta = ruta.id_ruta
    INNER JOIN  movil AS M ON M.id_usuario = Items.id_usuario
    
    WHERE ruta.nro_items_ingresados = 1
    
    
    GROUP BY Items.imei, Nombres, Items.id_ruta
    ORDER BY Items.id_ruta
    GO
    /* OPCION DOS TABLA DERIVADA */
    SELECT Items.imei, Us.Nombres, Items.id_ruta AS Ruta,  SUM(ruta.nro_items) as Bips, SUM(ruta.nro_items_ingresados) as 'Ejecutados',
     ((COUNT(nro_items_ingresados) * 100)/(SELECT COUNT(*) FROM ruta_actual)) AS 'Porcentaje %',  COUNT(*) AS 'Total Ejecutados',
     S.NUMERO AS 'TOTAL_NO_EJECUTADOS'
     
    
    FROM ruta_actual as ruta INNER JOIN usuarios AS Us ON Us.id_usuario = id_ruta
    
    INNER JOIN  items_tracking AS Items ON Items.id_ruta = ruta.id_ruta
    INNER JOIN  movil AS M ON M.id_usuario = Items.id_usuario
    	(
    		SELECT COUNT(*) AS NUMERO, R.ID_RUTA FROM RUTA_ACTUAL R 
    			WHERE  R.NRO_ITEMS_INGRESADOS= 0 GROUP BY R.ID_RUTA
    			/* AQUI ENTIENDO QUE LA CUENTA ES PORQUE R.ID_RUTA TIENE VARIOS REGISTROS, SI NO ES CORRECTO HAY QUE CAMBIAR ESTE POR EL VALOR
    			DISCRIMINATORIO PARA CADA RUTA */
    			.)  AS S ON S.ID_RUTA = RUTA.ID_RUTA
    	)
    
    WHERE ruta.nro_items_ingresados = 1
    
    
    GROUP BY Items.imei, Nombres, Items.id_ruta
    ORDER BY Items.id_ruta
    GO

    Para la opción 2, te va a dar un error si mal no recuerdo en el group by del conjunto principal porque no tienes mencionada la columna S.NUMERO, no lo he puesto porque no sabía si encaja en lo que planteas. Si es válido, solo tienes que añadirlo.

    Saludos

    viernes, 6 de abril de 2018 6:39
  • Hola, gracias por responder, y en respecto a la pregunta dejo nuevamente el código

    USE tracking SELECT Items.imei, Us.Nombres, Items.id_ruta AS Ruta, SUM(ruta.nro_items) as Bips, SUM(ruta.nro_items_ingresados) as 'Ejecutados', (CONCAT((COUNT(nro_items_ingresados) * 100) / (SELECT COUNT(nro_items_ingresados) FROM ruta_actual),'','%')) AS 'Eficacia', COUNT(ruta.nro_items_ingresados) AS 'Total Ejecutados' FROM ruta_actual as ruta INNER JOIN usuarios AS Us ON Us.id_usuario = id_ruta INNER JOIN items_tracking AS Items ON Items.id_ruta = ruta.id_ruta INNER JOIN movil AS M ON M.id_usuario = Items.id_usuario WHERE nro_items_ingresados = 1

    GROUP BY Items.imei, Nombres, Items.id_ruta

    /*EL VALOR QUE COMPARO DEL ID DE LA RUTA ES EL DE ITEMS, Y NO EL DE RUTA.

    AUNQUE ES CIERTO QUE EN UNA PARTE DENTRO DEL INNER JOIN (Del Items_tracking) EL ID DEL ITEMS

    LO COMPARO EL ID DE LA RUTA_ACTUAL, PERO EN CONCRETO EL RELEVANTE PARA LA CONSULTA ES EL ID DEL ITEMS,

    por eso el ID de la ruta no está en un GROUP BY

    */

    ORDER BY Items.id_ruta

    GO

    Y ahora su resultado:

    La Columna que marque en rojo es la importante

    Ahora la siguiente imagen muestra la columna que quiero agregar (EN ROJO):

    A lo que me refería con la pregunta era que:

    • El valor a comparar es 0 (En la clausula WHERE)
    • Para la 1era foto el valor se comparaba con 1

    Espero que se haya entendido. Quedo Atento... :)


    • Editado M4uriXD viernes, 6 de abril de 2018 14:02
    viernes, 6 de abril de 2018 14:01
  • Hola M4uriXD:

    En la opción de la subconsulta

    SELECT Items.imei, Us.Nombres, Items.id_ruta AS Ruta, 
    	SUM(ruta.nro_items) as Bips, SUM(ruta.nro_items_ingresados) as 'Ejecutados',
    	((COUNT(nro_items_ingresados) * 100)/(SELECT COUNT(*) FROM ruta_actual)) AS 'Porcentaje %',
    	COUNT(*) AS 'Total Ejecutados',
    	
    	(SELECT COUNT(*) 
    		FROM RUTA_ACTUAL R
    			INNER JOIN usuarios AS Us ON Us.id_usuario = id_ruta
    			INNER JOIN  items_tracking AS Items ON Items.id_ruta = ruta.id_ruta
    			INNER JOIN  movil AS M ON M.id_usuario = Items.id_usuario 
    		WHERE 
    			R.ID_RUTA = RUTA.ID_RUTA /*observa que la subconsulta interior se apoya en la consulta exterior */
    			AND R.NRO_ITEMS_INGRESADOS= 0
    	GROUP BY Items.imei, Nombres, Items.id_ruta
    
    )  AS SUBCONSULTACON_TOTALNO_EJECUTADOS 
    /*NOTA:
    Si tienes una consulta original que te da uno count(*) para un conjunto de datos.
    y en la misma, en una de las columnas, aplicas la misma consulta idéntica, pero
    le cambias el valor del where, te ofrece el resultado contrario.
    No es la forma óptima de realizarla, pero no dbe de ser diferente.
     */
    	
    /*cambiar este cero por uno o del reves*/
    
    
    
    	FROM ruta_actual as ruta 
    		INNER JOIN usuarios AS Us ON Us.id_usuario = id_ruta
    		INNER JOIN  items_tracking AS Items ON Items.id_ruta = ruta.id_ruta
    		INNER JOIN  movil AS M ON M.id_usuario = Items.id_usuario
    	WHERE 
    		ruta.nro_items_ingresados = 1
    /*cambiar este cero por uno o del reves */
    
    GROUP BY Items.imei, Nombres, Items.id_ruta
    ORDER BY Items.id_ruta
    GO
    

    Espero te ayude.

    • Marcado como respuesta M4uriXD viernes, 6 de abril de 2018 17:30
    viernes, 6 de abril de 2018 15:57
  • Aplique eso y me sale un error que dice:

    La subconsulta ha devuelto más de un valor, lo que no es correcto cuando va a continuación de =, !=, <, <=, >, >= o cuando se utiliza como expresión.

    viernes, 6 de abril de 2018 17:05
  • Tienes razón.

    Quita el group by de la subconsulta.

    Saludos

    viernes, 6 de abril de 2018 17:21
  • OH! Que sencillo lo hice y me funciona, ahora me muestra los datos que corresponden. Aunque me acabo de dar cuenta que estaba haciendo mal unos cálculos, pero en el fondo era lo que necesitaba. Gracias :)

    • Editado M4uriXD viernes, 6 de abril de 2018 18:27
    viernes, 6 de abril de 2018 17:30