Inventario a una Fecha determinada con su costo

Consultas SAP
1

Buenas tardes

Acudosa a usted para solicitar la ayuda de si tiene un query que arroje el inventario a una fecha deterninada con su costo por items. @willy_caldero. Agradeceira toda la ayuda posibible

Saludos Cordiales

2

Hola, buenas tardes:

Tengo uno que tiene un uso similar. Te invito a revisarlo y ver si te resulta útil.

SELECT 
  CAST( T2."ItemCode" AS varchar(100)) AS "LinkItem",
  T2."ItemCode", 
  T2."ItemName", 
  T1."WhsCode", 
  T1."WhsName",
  IFNULL((SELECT (SUM(Y."InQty") - SUM(Y."OutQty"))
          FROM "OINM" Y 
          WHERE Y."ItemCode" = T2."ItemCode" 
            AND Y."Warehouse" = T1."WhsCode" 
            AND Y."DocDate" <= {?DocDate}), 0) AS "Stock",
  IFNULL((SELECT (SUM(Y."TransValue")) 
          FROM "OINM" Y 
          WHERE Y."ItemCode" = T2."ItemCode" 
            AND Y."Warehouse" = T1."WhsCode" 
            AND Y."DocDate" <= {?DocDate}), 0) AS "Costo Total",
  T3."ItmsGrpNam"
  --T2."LastPurPrc" AS "UltimoPrecioCompra",
 --T0."AvgPrice" AS "PrecioPromedio"
FROM 
  "OITM" T2
  INNER JOIN "OITW" T0 ON T0."ItemCode" = T2."ItemCode"
  INNER JOIN "OWHS" T1 ON T1."WhsCode" = T0."WhsCode"
  INNER JOIN "OITB" T3 ON T2."ItmsGrpCod" = T3."ItmsGrpCod"
WHERE 
  (SELECT (SUM(Y."InQty") - SUM(Y."OutQty"))
   FROM "OINM" Y  
   WHERE Y."ItemCode" = T2."ItemCode" 
     AND Y."Warehouse" = T1."WhsCode" 
     AND Y."DocDate" <= {?DocDate}) != 0 
ORDER BY 
  T2."ItemCode", 
  T1."WhsCode";
3

Buenos dias
Benru
Primero que todo gracias por la informacion (query) tengo una consulta la variable {?DocDate}) a que a que tabla pertenece o debo cambiarla por la fecha final al corte de mi inventario. Ya que al momento de correctala me da error en esta variable.
error: sg 102, Level 15, State 1, Line 11
Incorrect syntax near ‘?’.

SIn mas por el momoenti y a la espera de tus comentarios

4

Hola, buen día, ppierce. Te sugiero que pruebes con el “DocDate” de la OINM.

5 
DECLARE @dataInicio DATETIME
DECLARE @dataFinal DATETIME
DECLARE @tempo INT

SET @tempo = (SELECT TOP 1 T0.TRANSNUM FROM OINM T0 WHERE T0.[DocDate] >= [%0] AND T0.[DocDate] <= [%1])
SET @dataInicio = (SELECT '[%0]')
SET @dataFinal = (SELECT '[%1]')

SELECT
    [Artículo] = t1.itemcode,
    [Desc. Artículo] = t1.ItemName,
    [Unidad de Medida] = t1.InvntryUom,
    [Stock inicial] = ISNULL(COALESCE(t0.saldoinic, 0), 0),
    [Entradas] = ISNULL(t1.entradas, 0),
    Consumo = ISNULL(t1.consumo, 0),
    [Consumo medio diario] = ISNULL(t1.consumo / DATEDIFF(day, @dataInicio, @dataFinal), 0),
  /*[costo] =   ISNULL(COALESCE(t1.AvgPrice,0),0),*/
    [Stock final] = ISNULL(COALESCE(t0.saldoinic, 0) + COALESCE(t1.saldofin, 0), 0),
    [Stock promedio] = ISNULL((COALESCE(t0.saldoinic, 0) + (COALESCE(t0.saldoinic, 0) + t1.saldofin)) / 2, 0),
    [Rotación stock] = CASE
        WHEN ISNULL((COALESCE(t0.saldoinic, 0) + (COALESCE(t0.saldoinic, 0) + t1.saldofin)) / 2, 0) = 0 THEN 0
        ELSE ISNULL(t1.consumo / ((COALESCE(t0.saldoinic, 0) + (COALESCE(t0.saldoinic, 0) + t1.saldofin)) / 2), 0)
    END,
    [Cobertura de stock] = CASE
        WHEN ISNULL(COALESCE(t1.consumo / DATEDIFF(day, @dataInicio, @dataFinal), 0), 0) = 0 THEN 0
        ELSE ISNULL(((COALESCE(t0.saldoinic, 0) + (COALESCE(t0.saldoinic, 0) + t1.saldofin)) / 2) / (t1.consumo / DATEDIFF(day, @dataInicio, @dataFinal)), 0)
    END
FROM 
(
    SELECT
        a1.itemcode,
        a1.ItemName,
        a1.InvntryUom,
         /*p1.AvgPrice,*/
        /*s1.Price,*/
        saldoInic = ISNULL(SUM(COALESCE(s1.Inqty, 0)) - SUM(COALESCE(s1.outqty, 0)), 0)
    FROM OITM a1
    JOIN OINM s1 ON a1.itemcode = s1.itemcode
   /*JOIN OITW p1 ON a1.[ItemCode] = p1.[ItemCode]*/
    WHERE s1.docdate < @dataInicio
    GROUP BY
        a1.itemcode,
        a1.ItemName,
        a1.InvntryUom
/*p1.AvgPrice */
      /*s1.Price*/
)       
AS t0
INNER JOIN (
    SELECT
        a.itemcode,
       /*p.AvgPrice, */
       /*s.Price,*/
        a.ItemName,
        a.InvntryUom,
        entradas = ISNULL(SUM(s.Inqty), 0),
        consumo = ISNULL(SUM(s.outqty), 0),
        saldoFin = ISNULL(SUM(s.Inqty) - SUM(s.outqty), 0)
         
    FROM OINM s
    JOIN OITM a ON a.itemcode = s.itemcode
  /*JOIN OITW p ON a.[ItemCode] = p.[ItemCode]*/
    WHERE s.docdate BETWEEN @dataInicio AND @dataFinal
    GROUP BY
        a.itemcode,
        a.ItemName,
        a.InvntryUom
     /* p.AvgPrice */
        /*s.Price*/
) t1 ON t1.itemcode = t0.itemcode
6

@Benru encontre esta consulta en foro me trate todo lo que ando buscando pero al colocarle la colunma .AvgPrice con la union de esta forma JOIN OITW p1 ON a1.[ItemCode] = p1.[ItemCode] me distorciona o me arroja resultado erroners no se que estoy haciendo mal. si puidera revisarlas me pueda ayudar con el error te lo agradeceria.

Saludo