Reporte comparativo de Gastos por año

[Lsanders]

Buenas tardes Estimados

Estoy necesitando de su ayuda, tengo un query el cual me muestra los gastos mensuales, pero ahora se quiere ver los gastos por año para comparar un los gastos de un año con los del otro año.

Esta es la consulta que tengo

DECLARE @YEAR SMALLINT
 
SET @YEAR = (SELECT A.Year FROM dbo.OACP A WHERE A.Year='[%0]')

 
SELECT 
	P.[Cuenta],
	P.[Nombre],
	P.[CCosto],
	[1] as [Ene],
	[2] as [Feb],
	[3] as [Mar],
	[4] as [Abr],
	[5] as [May],
	[6] as [Jun],
	[7] as [Jul],
	[8] as [Ago],
	[9] as [Sep],
	[10] as [Oct],
	[11] as [Nov],
	[12] as [Dic]
 
FROM (
	SELECT
		T0.Account AS Cuenta,
		T1.AcctName AS Nombre,
		T2.PrcName AS CCosto,
		MONTH(T0.RefDate)'Month',
		SUM(T0.Debit-T0.Credit)'CargoAbono'
 
	FROM dbo.JDT1 T0
	INNER JOIN dbo.OACT T1 ON T1.AcctCode=T0.Account
	LEFT JOIN dbo.OPRC T2 ON T2.PrcCode=T0.ProfitCode
 
	WHERE YEAR(T0.RefDate)=@YEAR AND T1.GroupMask BETWEEN 6 AND 8
	GROUP BY T0.Account, T1.AcctName, T2.PrcName,MONTH(T0.RefDate)
) P
 
PIVOT (
	SUM(CargoAbono)
	FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) P
 
ORDER BY P.[Cuenta], P.[CCosto]

[Willy_Caldero]

Hola @Lsanders, esta facil el cambio, segun comentas necesitas 2 parametros, año1 y año2 (para comparar ambos):

DECLARE @YEAR1 SMALLINT
DECLARE @YEAR2 SMALLINT
 
SET @YEAR1 = (SELECT A.Year FROM dbo.OACP A WHERE A.Year='[%0]')
SET @YEAR2 = (SELECT A.Year FROM dbo.OACP A WHERE A.Year='[%1]')


 
SELECT 
	P.[Cuenta],
	P.[Nombre],
	P.[CCosto],
	[1] as [Año Inicial],
	[2] as [Año Final],
 
FROM (
	SELECT
		T0.Account AS Cuenta,
		T1.AcctName AS Nombre,
		T2.PrcName AS CCosto,
		YEAR(T0.RefDate)'Year',
		SUM(T0.Debit-T0.Credit)'CargoAbono'
 
	FROM dbo.JDT1 T0
	INNER JOIN dbo.OACT T1 ON T1.AcctCode=T0.Account
	LEFT JOIN dbo.OPRC T2 ON T2.PrcCode=T0.ProfitCode
 
	WHERE YEAR(T0.RefDate) In(@YEAR1, @YEAR1) AND T1.GroupMask BETWEEN 6 AND 8
	GROUP BY T0.Account, T1.AcctName, T2.PrcName,YEAR(T0.RefDate)
) P
 
PIVOT (
	SUM(CargoAbono)
	FOR [Year] IN ([1],[2])
) P
 
ORDER BY P.[Cuenta], P.[CCosto]

Para que te bases, ojo no lo probé.

Saludos,