Cambiar la posicion de un campo en sql dinamico

[felipe_loyolarodriguez]

Estimados

Tengo el siguiente query usando un pivot, funciona correctamente, el problema es que la columna total aparece al principio y no al final de los meses y al cliente no le gusta visualmente...

¿Alguno sabe como colocarla al final?

Se los dejo.

SET LANGUAGE Spanish
/********** VARIABLES DE FECHAS EN SAP **********/
DECLARE @TOP INT, @F_INI DATETIME, @F_FIN DATETIME
SET @TOP=(SELECT TOP 1 A.TransId FROM [dbo].[JDT1] A WHERE A.RefDate>='[%0]' AND A.RefDate<='[%1]')
SET @F_INI='[%0]'
SET @F_FIN='[%1]'
/********** TABLA DE MESES SEGUN LAS FECHAS ELEGIDAS **********/
DECLARE @MESES TABLE(Anio SMALLINT, Mes SMALLINT, Nombre_mes NVARCHAR(20))
INSERT INTO @MESES
SELECT DISTINCT YEAR(RefDate)'Anio', MONTH(RefDate)'Mes', DATENAME(MONTH, RefDate)'Nombre_mes'
FROM JDT1
WHERE YEAR(RefDate)=YEAR(@F_FIN) AND RefDate>=@F_INI AND RefDate<=@F_FIN
ORDER BY Anio, Mes
/********** TABLA PARA PIVOTEAR POR MES **********/
DECLARE @pvt_table NVARCHAR(MAX)
SELECT @pvt_table = COALESCE(@pvt_table + ',[' + A.Nombre_mes + ']', '[' + A.Nombre_mes + ']')
FROM @MESES A
/********** SQL DINAMICO **********/
DECLARE @Pvt NVARCHAR(MAX)
SET @Pvt = 
          N'
          SELECT *
          FROM ( 
                    SELECT B.CardCode AS Codigo, B.CardName AS "Nombre Cliente", SUM(B.Compras) AS Compras, B.Month AS Mes,
                    ISNULL((SELECT SUM(Y0.DocTotal) FROM OINV Y0 WHERE Y0.CardCode=B.CardCode AND Y0.DocDate>=(@INI) AND Y0.DocDate<=(@FIN)),0)-ISNULL((SELECT SUM(Y0.DocTotal) FROM ORIN Y0 WHERE Y0.CardCode=B.CardCode AND Y0.DocDate>=(@INI) AND Y0.DocDate<=(@FIN)),0) AS Total
                    FROM ( 
                              SELECT T0.CardCode AS CardCode, T0.CardName AS CardName, SUM(T0.DocTotal) AS Compras, DATENAME(MONTH, T0.DocDate) AS Month 
                              FROM OINV T0
                              WHERE T0.DocDate>=(@INI) AND T0.DocDate<=(@FIN)
                              GROUP BY T0.CardCode, T0.CardName, T0.DocDate
                              UNION ALL
                              SELECT T0.CardCode AS CardCode, T0.CardName AS CardName, -SUM(T0.DocTotal) AS Compras, DATENAME(MONTH, T0.DocDate) AS Month
                              FROM ORIN T0
                              WHERE T0.DocDate>=(@INI) AND T0.DocDate<=(@FIN)
                              GROUP BY T0.CardCode, T0.CardName, T0.DocDate
                    ) AS B
                    GROUP BY B.CardCode, B.CardName, B.Month
          ) AS A
          PIVOT (
                    SUM(Compras)
                    FOR Mes IN ('+ @pvt_table +')
          ) AS Pvt
          ORDER BY 1
          '
/********** EJECUCION **********/
EXEC sp_executesql @pvt, N'@pvt_table NVARCHAR(MAX), @INI DATETIME, @FIN DATETIME',@pvt_table, @F_INI, @F_FIN

Saludos

Felipe

[felipe_loyolarodriguez]

Lo metí a un crystal y asunto solucionado

Saludos