cancel
Showing results for 
Search instead for 
Did you mean: 

Help Joining OITW with the Union of INV1 and RIN1

GAP_C
Explorer
0 Kudos

Please your help, I'm almost there, I need to join the OITW table with stock information with the union of the tables INV1 and RIN1, which i have used to calculate the maximum sales per sku. My objective is to calculate if Inventory onHand + Requested is grater than the max sales multiply by 3, as 3 months is the lead time.

TABLE 1

SELECT
TBL."SKU",
TBL."DESCRIPCION",
MAX(TBL."TOTAL") AS MAX_VENTA
FROM
(
SELECT
MES,
SKU,
DESCRIPCION,
SUM(TOTAL) AS TOTAL
FROM
(
SELECT
MONTH(T3."DocDate") AS MES,
T3."ItemCode" AS SKU,
T3."Dscription" AS DESCRIPCION,
T3."Quantity" AS TOTAL
FROM INV1 T3
WHERE T3."ItemCode" IS NOT NULL
UNION ALL
SELECT
MONTH(T3."DocDate") AS MES,
T3."ItemCode" AS SKU,
T3."Dscription" AS DESCRIPCION,
-T3."Quantity" AS TOTAL
FROM RIN1 T3
WHERE T3."ItemCode" IS NOT NULL
) TABLA1
GROUP BY MES, SKU, DESCRIPCION
ORDER BY MES, SKU
) TBL
GROUP BY SKU, DESCRIPCION
ORDER BY SKU

TABLE 2

SELECT
T0."ItemCode" AS SKU,
T0."OnHand",
T0."IsCommited",
T0."OnOrder",
T0."Counted"
FROM OITW T0

View Entire Topic
GAP_C
Explorer

 


@GAP_C wrote:

Please your help, I'm almost there, I need to join the OITW table with stock information with the union of the tables INV1 and RIN1, which i have used to calculate the maximum sales per sku. My objective is to calculate if Inventory onHand + Requested is grater than the max sales multiply by 3, as 3 months is the lead time.

TABLE 1

SELECT
TBL."SKU",
TBL."DESCRIPCION",
MAX(TBL."TOTAL") AS MAX_VENTA
FROM
(
SELECT
MES,
SKU,
DESCRIPCION,
SUM(TOTAL) AS TOTAL
FROM
(
SELECT
MONTH(T3."DocDate") AS MES,
T3."ItemCode" AS SKU,
T3."Dscription" AS DESCRIPCION,
T3."Quantity" AS TOTAL
FROM INV1 T3
WHERE T3."ItemCode" IS NOT NULL
UNION ALL
SELECT
MONTH(T3."DocDate") AS MES,
T3."ItemCode" AS SKU,
T3."Dscription" AS DESCRIPCION,
-T3."Quantity" AS TOTAL
FROM RIN1 T3
WHERE T3."ItemCode" IS NOT NULL
) TABLA1
GROUP BY MES, SKU, DESCRIPCION
ORDER BY MES, SKU
) TBL
GROUP BY SKU, DESCRIPCION
ORDER BY SKU

TABLE 2

SELECT
T0."ItemCode" AS SKU,
T0."OnHand",
T0."IsCommited",
T0."OnOrder",
T0."Counted"
FROM OITW T0


This is the final script I used.

 

SELECT
TBL2."SKU",
TBL2."DESCRIPCION",
TBL2."MAX_VENTA",
T0."OnHand",
T0."IsCommited",
T0."OnOrder"
FROM
(
SELECT
TBL."SKU",
TBL."DESCRIPCION",
MAX(TBL."TOTAL") AS MAX_VENTA
FROM
(
SELECT
MES,
SKU,
DESCRIPCION,
SUM(TOTAL) AS TOTAL
FROM
(
SELECT
MONTH(T3."DocDate") AS MES,
T3."ItemCode" AS SKU,
T3."Dscription" AS DESCRIPCION,
T3."Quantity" AS TOTAL
FROM INV1 T3
WHERE T3."ItemCode" IS NOT NULL
UNION ALL
SELECT
MONTH(T3."DocDate") AS MES,
T3."ItemCode" AS SKU,
T3."Dscription" AS DESCRIPCION,
-T3."Quantity" AS TOTAL
FROM RIN1 T3
WHERE T3."ItemCode" IS NOT NULL
) TABLA1
GROUP BY MES, SKU, DESCRIPCION
ORDER BY MES, SKU
) TBL
GROUP BY SKU, DESCRIPCION 
ORDER BY SKU
) TBL2
INNER JOIN OITM T0 ON TBL2."SKU" = T0."ItemCode"
WHERE T0."InvntItem"='Y'
ORDER BY "SKU"