USE [bdGestiondeAsuntos]
GO
/****** Object:  StoredProcedure [dbo].[spr_ReportexEstadosDesglosadosCG]    Script Date: 08/10/2012 10:45:12 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER        PROCEDURE [dbo].[spr_ReportexEstadosDesglosadosCG]
/*------------------------------------------------------------------------------------------------------------
parametros
------------------------------------------------------------------------------------------------------------*/

@idClasificacion		int,
@desde					datetime,
@hasta					datetime,
@idDependencia			int,
@Municipio				int,
@idEvento				int
AS
SELECT     dbo.tbDependencia.descripcionDependencia, 
[1] as recibido,
[2] as [en proceso],
 --0 as Terminado, 
[5] as [Sin Estado],
[6] as [Agotamiento de Instancia Edo],
[7] as [Conclusión Negativa], 
[8] as [Conclusión por información], 
[9] as [Conclusión Positiva],
[11] as [Falta de interés del ciudadano],
[1]+[2]+/*[4]+*/[5]+[6]+[7]+[8]+[9]+[11] as [Total],
isnull((([5]+[6]+[7]+[8]+[9]+[11])*100)/nullif(([1]+[2]+/*[4]+*/[5]+[6]+[7]+[8]+[9]+[11]),0),0)as porcentaje
FROM
(SELECT     ta.idDependencia, ta.idEstatus
FROM         dbo.tbAsuntoCiudadano AS ta INNER JOIN
                      dbo.tbAsuntos AS a ON ta.idAsunto = a.idAsunto INNER JOIN
                      dbo.tblLocalidad ON a.intIDLocalidad = dbo.tblLocalidad.intIDLocalidad
WHERE     (CAST(FLOOR( CAST(a.fechaRecepcion AS FLOAT ) )AS DATETIME) >= CONVERT(DATETIME, @desde, 103) OR @desde = '01/01/1900') AND 
		  (CAST(FLOOR( CAST(0a.fechaRecepcion AS FLOAT ) )AS DATETIME) <= CONVERT(DATETIME, @hasta, 103) OR @hasta = '01/01/1900') AND 
	      (ta.idDependencia = @idDependencia OR @idDependencia = 0 ) AND 
		  (a.idClasificacion = 2 OR a.idClasificacion = 4) AND 
		  (dbo.tblLocalidad.intID_tblMunicipio = @Municipio OR @Municipio = 0)AND 
		  (ta.idEvento = @idEvento OR @idEvento = 0)AND (ta.eliminado != 1)) ac

pivot
(count(ac.idEstatus) for ac.idEstatus in ([1],[2],/*[4],*/[5],[6],[7],[8],[9],[11]))
as pvt
 INNER JOIN
  dbo.tbDependencia ON pvt.idDependencia = dbo.tbDependencia.idDependencia
order by total desc