Skip to Content

query cubo

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Nov 08, 2011 at 04:17 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

    Add comment
    10|10000 characters needed characters exceeded