Código SQL:
Ver originalSELECT md_usuario,nombre,isnull(turno,'') turno,nombre_sup,ctes0001 AS [a],ctes0002 AS [b],ctes0003 AS [c],
ctes0004 AS [d],ctes0005 AS [e],ctes0006 AS [f],ctes0007 AS [g],ctesreferi2 AS [h],isnull(tot_llamadas,0) tot_llamadas,isnull(tot_contac,0) tot_contac, isnull(tot_efect,0) tot_efect,isnull(tot_ventas,0) tot_ventas FROM
(SELECT a.md_usuario,a.nombre,isnull(aa.turno,'') turno,aa.nombre_sup,b.plaza,isnull(b.tot_llamadas,0) tot_llamadas,isnull(c.tot_contac,0) tot_contac, isnull(e.tot_efect,0) tot_efect,isnull(d.tot_ventas,0) tot_ventas FROM(
(SELECT DISTINCT a.md_usuario, b.nombre+' '+b.paterno+' '+b.materno AS nombre
FROM amex_2010.dbo.intentos a, prod.rh.usuarios b WHERE a.md_usuario=b.idUSUARIO AND b.idempresa IN (5,6) AND (a.MD_FECHA_CAPTURA BETWEEN CONVERT(datetime,'01/08/2010',103) AND CONVERT(datetime,'31/08/2010',103)+1)) a
LEFT OUTER JOIN
(SELECT DISTINCT a.idsupervisor,a.turno,a.idusuario, b.nombre+' '+b.paterno+' '+b.materno AS nombre_sup
FROM amex_2010.dbo.ctrl_rvt a, prod.rh.usuarios b WHERE a.idsupervisor=b.idUSUARIO AND b.idempresa IN (5,6) AND (fecha BETWEEN CONVERT(datetime,'01/08/2010',103) AND CONVERT(datetime,'31/08/2010',103)+1)) aa
ON a.md_usuario=aa.idusuario
LEFT OUTER JOIN
(SELECT md_usuario,plaza,COUNT(1) AS tot_llamadas FROM amex_2010.dbo.intentos WHERE(MD_FECHA_CAPTURA BETWEEN CONVERT(datetime,'01/08/2010',103) AND CONVERT(datetime,'31/08/2010',103)+1) GROUP BY plaza,md_usuario) b
ON a.md_usuario=b.md_usuario
LEFT OUTER JOIN
(SELECT md_usuario,plaza,COUNT(1) AS tot_contac FROM amex_2010.dbo.intentos
WHERE md_status IN ('05A','70X','99','03M','03O','03N','03G','03J','03I','03L','05G','02M','02E','02C','02B','02F','02D','02L','02A','02H','02J','02N','02I','04J','04F','04D','04I','04H','04K','01A','01B','01C','01D','01E','01F','01G','01H','01I')
AND (MD_FECHA_CAPTURA BETWEEN CONVERT(datetime,'01/08/2010',103) AND CONVERT(datetime,'31/08/2010',103)+1)
GROUP BY plaza,md_usuario )c
ON a.md_usuario=c.md_usuario AND b.plaza=c.plaza
LEFT OUTER JOIN
(SELECT md_usuario,plaza,COUNT(1) AS tot_efect FROM amex_2010.dbo.intentos
WHERE md_status IN ('01G','01E','01F','01D','01B','01C','01A','01H','01I','03O','02E','02F','02J','02B','02D','02C','02M','02H','02A','02L','02I','03G','03N','02F','05G','03M','03J','03I','04K','04H','04I' )
AND (MD_FECHA_CAPTURA BETWEEN CONVERT(datetime,'01/08/2010',103) AND CONVERT(datetime,'31/08/2010',103)+1)
GROUP BY plaza,md_usuario )e
ON a.md_usuario=e.md_usuario AND b.plaza=e.plaza
LEFT OUTER JOIN
(SELECT md_usuario,md_tabla,COUNT(1) AS tot_ventas FROM amex_2010.dbo.VENTAS WHERE (MD_FECHA_CAPTURA BETWEEN CONVERT(datetime,'01/08/2010',103) AND CONVERT(datetime,'31/08/2010',103)+1) GROUP BY md_tabla,md_usuario) d
ON a.md_usuario=d.md_usuario AND b.plaza=d.md_tabla)
) x pivot(COUNT(plaza) FOR plaza IN ([ctes0001],[ctes0002],[ctes0003],[ctes0004],[ctes0005],[ctes0006],[ctes0007],[ctesreferi2])) AS y ORDER BY md_usuario,nombre