cancel
Showing results for 
Search instead for 
Did you mean: 

query cubo

angeles804
Active Contributor
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

felipe_loyolarodriguez
Active Contributor
0 Kudos

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

angeles804
Active Contributor
0 Kudos

oks. gracias felipe. ya me salio como yo quería ya sabes que le modificamos la ORPC para las notas de credito de proveedores, agregue campos fiscales del proveedor. y ya quedo de lujo gracias.

Answers (0)