Query con fecha de compra de las unidades que hay disponibles en el almacén

[vhzl123]

Hola!

No he encontrado un informe estandar de SAP que me de la información que necesito, creo que necesito crear un query.

Lo que busco es un informe que me devuelva la fecha de compra de las unidades que tengo en stock.

Suponemos que tengo 7 unidades, he visto informes que me devuelven la última fecha de compra pero lo quiero saber la primera fecha de compra. Si compré 2 UD ayer y las otras 5 UD hace un mes, la query me devolvería las fechas de compra últimas hasta llegar a las 7 UD que hay en stock. 

¿Se os ocurre como lo puedo hacer?

Muchas gracias!!

[vhzl123]

He llegado a esto pero no devuelve aun lo que me gustaría. Devuelve en todos los casos Abril2024 y no va sumando. 

SELECT 
    T1."ItemCode", 
    (T2."OnHand" - T2."IsCommited" + T2."OnOrder") AS Disponible,
    CASE 
        WHEN (T2."OnHand" - T2."IsCommited" + T2."OnOrder") <= SUM(CASE WHEN MONTH(T0."TaxDate") = 4 AND YEAR(T0."TaxDate") = 2024 THEN T1."Quantity" ELSE 0 END) OVER (ORDER BY T0."TaxDate" DESC) THEN 'ABRIL24'
        WHEN (T2."OnHand" - T2."IsCommited" + T2."OnOrder") <= SUM(CASE WHEN MONTH(T0."TaxDate") = 3 AND YEAR(T0."TaxDate") = 2024 THEN T1."Quantity" ELSE 0 END) OVER (ORDER BY T0."TaxDate" DESC) THEN 'MARZO24'
        WHEN (T2."OnHand" - T2."IsCommited" + T2."OnOrder") <= SUM(CASE WHEN MONTH(T0."TaxDate") = 2 AND YEAR(T0."TaxDate") = 2024 THEN T1."Quantity" ELSE 0 END) OVER (ORDER BY T0."TaxDate" DESC) THEN 'FEBRERO24'
        WHEN (T2."OnHand" - T2."IsCommited" + T2."OnOrder") <= SUM(CASE WHEN MONTH(T0."TaxDate") = 1 AND YEAR(T0."TaxDate") = 2024 THEN T1."Quantity" ELSE 0 END) OVER (ORDER BY T0."TaxDate" DESC) THEN 'ENERO24'
        WHEN (T2."OnHand" - T2."IsCommited" + T2."OnOrder") <= SUM(CASE WHEN MONTH(T0."TaxDate") = 12 AND YEAR(T0."TaxDate") = 2023 THEN T1."Quantity" ELSE 0 END) OVER (ORDER BY T0."TaxDate" DESC) THEN 'DIC23'
        WHEN (T2."OnHand" - T2."IsCommited" + T2."OnOrder") <= SUM(CASE WHEN MONTH(T0."TaxDate") = 11 AND YEAR(T0."TaxDate") = 2023 THEN T1."Quantity" ELSE 0 END) OVER (ORDER BY T0."TaxDate" DESC) THEN 'NOV23'
        WHEN (T2."OnHand" - T2."IsCommited" + T2."OnOrder") <= SUM(CASE WHEN MONTH(T0."TaxDate") = 10 AND YEAR(T0."TaxDate") = 2023 THEN T1."Quantity" ELSE 0 END) OVER (ORDER BY T0."TaxDate" DESC) THEN 'OCT23'
        WHEN (T2."OnHand" - T2."IsCommited" + T2."OnOrder") <= SUM(CASE WHEN MONTH(T0."TaxDate") = 9 AND YEAR(T0."TaxDate") = 2023 THEN T1."Quantity" ELSE 0 END) OVER (ORDER BY T0."TaxDate" DESC) THEN 'SEPT23'
        WHEN (T2."OnHand" - T2."IsCommited" + T2."OnOrder") <= SUM(CASE WHEN MONTH(T0."TaxDate") = 8 AND YEAR(T0."TaxDate") = 2023 THEN T1."Quantity" ELSE 0 END) OVER (ORDER BY T0."TaxDate" DESC) THEN 'AGO23'
        WHEN (T2."OnHand" - T2."IsCommited" + T2."OnOrder") <= SUM(CASE WHEN MONTH(T0."TaxDate") = 7 AND YEAR(T0."TaxDate") = 2023 THEN T1."Quantity" ELSE 0 END) OVER (ORDER BY T0."TaxDate" DESC) THEN 'JUL23'
        WHEN (T2."OnHand" - T2."IsCommited" + T2."OnOrder") <= SUM(CASE WHEN MONTH(T0."TaxDate") = 6 AND YEAR(T0."TaxDate") = 2023 THEN T1."Quantity" ELSE 0 END) OVER (ORDER BY T0."TaxDate" DESC) THEN 'JUN23'
        WHEN (T2."OnHand" - T2."IsCommited" + T2."OnOrder") <= SUM(CASE WHEN MONTH(T0."TaxDate") = 5 AND YEAR(T0."TaxDate") = 2023 THEN T1."Quantity" ELSE 0 END) OVER (ORDER BY T0."TaxDate" DESC) THEN 'MAYO23'
    END AS MesAlcanzado,
    SUM(CASE WHEN MONTH(T0."TaxDate") = 4 AND YEAR(T0."TaxDate") = 2024 THEN T1."Quantity" ELSE 0 END) AS Cantidad_ABRIL24, 
    SUM(CASE WHEN MONTH(T0."TaxDate") = 3 AND YEAR(T0."TaxDate") = 2024 THEN T1."Quantity" ELSE 0 END) AS Cantidad_MAR24, 
    SUM(CASE WHEN MONTH(T0."TaxDate") = 2 AND YEAR(T0."TaxDate") = 2024 THEN T1."Quantity" ELSE 0 END) AS Cantidad_FEB24, 
    SUM(CASE WHEN MONTH(T0."TaxDate") = 1 AND YEAR(T0."TaxDate") = 2024 THEN T1."Quantity" ELSE 0 END) AS Cantidad_ENE24, 
    SUM(CASE WHEN MONTH(T0."TaxDate") = 12 AND YEAR(T0."TaxDate") = 2023 THEN T1."Quantity" ELSE 0 END) AS Cantidad_DIC23,
    SUM(CASE WHEN MONTH(T0."TaxDate") = 11 AND YEAR(T0."TaxDate") = 2023 THEN T1."Quantity" ELSE 0 END) AS Cantidad_NOV23,
    SUM(CASE WHEN MONTH(T0."TaxDate") = 10 AND YEAR(T0."TaxDate") = 2023 THEN T1."Quantity" ELSE 0 END) AS Cantidad_OCT23, 
    SUM(CASE WHEN MONTH(T0."TaxDate") = 9 AND YEAR(T0."TaxDate") = 2023 THEN T1."Quantity" ELSE 0 END) AS Cantidad_SEPT23,
    SUM(CASE WHEN MONTH(T0."TaxDate") = 8 AND YEAR(T0."TaxDate") = 2023 THEN T1."Quantity" ELSE 0 END) AS Cantidad_AGOSTO23,
    SUM(CASE WHEN MONTH(T0."TaxDate") = 7 AND YEAR(T0."TaxDate") = 2023 THEN T1."Quantity" ELSE 0 END) AS Cantidad_JULIO23,
    SUM(CASE WHEN MONTH(T0."TaxDate") = 6 AND YEAR(T0."TaxDate") = 2023 THEN T1."Quantity" ELSE 0 END) AS Cantidad_JUNIO23, 
    SUM(CASE WHEN MONTH(T0."TaxDate") = 5 AND YEAR(T0."TaxDate") = 2023 THEN T1."Quantity" ELSE 0 END) AS Cantidad_MAYO23

FROM 
    OPDN T0  
INNER JOIN 
    PDN1 T1 ON T0."DocEntry" = T1."DocEntry" 
INNER JOIN 
    OITM T2 ON T1."ItemCode" = T2."ItemCode" 
WHERE 
    (T2."OnHand" - T2."IsCommited" + T2."OnOrder") > 0 
    AND T1."ItemCode" LIKE '101%%' 
    AND T0."CardCode" = 'P003435' 
GROUP BY 
    T1."ItemCode", 
    T2."OnHand" - T2."IsCommited", 
    T2."OnOrder",
    T0."TaxDate",
    T1."Quantity"
ORDER BY 
    MesAlcanzado;