Condiciones Query Manager

SAP Business One
1

Estimados tengo un inconveniente con los placeholders tengo esta query

DECLARE @AnioActual INT
SET @AnioActual = (SELECT YEAR(GETDATE()));

SELECT 
    T0.[U_GKP_FAMILIAART] AS 'Familia',
    T0.ItemCode AS 'Codigo SAP', 
    T0.ItemName AS 'Descripcion del articulo', 

    /* Ventas por Anio */
   COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END) FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 6),0) +
     ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END) FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 6),0)), 0) as  'Ventas (M) AnioACTUAL - 6',

    COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 5),0)+
        ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END) FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 5),0)), 0) as 'Ventas (M) AnioACTUAL - 5' ,

     COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 4),0)+
        ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 4),0)), 0) as 'Ventas (M) AnioACTUAL - 4',

   COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 3),0) +
        ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END) FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 3),0)), 0) as 'Ventas (M) AnioACTUAL - 3',

     COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END) FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 2),0)+
        ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 2),0)), 0) as 'Ventas (M) AnioACTUAL - 2',

    COALESCE(( ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 1),0)+
        ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 1),0)), 0) as ' Ventas (M) AnioACTUAL - 1',

    COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END) FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual),0)+
        ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END) FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual),0)), 0) as ' [Ventas (M) AnioACTUAL] ',

    /* Stock */
    T0.OnHand AS 'Stock',
    T0.OnOrder AS 'Solicitado a proveedor (m)',
    T0.IsCommited AS'[Solicitado por cliente',
    (T0.OnHand - T0.IsCommited + T0.OnOrder) AS 'Disponible',

    '' AS 'Recomendado Importar (m)',
    COALESCE((SELECT TOP 1 T1.[PriceBefDi] FROM POR1 T1 INNER JOIN OPOR T2 ON T1.Docentry = T2.Docentry  WHERE T1.ItemCode = T0.itemcode ORDER BY T2.DOCDATE DESC),0) as  'Precio unitario ($)',
/* Ventas por Mes del Anio Anterior */
    COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 1),0)+
        ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 1),0)),0) as 'Ventas ENERO AnioACTUAL -1',
	 COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 2),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 2),0)),0) as 'Ventas FEBRERO AnioACTUAL -1',
	 COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 3),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 3),0)),0) as 'Ventas MARZO AnioACTUAL -1',
	 COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 4),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 4),0)),0)as 'Ventas ABRIL AnioACTUAL -1',
	 COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 5),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 5),0)),0) as 'Ventas MAYO AnioACTUAL -1',
	COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 6),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 6),0)),0) as 'Ventas JUNIO AnioACTUAL -1',
	 COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 7),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 7),0)),0)as 'Ventas JULIO AnioACTUAL -1',
	 COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 8),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 8),0)),0)as 'Ventas AGOSTO AnioACTUAL -1',
	 COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 9),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 9),0)),0)as 'Ventas SEPTIEMBRE AnioACTUAL -1',
	 COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 10),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 10),0)),0)as 'Ventas OCTUBRE AnioACTUAL -1',
	COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 11),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 11),0)),0)as 'Ventas NOVIEMBRE AnioACTUAL -1',
	 COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 12),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 12),0)),0)as 'Ventas DICIEMBRE AnioACTUAL -1',
	
    '' AS 'Total Proyectado US$'

FROM OITM T0
WHERE T0.[U_GKP_FAMILIAART] = 'SHD GC 2KV'

Lo hice en SMSS pero al querer pasar al Query managerme sale el siguiente error :

image
image2026×887 92.9 KB

1). [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Falta la comilla de cierre después de la cadena de caracteres 'SHD GC 2KV'. 2). [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Sintaxis incorrecta cerca de 'SHD GC 2KV'. 3). [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]No se puede preparar la instrucción o instrucciones.  'Job de programación back end' (OBSJ)

pero cuando le coloco un valor cualquiera si me lo reconoce

image
image2318×841 66 KB

a que se debe y como puedo solucionarlo ?

2

Buena tarde,

No habia comprendido bien tu pregunta, pero veo que es para ingresar un parametro al query manager. Intenta hacer esto y con eso solucionas tu inconveniente, solo adaptalo con tu campo.

/*SELECT FROM OITB T1*/
DECLARE @Codigo INT 
DECLARE @AnioActual INT

/*WHERE*/ 

SET @Codigo = /* T1.ItmsGrpCod */ [%0]
SET @AnioActual = (SELECT YEAR(GETDATE()))


SELECT 
    --T0.[U_GKP_FAMILIAART] AS 'Familia',
    T0.ItemCode AS 'Codigo SAP', 
    T0.ItemName AS 'Descripcion del articulo', 

    /* Ventas por Anio */
   COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END) FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 6),0) +
     ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END) FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 6),0)), 0) as  'Ventas (M) AnioACTUAL - 6',

    COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 5),0)+
        ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END) FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 5),0)), 0) as 'Ventas (M) AnioACTUAL - 5' ,

     COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 4),0)+
        ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 4),0)), 0) as 'Ventas (M) AnioACTUAL - 4',

   COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 3),0) +
        ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END) FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 3),0)), 0) as 'Ventas (M) AnioACTUAL - 3',

     COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END) FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 2),0)+
        ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 2),0)), 0) as 'Ventas (M) AnioACTUAL - 2',

    COALESCE(( ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 1),0)+
        ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 1),0)), 0) as ' Ventas (M) AnioACTUAL - 1',

    COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END) FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual),0)+
        ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END) FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual),0)), 0) as ' [Ventas (M) AnioACTUAL] ',

    /* Stock */
    T0.OnHand AS 'Stock',
    T0.OnOrder AS 'Solicitado a proveedor (m)',
    T0.IsCommited AS'[Solicitado por cliente',
    (T0.OnHand - T0.IsCommited + T0.OnOrder) AS 'Disponible',

    '' AS 'Recomendado Importar (m)',
    COALESCE((SELECT TOP 1 T1.[PriceBefDi] FROM POR1 T1 INNER JOIN OPOR T2 ON T1.Docentry = T2.Docentry  WHERE T1.ItemCode = T0.itemcode ORDER BY T2.DOCDATE DESC),0) as  'Precio unitario ($)',
/* Ventas por Mes del Anio Anterior */
    COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 1),0)+
        ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 1),0)),0) as 'Ventas ENERO AnioACTUAL -1',
	 COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 2),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 2),0)),0) as 'Ventas FEBRERO AnioACTUAL -1',
	 COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 3),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 3),0)),0) as 'Ventas MARZO AnioACTUAL -1',
	 COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 4),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 4),0)),0)as 'Ventas ABRIL AnioACTUAL -1',
	 COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 5),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 5),0)),0) as 'Ventas MAYO AnioACTUAL -1',
	COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 6),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 6),0)),0) as 'Ventas JUNIO AnioACTUAL -1',
	 COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 7),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 7),0)),0)as 'Ventas JULIO AnioACTUAL -1',
	 COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 8),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 8),0)),0)as 'Ventas AGOSTO AnioACTUAL -1',
	 COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 9),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 9),0)),0)as 'Ventas SEPTIEMBRE AnioACTUAL -1',
	 COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 10),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 10),0)),0)as 'Ventas OCTUBRE AnioACTUAL -1',
	COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 11),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 11),0)),0)as 'Ventas NOVIEMBRE AnioACTUAL -1',
	 COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 12),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 12),0)),0)as 'Ventas DICIEMBRE AnioACTUAL -1',
	
 ' ' AS 'Total Proyectado US$'

FROM OITM T0
WHERE  T0.ItmsGrpCod = @Codigo
1 me gusta
3

Hola @jhernandez gracias por tu apoyo, lo modifique a mi campo de usuario de esta manera y me corre hasta aqui pero al dar ejecutar me salta error.

image
image1143×482 37.1 KB

/*SELECT FROM OITM T1*/

DECLARE @Codigo nvarchar(50)
DECLARE @AnioActual INT

/*WHERE*/ 

SET @Codigo = /* T1.[U_GKP_FAMILIAART] */ [%0]
SET @AnioActual = (SELECT YEAR(GETDATE()))

y en la parte del where asi

FROM OITM T0
WHERE  T0.[U_GKP_FAMILIAART] = @Codigo```

pero ahora me sale este error :/

1). [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]El nombre de columna ‘H07RN’ no es válido. 2). [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]El nombre de columna ‘F’ no es válido. 3). [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]No se puede preparar la instrucción o instrucciones. ‘Job de programación back end’ (OBSJ)

Que estoy haciendo mal ?
4

Buena tarde, este campo que tenes T0.[U_GKP_FAMILIAART], como está definido en sap? lista desplegable o hace referencia a otra tabla ?

5

Es un campo nvarchar simplemente no he creado ni lista ni referencia a otra tabla, el problema era que no lo enviaba como texto, la query final seria así.

/*SELECT DISTINCT T1.[U_GKP_FAMILIAART] FROM OITM T1 */

DECLARE @Codigo nvarchar(50)
DECLARE @AnioActual INT

/*WHERE*/ 

SET @Codigo  = /* T1.[U_GKP_FAMILIAART] */ '[%0]'
SET @AnioActual = (SELECT YEAR(GETDATE()))


SELECT 
    --T0.[U_GKP_FAMILIAART] AS 'Familia',
    T0.ItemCode AS 'Codigo SAP', 
    T0.ItemName AS 'Descripcion del articulo', 

    /* Ventas por Anio */
   COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END) FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 6),0) +
     ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END) FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 6),0)), 0) as  'Ventas (M) AnioACTUAL - 6',

    COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 5),0)+
        ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END) FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 5),0)), 0) as 'Ventas (M) AnioACTUAL - 5' ,

     COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 4),0)+
        ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 4),0)), 0) as 'Ventas (M) AnioACTUAL - 4',

   COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 3),0) +
        ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END) FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 3),0)), 0) as 'Ventas (M) AnioACTUAL - 3',

     COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END) FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 2),0)+
        ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 2),0)), 0) as 'Ventas (M) AnioACTUAL - 2',

    COALESCE(( ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 1),0)+
        ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual - 1),0)), 0) as ' Ventas (M) AnioACTUAL - 1',

    COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END) FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual),0)+
        ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END) FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND T2.DocSubType NOT IN ('DN', 'IB') AND YEAR(T2.DocDate) = @AnioActual),0)), 0) as ' [Ventas (M) AnioACTUAL] ',

    /* Stock */
    T0.OnHand AS 'Stock',
    T0.OnOrder AS 'Solicitado a proveedor (m)',
    T0.IsCommited AS'[Solicitado por cliente',
    (T0.OnHand - T0.IsCommited + T0.OnOrder) AS 'Disponible',

    '' AS 'Recomendado Importar (m)',
    COALESCE((SELECT TOP 1 T1.[PriceBefDi] FROM POR1 T1 INNER JOIN OPOR T2 ON T1.Docentry = T2.Docentry  WHERE T1.ItemCode = T0.itemcode ORDER BY T2.DOCDATE DESC),0) as  'Precio unitario ($)',
/* Ventas por Mes del Anio Anterior */
    COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 1),0)+
        ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 1),0)),0) as 'Ventas ENERO AnioACTUAL -1',
	 COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 2),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 2),0)),0) as 'Ventas FEBRERO AnioACTUAL -1',
	 COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 3),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 3),0)),0) as 'Ventas MARZO AnioACTUAL -1',
	 COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 4),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 4),0)),0)as 'Ventas ABRIL AnioACTUAL -1',
	 COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 5),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 5),0)),0) as 'Ventas MAYO AnioACTUAL -1',
	COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 6),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 6),0)),0) as 'Ventas JUNIO AnioACTUAL -1',
	 COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 7),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 7),0)),0)as 'Ventas JULIO AnioACTUAL -1',
	 COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 8),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 8),0)),0)as 'Ventas AGOSTO AnioACTUAL -1',
	 COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 9),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 9),0)),0)as 'Ventas SEPTIEMBRE AnioACTUAL -1',
	 COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 10),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 10),0)),0)as 'Ventas OCTUBRE AnioACTUAL -1',
	COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 11),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 11),0)),0)as 'Ventas NOVIEMBRE AnioACTUAL -1',
	 COALESCE((ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity * -1 ELSE T1.Quantity END)FROM INV1 T1 INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 12),0)+
    ISNULL((SELECT SUM(CASE WHEN T2.CANCELED = 'C' THEN T1.Quantity ELSE T1.Quantity * -1 END)FROM RIN1 T1 INNER JOIN ORIN T2 ON T1.DocEntry = T2.DocEntry WHERE T1.ItemCode = T0.ItemCode AND YEAR(T2.DocDate) = @AnioActual-1 AND MONTH(T2.DocDate) = 12),0)),0)as 'Ventas DICIEMBRE AnioACTUAL -1',
	
 ' ' AS 'Total Proyectado US$'

FROM OITM T0
WHERE T0.[U_GKP_FAMILIAART] = @Codigo

Muchas gracias estimado marcare tu primera respuesta como la solucion.
@jhernandez

1 me gusta