on 11-08-2011 1:27 AM
a ver si alguien puede ayudarme, quiero hacer un query de tal manera que me aparezca el total de las compras hechas a los proveedores por meses.
tengo esto, pero no me convence para nada utilizando el pivot, a ver si alguien puede ayudarme.
select proveedor, [1] Ene, [2] Feb, [3] Mar, [4] Abr, [5] May, [6] Jun,
[7] Jul, [8] Ago, [9] Sep, [10] Oct, [11] Nov, [12] Dic
from(
SELECT T2.[CardCode] as proveedor,T2.CARDNAME,T2.ADDRESS,T2.ZIPCODE,T2.CITY, T2.BLOCK,T2.STATE1,T2.COUNTRY,T0.[DocTotal]AS 'TOTAL',T0.[VatSum],t0.paidtodate,T0.[DocTotalFC],
T0.[VatSumFC],MONTH(T0.DOCDATE) AS MES
FROM Opch T0
INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode
WHERE T0.DOCENTRY NOT IN(SELECT DISTINCT(Opch.DOCENTRY)
FROM OPCH
INNER JOIN PCH1 ON OPCH.DOCENTRY=PCH1.DOCENTRY
WHERE PCH1.TARGETTYPE=19 )
)
V
PIVOT (SUM (TOTAL) FOR MES IN ([1], [2], [3], [4], [5],
[6], [7], [8], [9], [10], [11], [12]) ) as PT
quier un tipo distinct con el proveedor, pero apenas estoy usando el pivot.
Edited by: mary merecias on Nov 7, 2011 6:28 PM
Mary, prueba esta query
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 OPCH Y0 WHERE Y0.CardCode=B.CardCode AND Y0.DocDate>=(@INI) AND Y0.DocDate<=(@FIN)),0)
-ISNULL((SELECT SUM(Y0.DocTotal) FROM ORDR 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 OPCH 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 ORPC 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
Es un poco larga y complicada pero funciona
Saludos
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
7 | |
6 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.