SoloCodigo

Bases de Datos => SQL Server => Mensaje iniciado por: ProfesorX en Lunes 30 de Mayo de 2011, 07:10

Título: Crear una tabla con totales
Publicado por: ProfesorX en Lunes 30 de Mayo de 2011, 07:10
Bueno, ahora es mi turno de pedir ayuda, a ver si alguien me puede ayudar.

Mi problema consiste en lo siguiente, necesito sacar un total de clientes activos e inactivos por colonia, en una sola tabla, de tal forma que quede de la siguiente manera:

Código: Text
  1.  
  2. Colonia Activos Inactivos
  3. A          2         9
  4. B          0         2
  5. C          0         1
  6. D          1         1
  7.  
  8.  

Actualmente saco la informacion de 2 tablas (clientes y contrato), y tengo que crear 3 tablas de la siguiente manera:

Código: SQL
  1.  
  2. -- Creacion de la tabla activos
  3. DROP TABLE colonia_activos
  4. SELECT c1.colonia, COUNT(DISTINCT c1.id) AS activos
  5. INTO colonia_activos
  6. FROM clientes AS c1
  7. INNER JOIN contrato AS c2
  8. ON c1.id = c2.cliente
  9. WHERE c2.cerrado = 0
  10. GROUP BY c1.colonia
  11.  
  12. -- Creacion de la tabla inactivos
  13. DROP TABLE colonia_inactivos
  14. SELECT c1.colonia, COUNT(DISTINCT c1.id) AS inactivos
  15. INTO colonia_inactivos
  16. FROM clientes AS c1
  17. INNER JOIN contrato AS c2
  18. ON c1.id = c2.cliente
  19. WHERE c2.cliente NOT IN
  20. (SELECT cliente FROM contrato WHERE cerrado = 0)
  21. GROUP BY c1.colonia
  22.  
  23. -- Creacion de la tabla totales
  24. DROP TABLE colonia_totales
  25. SELECT DISTINCT c1.colonia, c2.activos, c3.inactivos
  26. INTO colonia_totales
  27. FROM clientes AS c1
  28. LEFT JOIN colonia_activos AS c2
  29. ON c1.colonia = c2.colonia
  30. LEFT JOIN colonia_inactivos AS c3
  31. ON c1.colonia = c3.colonia
  32. ORDER BY c1.colonia
  33.  
  34. UPDATE colonia_totales
  35. SET activos = 0
  36. WHERE activos IS NULL
  37. UPDATE colonia_totales
  38. SET inactivos = 0
  39. WHERE inactivos IS NULL
  40. SELECT * FROM colonia_totales
  41.  
  42.  

Quisiera saber si es posible sacar esta informacion creando esa ultima tabla de totales, de una manera mas simple, sin tener que crear 2 tablas intermedias.

Intente lo siguiente:

Código: SQL
  1.  
  2. SELECT c1.colonia,
  3. COUNT(DISTINCT c2.cliente) AS activos,
  4. COUNT(DISTINCT c3.cliente) AS inactivos
  5. FROM clientes AS c1
  6. INNER JOIN contrato AS c2
  7. ON c1.id = c2.cliente
  8. INNER JOIN contrato AS c3
  9. ON c1.id = c3.cliente
  10. WHERE c2.cerrado = 0
  11. OR
  12. c3.cliente NOT IN
  13. (SELECT cliente FROM contrato WHERE cerrado = 0)
  14. GROUP BY c1.colonia
  15. ORDER BY c1.colonia
  16.  
  17.  

Pero ademas de que la consulta demora 15 segundos, a diferencia de las anteriores, que hace todo en 2 segundos, no me arroja el resultado correcto, sino que me arroja la suma de activos e inactivos, de la siguiente manera:

Código: Text
  1.  
  2. Colonia Activos Inactivos
  3. A          11       11
  4. B          2         2
  5. C          1         1
  6. D          2         2
  7.  
  8.  

Pero eso mismo ya lo obtengo de manera mas eficiente (un segundo) de la siguiente manera:

Código: SQL
  1.  
  2. SELECT c1.colonia, COUNT(DISTINCT c1.id) AS total
  3. FROM clientes AS c1
  4. INNER JOIN contrato AS c2
  5. ON c1.id = c2.cliente
  6. GROUP BY c1.colonia
  7. ORDER BY c1.colonia
  8.  
  9.  

Repito mi pregunta, alguna idea para sacar el resultado de totales que deseo, sin tener que crear 3 tablas intermedias?

Uso SQL Server 2000

Saludos :)
Título: Re: Crear una tabla con totales
Publicado por: F_Tanori en Miércoles 1 de Junio de 2011, 08:49
Bueno con lo que haz dado, y sin probar :P, puedes intentar esta idea preeliminar, a ver si te sirve, (y despues viene optimizacion)

Código: SQL
  1. SELECT Colonia,SUM(activos) 'Activos',SUM(inactivos) 'Inactivos' FROM (
  2.     --Activos
  3.     SELECT c1.colonia 'Colonia', COUNT(DISTINCT c1.id) AS activos,0 AS inactivos
  4.     FROM clientes AS c1
  5.       INNER JOIN contrato AS c2
  6.       ON c1.id = c2.cliente
  7.       WHERE c2.cerrado = 0
  8.     GROUP BY c1.colonia
  9. UNION
  10.     -- Inactivos
  11.     SELECT c1.colonia, 0 , COUNT(DISTINCT c1.id)
  12.     FROM clientes AS c1
  13.      INNER JOIN contrato AS c2
  14.      ON c1.id = c2.cliente
  15.      WHERE c2.cliente NOT IN
  16.        (SELECT cliente FROM contrato WHERE cerrado = 0)
  17.      GROUP BY c1.colonia
  18. ) AS Tabla
  19. GROUP BY colonia
  20.  

Saludos