Crear consulta MSSQL que agrupe los datos segun parametro

[rafaely2011]

Hola de ante manos gracias por permitirme formar parte de esta comunidad tan buena

Mi problema consiste en crear una consulta que me devuelva la cantidad de recorridos que ha realizado un agente por ruta yo cree una Procedimiento almacendado pero no logro que me lo de agrupado por agente y acantidad total por ruta

Código:

ALTER PROCEDURE [dbo].[ar_Graf]
@ruta numeric
AS

CREATE TABLE #temp
(
nombre varchar(50),
ruta numeric,
cantidad numeric

)

INSERT INTO #temp


SELECT     ar_Tripulaciones.nombre, ar_rutas.ruta, COUNT(ar_rutas.idchofer) AS cantidad
FROM         ar_rutas RIGHT OUTER JOIN
                      ar_Tripulaciones ON ar_rutas.idchofer = ar_Tripulaciones.id
GROUP BY ar_rutas.idchofer, ar_rutas.ruta, ar_Tripulaciones.nombre

UNION ALL

SELECT     ar_Tripulaciones.nombre, ar_rutas.ruta, COUNT(ar_rutas.idjefe) AS cantidad
FROM         ar_rutas RIGHT OUTER JOIN
                      ar_Tripulaciones ON ar_rutas.idjefe = ar_Tripulaciones.id
GROUP BY ar_rutas.idjefe, ar_rutas.ruta, ar_Tripulaciones.nombre

UNION ALL


SELECT     ar_Tripulaciones.nombre, ar_rutas.ruta, COUNT(ar_rutas.idescolta) AS cantidad
FROM         ar_rutas RIGHT OUTER JOIN
                      ar_Tripulaciones ON ar_rutas.idescolta = ar_Tripulaciones.id
GROUP BY ar_rutas.idescolta, ar_rutas.ruta, ar_Tripulaciones.nombre

UNION ALL


SELECT     ar_Tripulaciones.nombre, ar_rutas.ruta, COUNT(ar_rutas.idcajero) AS cantidad
FROM         ar_rutas RIGHT OUTER JOIN
                      ar_Tripulaciones ON ar_rutas.idcajero = ar_Tripulaciones.id
GROUP BY ar_rutas.idcajero, ar_rutas.ruta, ar_Tripulaciones.nombre


SELECT nombre, cantidad ,ruta
FROM #temp
WHERE ruta= @ruta
ORDER BY nombre

a por supesto la estructura de las tablas afectadas

Tabla ar_rutas donde estan los recorridos(rutas) realizados

Código:

CREATE TABLE [dbo].[ar_rutas](
	[codigo] [int] NOT NULL,
	[ruta] [int] NULL,
	[idchofer] [int] NULL,
	[idjefe] [int] NULL,
	[idescolta] [int] NULL,
	[idcajero] [int] NULL,
	[fecha] [datetime] NULL
) ON [PRIMARY]

Tabla ar_Tripulaciones que es donde estan los agentes

Código:

CREATE TABLE [dbo].[ar_Tripulaciones](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[nombre] [varchar](70) NULL,
	[cargo] [varchar](50) NULL,
	[activo] [int] NULL,
	[asignado] [int] NULL
) ON [PRIMARY]

hasta el momneto con lo que tengo creado me muestra

nombre | cantidad |
_________________
nombre 1| 5 |
nombre 1| 4 |
nombre 2| 1 |
nombre 2| 2 |

esto pasa porque cada agente puede haber echo un recorrido como cajero y los otros como chofer y por eso me lo pone separado y lo que quiero es mostrarlo todo total no importa como que lo haya echo

en si lo que quiero es que me muestra todos los nombres de los agentes y al lado la cantidad de veces que salio al recorido teniendo en cuenta el paramtro que yo le pase al SP por ejemplo @ruta = 1 pues entonces me muestre

nombre | cantidad |
_________________
nombre 1 | 5 |
nombre 2 | 2 |
nombre 3 | 1 |

[Libras]

SELECT nombre, sum(cantidad) ,ruta
FROM #temp
WHERE ruta= @ruta
group by nombre,ruta
ORDER BY nombre

nombre | cantidad |
_________________
nombre 1 | 5 |
nombre 2 | 2 |
nombre 3 | 1 |

Aqui no entiendo como sacas el 5 si en esta tabla tiene por ejemplo el nombre 1 2 apariciones

nombre | cantidad |
_________________
nombre 1| 5 |
nombre 1| 4 |
nombre 2| 1 |
nombre 2| 2 |

con lo que el nombre1 tendria 9 en cantidad y el nombre2 3 no???
saludos!

[rafaely2011]

Libras de ante mano gracias por la prontitud de ayuda ya logre solucionarlo despues de un dia entero de intento logre modificar mi SP para lograr mostrar los datos quizas no es la mejor forma pero al final me dio lo que queria aqui esta para si quieres darme alguna sugerencia

SP final

Código:

ALTER PROCEDURE [dbo].[ar_Graf]
@ruta numeric
AS

INSERT INTO ar_temp

SELECT     ar_Tripulaciones.nombre, ar_rutas.ruta, COUNT(ar_rutas.idchofer) AS cantidad
FROM         ar_rutas RIGHT OUTER JOIN
                      ar_Tripulaciones ON ar_rutas.idchofer = ar_Tripulaciones.id
GROUP BY ar_rutas.idchofer, ar_rutas.ruta, ar_Tripulaciones.nombre

UNION ALL

SELECT     ar_Tripulaciones.nombre, ar_rutas.ruta, COUNT(ar_rutas.idjefe) AS cantidad
FROM         ar_rutas RIGHT OUTER JOIN
                      ar_Tripulaciones ON ar_rutas.idjefe = ar_Tripulaciones.id
GROUP BY ar_rutas.idjefe, ar_rutas.ruta, ar_Tripulaciones.nombre

UNION ALL


SELECT     ar_Tripulaciones.nombre, ar_rutas.ruta, COUNT(ar_rutas.idescolta) AS cantidad
FROM         ar_rutas RIGHT OUTER JOIN
                      ar_Tripulaciones ON ar_rutas.idescolta = ar_Tripulaciones.id
GROUP BY ar_rutas.idescolta, ar_rutas.ruta, ar_Tripulaciones.nombre

UNION ALL


SELECT     ar_Tripulaciones.nombre, ar_rutas.ruta, COUNT(ar_rutas.idcajero) AS cantidad
FROM         ar_rutas RIGHT OUTER JOIN
                      ar_Tripulaciones ON ar_rutas.idcajero = ar_Tripulaciones.id
GROUP BY ar_rutas.idcajero, ar_rutas.ruta, ar_Tripulaciones.nombre


CREATE TABLE #temp_ruta
(
nombre varchar(50),
cantidad numeric(18,0)
)

INSERT INTO #temp_ruta

SELECT nombre, SUM(cantidad) as cantidad
FROM ar_temp
WHERE ruta= @ruta
Group by nombre,cantidad
ORDER BY nombre

SELECT nombre, SUM(cantidad) as cantidad
from #temp_ruta
Group by nombre, cantidad

Gracias por toda su ayuda