Buen día
Tengo una consulta en el querie manager de SAP que genera 2 tablas temporales pero tiene los campos de fecha fijos,al querer modificarla para colocarle variables del tipo [%0], me da un error, en otras consultas si es posible tener estas variables que normalmente muestran las casillas donde se puede colocar la fecha antes de que sap muestre la consulta, no se si por la creacion de las tablas temporales hana no soporte la operacion? alguien me puede orientar dejo codigo de la consulta
P.d las parte que quiero modificar es la del BETWEEN.
Gracias de antemano
CREATE LOCAL TEMPORARY TABLE #Facturas(“Articulo” varchar(30), “Nombre” varchar(250), “Stock” float, “Min” float,
“Max” float, “Costo” decimal(15,2), “Lista” varchar(20), “Almacen” float, “Venta1” float, “Total” decimal(15,2),
“Utilidad” decimal(15,2));
CREATE LOCAL TEMPORARY TABLE #Devolucion("Articulo" varchar(30), "Nombre" varchar(250), "Stock" float, "Min" float,
"Max" float, "Costo" decimal(15,2), "Lista" varchar(20), "Almacen" float, "Devolucion1" float);
INSERT INTO #Facturas
(SELECT DISTINCT
T0."ItemCode",
T0."ItemName",
T1."OnHand",
T1."MinStock",
T1."MaxStock",
T1."AvgPrice",
T2."Price",
T1."WhsCode",
T3."Venta",
T3."Total",
T3."Utilidad"
FROM OITM T0
INNER JOIN OITW T1 ON T0."ItemCode" = T1."ItemCode"
INNER JOIN ITM1 T2 ON T0."ItemCode" = T2."ItemCode"
LEFT JOIN ( SELECT DISTINCT
T0."DocDate",
T1."ItemCode",
SUM(T1."Quantity") as "Venta",
SUM(T1."LineTotal") as "Total",
SUM(T1."GrssProfit") as "Utilidad",
T1."WhsCode"
FROM OINV T0
INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry"
WHERE
T0."CANCELED" = 'N'
GROUP BY T1."ItemCode", T1."WhsCode", T0."DocDate") T3 ON T0."ItemCode" = T3."ItemCode" and T3."WhsCode" = T1."WhsCode"
WHERE
T2."PriceList" = '1'
and T1."WhsCode" IN ('201', '222', '223', '224', '225','228','229','230','232','233','234','235','236','237','239','240','242','243')
and T3."DocDate" **BETWEEN '20200101' and '20201208'**
ORDER BY
T0."ItemCode",
T1."WhsCode");
INSERT INTO #Devolucion(
SELECT DISTINCT
T0."ItemCode",
T0."ItemName",
T1."OnHand",
T1."MinStock",
T1."MaxStock",
T1."AvgPrice",
T2."Price",
T1."WhsCode",
T3."Devolucion"
FROM OITM T0
INNER JOIN OITW T1 ON T0."ItemCode" = T1."ItemCode"
INNER JOIN ITM1 T2 ON T0."ItemCode" = T2."ItemCode"
LEFT JOIN ( SELECT DISTINCT
T0."DocDate",
T1."ItemCode",
--SUM(T1."Quantity") as "Venta",
SUM(T1."Quantity") as "Devolucion",
T1."WhsCode"
FROM ORIN T0
INNER JOIN RIN1 T1 ON T0."DocEntry" = T1."DocEntry"
WHERE
T0."CANCELED" = 'N'
GROUP BY T1."ItemCode", T1."WhsCode", T0."DocDate") T3 ON T0."ItemCode" = T3."ItemCode" and T3."WhsCode" = T1."WhsCode"
WHERE
T2."PriceList" = '1'
and T1."WhsCode" IN ('201', '222', '223', '224', '225','228','229','230','232','233','234','235','236','237','239','240','242','243')
and T3."DocDate" BETWEEN '20200101' and '20201208'
ORDER BY
T0."ItemCode",
T1."WhsCode");
SELECT
T0."Articulo",
T0."Nombre",
T0."Stock",
T0."Min",
T0."Max",
T0."Costo",
T0."Almacen",
T0."Lista",
CAST(SUM(T0."Venta1") as Decimal(15,2)) as "Venta",
CAST(SUM(T1."Devolucion1") as Decimal(15,2)) "Devoluciones",
SUM(T0."Total") as "Total",
SUM(T0."Utilidad") as "Utilidad"
FROM
#Facturas T0
LEFT JOIN #Devolucion T1 ON T0."Articulo" = T1."Articulo" and T0."Almacen" = T1."Almacen"
GROUP BY
T0."Articulo",
T0."Nombre",
T0."Stock",
T0."Min",
T0."Max",
T0."Costo",
T0."Almacen",
T0."Lista"
ORDER BY
T0."Nombre";
DROP TABLE #Facturas;
DROP TABLE #Devolucion;