ALTER PROCEDURE [dbo].[sp_remInformeComparativo_Excel]
	(
	@periodo int =0,
	@mes int = 0,
	@fichaI	int=0,
	@fichaF	int=999999999,
	@unineg int=0,
	@dep int=0,
	@activo int=0,
	@tipoconcepto varchar(1)='',
	@idconcepto int=0,
	@tipoComparativo int = 1 -- 0 = Trimestral, 1 = Semestral, 2 = Anual
	)
AS	


declare @date date

Declare @DayOfMonth TinyInt Set @DayOfMonth = 1
Declare @Month TinyInt Set @Month = @mes
Declare @Year Integer Set @Year = @periodo
-- ------------------------------------
Select @date = DateAdd(day, @DayOfMonth - 1, 
          DateAdd(month, @Month - 1, 
              DateAdd(Year, @Year-1900, 0)))


DECLARE @periodo1 INT ,@periodo2 INT
DECLARE @mes1 INT ,@mes2 INT

IF @tipoComparativo = 0 -- Trimestral
	BEGIN
		select @periodo1=YEAR(DATEADD(MM, -2, @date)), @mes1=MONTH(DATEADD(MM, -2, @date))
		select @periodo2=YEAR(DATEADD(MM, -1, @date)), @mes2=MONTH(DATEADD(MM, -1, @date))

		--DROP TABLE #FICHAS3

		SELECT     T.ficha,periodo,mes
		INTO #FICHAS3
		FROM            remTrabajadores T
		WHERE  (T.periodo IN(@periodo1,@periodo2,@periodo)) AND (T.mes IN(@mes1,@mes2,@mes))
		AND (T.ficha between @fichaI AND @fichaF)
		AND (T.unidadNegocios=@unineg or @unineg=0)	
		AND (@activo=0 OR T.activo=@activo)
		AND (@dep=0 OR T.departamento=@dep)

		--DROP TABLE #A3

		SELECT ficha,[dbo].[remValorConcepto](@periodo1,@mes1,ficha,'R',@idconcepto) as col1,0 AS col2,0 as col3
		INTO #A3
		FROM            remTrabajadores T
		WHERE  (T.periodo=@periodo1) AND (T.mes=@mes1)
		AND (T.ficha between @fichaI AND @fichaF)
		AND (T.unidadNegocios=@unineg or @unineg=0)	
		AND (@activo=0 OR T.activo=@activo)
		AND (@dep=0 OR T.departamento=@dep)

		UNION ALL

		SELECT ficha,0 as col1,[dbo].[remValorConcepto](@periodo2,@mes2,ficha,'R',@idconcepto) AS col2,0 as col3
		FROM            remTrabajadores T
		WHERE  (T.periodo=@periodo2) AND (T.mes=@mes2)
		AND (T.ficha between @fichaI AND @fichaF)
		AND (T.unidadNegocios=@unineg or @unineg=0)	
		AND (@activo=0 OR T.activo=@activo)
		AND (@dep=0 OR T.departamento=@dep)

		UNION ALL

		SELECT ficha,0 as col1,0 AS col2, [dbo].[remValorConcepto](@periodo,@mes,ficha,'R',@idconcepto) as col3
		FROM            remTrabajadores T
		WHERE  (T.periodo=@periodo) AND (T.mes=@mes)
		AND (T.ficha between @fichaI AND @fichaF)
		AND (T.unidadNegocios=@unineg or @unineg=0)	
		AND (@activo=0 OR T.activo=@activo)
		AND (@dep=0 OR T.departamento=@dep)

		--DROP TABLE #TRABAJADORES3

		SELECT ficha,
		@periodo1 as P1,@mes1 AS M1,sum(col1) as V1,
		@periodo2 as P2,@mes2 AS M2,sum(col2) as V2,
		@periodo as P3,@mes AS M3,sum(col3) as V3
		INTO #TRABAJADORES3
		FROM #A3
		GROUP BY ficha

		--DROP TABLE #FINAL3

		SELECT ROW_NUMBER() OVER (PARTITION BY #TRABAJADORES3.ficha ORDER BY #TRABAJADORES3.ficha) AS numeroFila, #TRABAJADORES3.*, 
		(V1 + V2 + V3) / ((CASE WHEN V1 = 0 THEN 0 ELSE 1 END) + 
		(CASE WHEN  V2 = 0 THEN 0 ELSE 1 END) + 
		(CASE WHEN  V3 = 0 THEN 0 ELSE 1 END) +
		(CASE WHEN  V1+V2+V3 = 0 THEN 1 ELSE 0 END) 
		)
		 as PROMEDIO,
		T.rut, T.dv, T.extranjero, RTRIM(T.nombres) AS nombres, RTRIM(T.apePaterno) AS apePaterno, RTRIM(T.apeMaterno) 
								 AS apeMaterno,RTRIM(T.apePaterno) + ' ' + RTRIM(T.apeMaterno) 
								 + ' ' + RTRIM(T.nombres) AS nombrecompleto, remMovimientosRM.idConcepto,remConceptos.DESCRIPCION as nomconcepto,unidadnegocios as unineg,departamento as dep,T.cargo as cargo
		INTO #FINAL3
		FROM           remTrabajadores AS T INNER JOIN
								 remConceptos ON T.periodo = remConceptos.periodo AND T.mes = remConceptos.mes INNER JOIN
								 remMovimientosRM ON remConceptos.periodo = remMovimientosRM.periodo AND remConceptos.mes = remMovimientosRM.mes AND 
								 T.ficha = remMovimientosRM.ficha AND remConceptos.tipoConcepto = remMovimientosRM.tipoConcepto AND 
								 remConceptos.idConcepto = remMovimientosRM.idConcepto INNER JOIN
							 #TRABAJADORES3 ON T.ficha = #TRABAJADORES3.ficha
		WHERE   (remMovimientosRM.tipoConcepto=@tipoconcepto)
		AND (@idconcepto=0 OR remConceptos.idConcepto=@idconcepto)
		AND (@activo=0 OR T.activo=@activo)
		GROUP BY #TRABAJADORES3.ficha, #TRABAJADORES3.P1, #TRABAJADORES3.M1, #TRABAJADORES3.V1, #TRABAJADORES3.P2, #TRABAJADORES3.M2, #TRABAJADORES3.V2
		, #TRABAJADORES3.P3, #TRABAJADORES3.M3, #TRABAJADORES3.V3
		, T.rut, T.dv, T.extranjero, T.nombres, T.apePaterno, T.apeMaterno, remMovimientosRM.idConcepto, remConceptos.descripcion
		, T.unidadNegocios, T.departamento, T.cargo

		SELECT ficha, FORMAT(CONVERT(int, rut), '##,###,###', 'es-ES') + '-' + RTRIM(dv) AS [RUT], nombrecompleto, RTRIM(ISNULL(un.descripcion, '')) AS [U. Negocio], ISNULL(dpto.descripcion, '') AS dep, ISNULL(car.descripcion, '') AS [Cargo], P1, M1, V1, P2, M2, V2, P3, M3, V3,
			  PROMEDIO, V3 - V1 AS Diferencia, idConcepto  FROM #FINAL3 LEFT JOIN conUnidadNegocio un ON unineg = un.idunineg LEFT JOIN remCargos car ON cargo = car.idcargo LEFT JOIN remDepartamentos dpto ON dpto.idDepartamento = dep WHERE numeroFila = 1

END

IF @tipoComparativo = 1  --Semestral
	BEGIN
		DECLARE @periodo3 INT ,@periodo4 INT, @periodo5 INT
		DECLARE @mes3 INT ,@mes4 INT, @mes5 INT

		select @periodo1=YEAR(DATEADD(MM, -5, @date)), @mes1=MONTH(DATEADD(MM, -5, @date))
		select @periodo2=YEAR(DATEADD(MM, -4, @date)), @mes2=MONTH(DATEADD(MM, -4, @date))
		select @periodo3=YEAR(DATEADD(MM, -3, @date)), @mes3=MONTH(DATEADD(MM, -3, @date))
		select @periodo4=YEAR(DATEADD(MM, -2, @date)), @mes4=MONTH(DATEADD(MM, -2, @date))
		select @periodo5=YEAR(DATEADD(MM, -1, @date)), @mes5=MONTH(DATEADD(MM, -1, @date))

		--DROP TABLE #FICHAS6

		SELECT     T.ficha,periodo,mes
		INTO #FICHAS6
		FROM            remTrabajadores T
		WHERE  (T.periodo IN(@periodo1,@periodo2,@periodo3,@periodo4,@periodo5,@periodo)) AND (T.mes IN(@mes1,@mes2,@mes3,@mes4,@mes5,@mes))
		AND (T.ficha between @fichaI AND @fichaF)
		AND (T.unidadNegocios=@unineg or @unineg=0)	
		AND (@activo=0 OR T.activo=@activo)
		AND (@dep=0 OR T.departamento=@dep)

		--DROP TABLE #A6

		SELECT ficha,[dbo].[remValorConcepto](@periodo1,@mes1,ficha,'R',@idconcepto) as col1,0 AS col2,0 as col3, 0 as col4, 0 AS col5,0 as col6
		INTO #A6
		FROM            remTrabajadores T
		WHERE  (T.periodo=@periodo1) AND (T.mes=@mes1)
		AND (T.ficha between @fichaI AND @fichaF)
		AND (T.unidadNegocios=@unineg or @unineg=0)	
		AND (@activo=0 OR T.activo=@activo)
		AND (@dep=0 OR T.departamento=@dep)

		UNION ALL

		SELECT ficha,0 as col1,[dbo].[remValorConcepto](@periodo2,@mes2,ficha,'R',@idconcepto) AS col2,0 as col3, 0 as col4, 0 AS col5,0 as col6
		FROM            remTrabajadores T
		WHERE  (T.periodo=@periodo2) AND (T.mes=@mes2)
		AND (T.ficha between @fichaI AND @fichaF)
		AND (T.unidadNegocios=@unineg or @unineg=0)	
		AND (@activo=0 OR T.activo=@activo)
		AND (@dep=0 OR T.departamento=@dep)

		UNION ALL

		SELECT ficha,0 as col1,0 AS col2,[dbo].[remValorConcepto](@periodo3,@mes3,ficha,'R',@idconcepto) as col3, 0 as col4, 0 AS col5,0 as col6
		FROM            remTrabajadores T
		WHERE  (T.periodo=@periodo3) AND (T.mes=@mes3)
		AND (T.ficha between @fichaI AND @fichaF)
		AND (T.unidadNegocios=@unineg or @unineg=0)	
		AND (@activo=0 OR T.activo=@activo)
		AND (@dep=0 OR T.departamento=@dep)

		UNION ALL

		SELECT ficha,0 as col1,0 AS col2,0 as col3,[dbo].[remValorConcepto](@periodo4,@mes4,ficha,'R',@idconcepto) as col4, 0 AS col5,0 as col6
		FROM            remTrabajadores T
		WHERE  (T.periodo=@periodo4) AND (T.mes=@mes4)
		AND (T.ficha between @fichaI AND @fichaF)
		AND (T.unidadNegocios=@unineg or @unineg=0)	
		AND (@activo=0 OR T.activo=@activo)
		AND (@dep=0 OR T.departamento=@dep)

		UNION ALL 

		SELECT ficha,0 as col1,0 AS col2,0 as col3, 0 as col4, [dbo].[remValorConcepto](@periodo5,@mes5,ficha,'R',@idconcepto) AS col5,0 as col6
		FROM            remTrabajadores T
		WHERE  (T.periodo=@periodo5) AND (T.mes=@mes5)
		AND (T.ficha between @fichaI AND @fichaF)
		AND (T.unidadNegocios=@unineg or @unineg=0)	
		AND (@activo=0 OR T.activo=@activo)
		AND (@dep=0 OR T.departamento=@dep)

		UNION ALL

		SELECT ficha,0 as col1,0 AS col2,0 as col3, 0 as col4, 0 AS col5,[dbo].[remValorConcepto](@periodo,@mes,ficha,'R',@idconcepto) as col6
		FROM            remTrabajadores T
		WHERE  (T.periodo=@periodo) AND (T.mes=@mes)
		AND (T.ficha between @fichaI AND @fichaF)
		AND (T.unidadNegocios=@unineg or @unineg=0)	
		AND (@activo=0 OR T.activo=@activo)
		AND (@dep=0 OR T.departamento=@dep)

		--DROP TABLE #TRABAJADORES6

		SELECT ficha,
		@periodo1 as P1,@mes1 AS M1,sum(col1) as V1,
		@periodo2 as P2,@mes2 AS M2,sum(col2) as V2,
		@periodo3 as P3,@mes3 AS M3,sum(col3) as V3,
		@periodo4 as P4,@mes4 AS M4,sum(col4) as V4,
		@periodo5 as P5,@mes5 AS M5,sum(col5) as V5,
		@periodo as P6,@mes AS M6,sum(col6) as V6
		INTO #TRABAJADORES6
		FROM #A6
		GROUP BY ficha

		--DROP TABLE #FINAL6

		SELECT ROW_NUMBER() OVER (PARTITION BY #TRABAJADORES6.ficha ORDER BY #TRABAJADORES6.ficha) AS numeroFila, #TRABAJADORES6.*, 
		(V1 + V2 + V3 + V4 + V5 + V6) / ((CASE WHEN V1 = 0 THEN 0 ELSE 1 END) + 
		(CASE WHEN  V2 = 0 THEN 0 ELSE 1 END) + 
		(CASE WHEN  V3 = 0 THEN 0 ELSE 1 END) +
		(CASE WHEN  V4 = 0 THEN 0 ELSE 1 END) +
		(CASE WHEN  V5 = 0 THEN 0 ELSE 1 END) +
		(CASE WHEN  V6 = 0 THEN 0 ELSE 1 END) +
		(CASE WHEN  V1+V2+V3+V4+V5+V6 = 0 THEN 1 ELSE 0 END) 
		)
		 as PROMEDIO,
		T.rut, T.dv, T.extranjero, RTRIM(T.nombres) AS nombres, RTRIM(T.apePaterno) AS apePaterno, RTRIM(T.apeMaterno) 
								 AS apeMaterno,RTRIM(T.apePaterno) + ' ' + RTRIM(T.apeMaterno) 
								 + ' ' + RTRIM(T.nombres) AS nombrecompleto, remMovimientosRM.idConcepto,remConceptos.DESCRIPCION as nomconcepto,unidadnegocios as unineg,departamento as dep,T.cargo as cargo
		INTO #FINAL6
		FROM           remTrabajadores AS T INNER JOIN
								 remConceptos ON T.periodo = remConceptos.periodo AND T.mes = remConceptos.mes INNER JOIN
								 remMovimientosRM ON remConceptos.periodo = remMovimientosRM.periodo AND remConceptos.mes = remMovimientosRM.mes AND 
								 T.ficha = remMovimientosRM.ficha AND remConceptos.tipoConcepto = remMovimientosRM.tipoConcepto AND 
								 remConceptos.idConcepto = remMovimientosRM.idConcepto INNER JOIN
							 #TRABAJADORES6 ON T.ficha = #TRABAJADORES6.ficha
		WHERE   (remMovimientosRM.tipoConcepto=@tipoconcepto)
		AND (@idconcepto=0 OR remConceptos.idConcepto=@idconcepto)
		AND (@activo=0 OR T.activo=@activo)
		GROUP BY #TRABAJADORES6.ficha, #TRABAJADORES6.P1, #TRABAJADORES6.M1, #TRABAJADORES6.V1, #TRABAJADORES6.P2, #TRABAJADORES6.M2, #TRABAJADORES6.V2
		, #TRABAJADORES6.P3, #TRABAJADORES6.M3, #TRABAJADORES6.V3, #TRABAJADORES6.P4, #TRABAJADORES6.M4, #TRABAJADORES6.V4
		, #TRABAJADORES6.P5, #TRABAJADORES6.M5, #TRABAJADORES6.V5, #TRABAJADORES6.P6, #TRABAJADORES6.M6, #TRABAJADORES6.V6
		, T.rut, T.dv, T.extranjero, T.nombres, T.apePaterno, T.apeMaterno, remMovimientosRM.idConcepto, remConceptos.descripcion
		, T.unidadNegocios, T.departamento, T.cargo


		SELECT ficha, FORMAT(CONVERT(int, rut), '##,###,###', 'es-ES') + '-' + RTRIM(dv) AS [RUT], nombrecompleto, RTRIM(ISNULL(un.descripcion, '')) AS [U. Negocio], ISNULL(dpto.descripcion, '') AS dep, ISNULL(car.descripcion, '') AS [Cargo], P1, M1, V1, P2, M2, V2, P3, M3, V3, P4, M4, V4, P5, M5, V5, P6, M6, V6,
			  PROMEDIO, V6 - V1 AS Diferencia, idConcepto  FROM #FINAL6 LEFT JOIN conUnidadNegocio un ON unineg = un.idunineg LEFT JOIN remCargos car ON cargo = car.idcargo LEFT JOIN remDepartamentos dpto ON dpto.idDepartamento = dep WHERE numeroFila = 1
END

IF @tipoComparativo = 2 -- Anual
	BEGIN
		DECLARE @periodo6 INT ,@periodo7 INT, @periodo8 INT, @periodo9 INT ,@periodo10 INT, @periodo11 INT
		DECLARE @mes6 INT ,@mes7 INT, @mes8 INT, @mes9 INT ,@mes10 INT, @mes11 INT

		select @periodo1=YEAR(DATEADD(MM, -11, @date)), @mes1=MONTH(DATEADD(MM, -11, @date))
		select @periodo2=YEAR(DATEADD(MM, -10, @date)), @mes2=MONTH(DATEADD(MM, -10, @date))
		select @periodo3=YEAR(DATEADD(MM, -9, @date)), @mes3=MONTH(DATEADD(MM, -9, @date))
		select @periodo4=YEAR(DATEADD(MM, -8, @date)), @mes4=MONTH(DATEADD(MM, -8, @date))
		select @periodo5=YEAR(DATEADD(MM, -7, @date)), @mes5=MONTH(DATEADD(MM, -7, @date))
		select @periodo6=YEAR(DATEADD(MM, -6, @date)), @mes6=MONTH(DATEADD(MM, -6, @date))
		select @periodo7=YEAR(DATEADD(MM, -5, @date)), @mes7=MONTH(DATEADD(MM, -5, @date))
		select @periodo8=YEAR(DATEADD(MM, -4, @date)), @mes8=MONTH(DATEADD(MM, -4, @date))
		select @periodo9=YEAR(DATEADD(MM, -3, @date)), @mes9=MONTH(DATEADD(MM, -3, @date))
		select @periodo10=YEAR(DATEADD(MM, -2, @date)), @mes10=MONTH(DATEADD(MM, -2, @date))
		select @periodo11=YEAR(DATEADD(MM, -1, @date)), @mes11=MONTH(DATEADD(MM, -1, @date))

		--DROP TABLE #FICHAS12

		SELECT     T.ficha,periodo,mes
		INTO #FICHAS12
		FROM            remTrabajadores T
		WHERE  (T.periodo IN(@periodo1,@periodo2,@periodo3,@periodo4,@periodo5,@periodo6,@periodo7,@periodo8,@periodo9,@periodo10,@periodo11,@periodo)) AND (T.mes IN(@mes1,@mes2,@mes3,@mes4,@mes5,@mes6,@mes7,@mes8,@mes9,@mes10,@mes11,@mes))
		AND (T.ficha between @fichaI AND @fichaF)
		AND (T.unidadNegocios=@unineg or @unineg=0)	
		AND (@activo=0 OR T.activo=@activo)
		AND (@dep=0 OR T.departamento=@dep)

		--DROP TABLE #A12

		SELECT ficha,[dbo].[remValorConcepto](@periodo1,@mes1,ficha,'R',@idconcepto) as col1,0 AS col2,0 as col3, 0 as col4, 0 AS col5, 0 as col6, 0 as col7, 0 as col8, 0 as col9, 0 as col10, 0 as col11, 0 as col12
		INTO #A12
		FROM            remTrabajadores T
		WHERE  (T.periodo=@periodo1) AND (T.mes=@mes1)
		AND (T.ficha between @fichaI AND @fichaF)
		AND (T.unidadNegocios=@unineg or @unineg=0)	
		AND (@activo=0 OR T.activo=@activo)
		AND (@dep=0 OR T.departamento=@dep)

		UNION ALL

		SELECT ficha,0 as col1,[dbo].[remValorConcepto](@periodo2,@mes2,ficha,'R',@idconcepto) AS col2,0 as col3, 0 as col4, 0 AS col5, 0 as col6, 0 as col7, 0 as col8, 0 as col9, 0 as col10, 0 as col11, 0 as col12
		FROM            remTrabajadores T
		WHERE  (T.periodo=@periodo2) AND (T.mes=@mes2)
		AND (T.ficha between @fichaI AND @fichaF)
		AND (T.unidadNegocios=@unineg or @unineg=0)	
		AND (@activo=0 OR T.activo=@activo)
		AND (@dep=0 OR T.departamento=@dep)

		UNION ALL

		SELECT ficha,0 as col1,0 AS col2,[dbo].[remValorConcepto](@periodo3,@mes3,ficha,'R',@idconcepto) as col3, 0 as col4, 0 AS col5, 0 as col6, 0 as col7, 0 as col8, 0 as col9, 0 as col10, 0 as col11, 0 as col12
		FROM            remTrabajadores T
		WHERE  (T.periodo=@periodo3) AND (T.mes=@mes3)
		AND (T.ficha between @fichaI AND @fichaF)
		AND (T.unidadNegocios=@unineg or @unineg=0)	
		AND (@activo=0 OR T.activo=@activo)
		AND (@dep=0 OR T.departamento=@dep)

		UNION ALL

		SELECT ficha,0 as col1,0 AS col2,0 as col3,[dbo].[remValorConcepto](@periodo4,@mes4,ficha,'R',@idconcepto) as col4, 0 AS col5, 0 as col6, 0 as col7, 0 as col8, 0 as col9, 0 as col10, 0 as col11, 0 as col12
		FROM            remTrabajadores T
		WHERE  (T.periodo=@periodo4) AND (T.mes=@mes4)
		AND (T.ficha between @fichaI AND @fichaF)
		AND (T.unidadNegocios=@unineg or @unineg=0)	
		AND (@activo=0 OR T.activo=@activo)
		AND (@dep=0 OR T.departamento=@dep)

		UNION ALL 

		SELECT ficha,0 as col1,0 AS col2,0 as col3, 0 as col4, [dbo].[remValorConcepto](@periodo5,@mes5,ficha,'R',@idconcepto) AS col5, 0 as col6, 0 as col7, 0 as col8, 0 as col9, 0 as col10, 0 as col11, 0 as col12
		FROM            remTrabajadores T
		WHERE  (T.periodo=@periodo5) AND (T.mes=@mes5)
		AND (T.ficha between @fichaI AND @fichaF)
		AND (T.unidadNegocios=@unineg or @unineg=0)	
		AND (@activo=0 OR T.activo=@activo)
		AND (@dep=0 OR T.departamento=@dep)

		UNION ALL

		SELECT ficha,0 as col1,0 AS col2,0 as col3, 0 as col4, 0 AS col5,[dbo].[remValorConcepto](@periodo6,@mes6,ficha,'R',@idconcepto) as col6, 0 as col7, 0 as col8, 0 as col9, 0 as col10, 0 as col11, 0 as col12
		FROM            remTrabajadores T
		WHERE  (T.periodo=@periodo6) AND (T.mes=@mes6)
		AND (T.ficha between @fichaI AND @fichaF)
		AND (T.unidadNegocios=@unineg or @unineg=0)	
		AND (@activo=0 OR T.activo=@activo)
		AND (@dep=0 OR T.departamento=@dep)

		UNION ALL

		SELECT ficha,0 as col1,0 AS col2,0 as col3, 0 as col4, 0 AS col5,0 as col6,[dbo].[remValorConcepto](@periodo7,@mes7,ficha,'R',@idconcepto) as col7, 0 as col8, 0 as col9, 0 as col10, 0 as col11, 0 as col12
		FROM            remTrabajadores T
		WHERE  (T.periodo=@periodo7) AND (T.mes=@mes7)
		AND (T.ficha between @fichaI AND @fichaF)
		AND (T.unidadNegocios=@unineg or @unineg=0)	
		AND (@activo=0 OR T.activo=@activo)
		AND (@dep=0 OR T.departamento=@dep)

		UNION ALL

		SELECT ficha,0 as col1,0 AS col2,0 as col3, 0 as col4, 0 AS col5,0 as col6, 0 as col7,[dbo].[remValorConcepto](@periodo8,@mes8,ficha,'R',@idconcepto) as col8, 0 as col9, 0 as col10, 0 as col11, 0 as col12
		FROM            remTrabajadores T
		WHERE  (T.periodo=@periodo8) AND (T.mes=@mes8)
		AND (T.ficha between @fichaI AND @fichaF)
		AND (T.unidadNegocios=@unineg or @unineg=0)	
		AND (@activo=0 OR T.activo=@activo)
		AND (@dep=0 OR T.departamento=@dep)

		UNION ALL

		SELECT ficha,0 as col1,0 AS col2,0 as col3, 0 as col4, 0 AS col5,0 as col6, 0 as col7, 0 as col8, [dbo].[remValorConcepto](@periodo9,@mes9,ficha,'R',@idconcepto) as col9, 0 as col10, 0 as col11, 0 as col12
		FROM            remTrabajadores T
		WHERE  (T.periodo=@periodo9) AND (T.mes=@mes9)
		AND (T.ficha between @fichaI AND @fichaF)
		AND (T.unidadNegocios=@unineg or @unineg=0)	
		AND (@activo=0 OR T.activo=@activo)
		AND (@dep=0 OR T.departamento=@dep)

		UNION ALL

		SELECT ficha,0 as col1,0 AS col2,0 as col3, 0 as col4, 0 AS col5,0 as col6, 0 as col7, 0 as col8, 0 as col9, [dbo].[remValorConcepto](@periodo10,@mes10,ficha,'R',@idconcepto) as col10, 0 as col11, 0 as col12
		FROM            remTrabajadores T
		WHERE  (T.periodo=@periodo10) AND (T.mes=@mes10)
		AND (T.ficha between @fichaI AND @fichaF)
		AND (T.unidadNegocios=@unineg or @unineg=0)	
		AND (@activo=0 OR T.activo=@activo)
		AND (@dep=0 OR T.departamento=@dep)

		UNION ALL

		SELECT ficha,0 as col1,0 AS col2,0 as col3, 0 as col4, 0 AS col5,0 as col6, 0 as col7, 0 as col8, 0 as col9, 0 as col10, [dbo].[remValorConcepto](@periodo11,@mes11,ficha,'R',@idconcepto) as col11, 0 as col12
		FROM            remTrabajadores T
		WHERE  (T.periodo=@periodo11) AND (T.mes=@mes11)
		AND (T.ficha between @fichaI AND @fichaF)
		AND (T.unidadNegocios=@unineg or @unineg=0)	
		AND (@activo=0 OR T.activo=@activo)
		AND (@dep=0 OR T.departamento=@dep)

		UNION ALL

		SELECT ficha,0 as col1,0 AS col2,0 as col3, 0 as col4, 0 AS col5,0 as col6, 0 as col7, 0 as col8, 0 as col9, 0 as col10, 0 as col11, [dbo].[remValorConcepto](@periodo,@mes,ficha,'R',@idconcepto) as col12
		FROM            remTrabajadores T
		WHERE  (T.periodo=@periodo) AND (T.mes=@mes)
		AND (T.ficha between @fichaI AND @fichaF)
		AND (T.unidadNegocios=@unineg or @unineg=0)	
		AND (@activo=0 OR T.activo=@activo)
		AND (@dep=0 OR T.departamento=@dep)

		--DROP TABLE #TRABAJADORES12

		SELECT ficha,
		@periodo1 as P1,@mes1 AS M1,sum(col1) as V1,
		@periodo2 as P2,@mes2 AS M2,sum(col2) as V2,
		@periodo3 as P3,@mes3 AS M3,sum(col3) as V3,
		@periodo4 as P4,@mes4 AS M4,sum(col4) as V4,
		@periodo5 as P5,@mes5 AS M5,sum(col5) as V5,
		@periodo6 as P6,@mes6 AS M6,sum(col6) as V6,
		@periodo7 as P7,@mes7 AS M7,sum(col7) as V7,
		@periodo8 as P8,@mes8 AS M8,sum(col8) as V8,
		@periodo9 as P9,@mes9 AS M9,sum(col9) as V9,
		@periodo10 as P10,@mes10 AS M10,sum(col10) as V10,
		@periodo11 as P11,@mes11 AS M11,sum(col11) as V11,
		@periodo as P12,@mes AS M12,sum(col12) as V12
		INTO #TRABAJADORES12
		FROM #A12
		GROUP BY ficha

		--DROP TABLE #FINAL12

		SELECT ROW_NUMBER() OVER (PARTITION BY #TRABAJADORES12.ficha ORDER BY #TRABAJADORES12.ficha) AS numeroFila, #TRABAJADORES12.*, 
		(V1 + V2 + V3 + V4 + V5 + V6 + V7 + V8 + V9 + V10 + V11 + V12) / ((CASE WHEN V1 = 0 THEN 0 ELSE 1 END) + 
		(CASE WHEN  V2 = 0 THEN 0 ELSE 1 END) + 
		(CASE WHEN  V3 = 0 THEN 0 ELSE 1 END) +
		(CASE WHEN  V4 = 0 THEN 0 ELSE 1 END) +
		(CASE WHEN  V5 = 0 THEN 0 ELSE 1 END) +
		(CASE WHEN  V6 = 0 THEN 0 ELSE 1 END) +
		(CASE WHEN  V7 = 0 THEN 0 ELSE 1 END) +
		(CASE WHEN  V8 = 0 THEN 0 ELSE 1 END) +
		(CASE WHEN  V9 = 0 THEN 0 ELSE 1 END) +
		(CASE WHEN  V10 = 0 THEN 0 ELSE 1 END) +
		(CASE WHEN  V11 = 0 THEN 0 ELSE 1 END) +
		(CASE WHEN  V12 = 0 THEN 0 ELSE 1 END) +
		(CASE WHEN  V1+V2+V3+V4+V5+V6+V7+V8+V9+V10+V11+V12 = 0 THEN 1 ELSE 0 END) 
		)
		 as PROMEDIO,
		T.rut, T.dv, T.extranjero, RTRIM(T.nombres) AS nombres, RTRIM(T.apePaterno) AS apePaterno, RTRIM(T.apeMaterno) 
								 AS apeMaterno,RTRIM(T.apePaterno) + ' ' + RTRIM(T.apeMaterno) 
								 + ' ' + RTRIM(T.nombres) AS nombrecompleto, remMovimientosRM.idConcepto,remConceptos.DESCRIPCION as nomconcepto,unidadnegocios as unineg,departamento as dep,T.cargo as cargo
		INTO #FINAL12
		FROM           remTrabajadores AS T INNER JOIN
								 remConceptos ON T.periodo = remConceptos.periodo AND T.mes = remConceptos.mes INNER JOIN
								 remMovimientosRM ON remConceptos.periodo = remMovimientosRM.periodo AND remConceptos.mes = remMovimientosRM.mes AND 
								 T.ficha = remMovimientosRM.ficha AND remConceptos.tipoConcepto = remMovimientosRM.tipoConcepto AND 
								 remConceptos.idConcepto = remMovimientosRM.idConcepto INNER JOIN
							 #TRABAJADORES12 ON T.ficha = #TRABAJADORES12.ficha
		WHERE   (remMovimientosRM.tipoConcepto=@tipoconcepto)
		AND (@idconcepto=0 OR remConceptos.idConcepto=@idconcepto)
		AND (@activo=0 OR T.activo=@activo)
		AND T.periodo = @periodo
		GROUP BY #TRABAJADORES12.ficha, #TRABAJADORES12.P1, #TRABAJADORES12.M1, #TRABAJADORES12.V1, #TRABAJADORES12.P2, #TRABAJADORES12.M2, #TRABAJADORES12.V2
		, #TRABAJADORES12.P3, #TRABAJADORES12.M3, #TRABAJADORES12.V3, #TRABAJADORES12.P4, #TRABAJADORES12.M4, #TRABAJADORES12.V4
		, #TRABAJADORES12.P5, #TRABAJADORES12.M5, #TRABAJADORES12.V5, #TRABAJADORES12.P6, #TRABAJADORES12.M6, #TRABAJADORES12.V6
		, #TRABAJADORES12.P7, #TRABAJADORES12.M7, #TRABAJADORES12.V7, #TRABAJADORES12.P8, #TRABAJADORES12.M8, #TRABAJADORES12.V8
		, #TRABAJADORES12.P9, #TRABAJADORES12.M9, #TRABAJADORES12.V9, #TRABAJADORES12.P10, #TRABAJADORES12.M10, #TRABAJADORES12.V10
		, #TRABAJADORES12.P11, #TRABAJADORES12.M11, #TRABAJADORES12.V11, #TRABAJADORES12.P12, #TRABAJADORES12.M12, #TRABAJADORES12.V12
		, T.rut, T.dv, T.extranjero, T.nombres, T.apePaterno, T.apeMaterno, remMovimientosRM.idConcepto, remConceptos.descripcion
		, T.unidadNegocios, T.departamento, T.cargo


		SELECT ficha, FORMAT(CONVERT(int, rut), '##,###,###', 'es-ES') + '-' + RTRIM(dv) AS [RUT], nombrecompleto, RTRIM(ISNULL(un.descripcion, '')) AS [U. Negocio], ISNULL(dpto.descripcion, '') AS dep, ISNULL(car.descripcion, '') AS [Cargo], P1, M1, V1, P2, M2, V2, P3, M3, V3, P4, M4, V4, P5, M5, V5, P6, M6, V6, P7, M7, V7, P8, M8, V8, P9, M9, V9, P10, M10, V10, P11, M11, V11, P12, M12, V12,
			  PROMEDIO, V12 - V1 AS Diferencia, idConcepto  FROM #FINAL12 LEFT JOIN conUnidadNegocio un ON unineg = un.idunineg LEFT JOIN remCargos car ON cargo = car.idcargo LEFT JOIN remDepartamentos dpto ON dpto.idDepartamento = dep WHERE numeroFila = 1
END