on 02-02-2022 1:26 AM
Hi Experts. I have this following query working to see the current stocks in SAP and quantity sold, say last one year. the problem i have here is, if there is no invoice for a particular SKU during given timeframe then this report doesn't fetch the data of that SKU. I tried even ISNULL to fetch that but certainly missing something. please advise.
here is the query:
Select A.[ItemCode], A.[ItemName] [itemDescription], A.[OnHand] [In Stock], A.[IsCommited], A.[OnOrder] [Ordered], ((A.[OnHand] - A.[IsCommited]) + A.OnOrder) [Available], SUM(A.Quantity) [Quantity Sold], A.[Price]
From(
SELECT
T2.[ItemCode], T2.[ItemName], isnull(T4.[OnHand],0) as 'OnHand', T4.[IsCommited], T4.[OnOrder],
ISNULL(SUM(T1.[Quantity]),0) as 'Quantity', T3.[Price]
FROM
OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] INNER JOIN ITM1 T3 ON T2.[ItemCode] = T3.[ItemCode] INNER JOIN OITW T4 ON T2.[ItemCode] = T4.[ItemCode] INNER JOIN OMRC T5 ON T2.[FirmCode] = T5.[FirmCode]
WHERE
T0.[CANCELED] = 'N' and
T0.[DocDate] >=[%0] and
T0.[DocDate] <=[%1] and
T5.[FirmName] = 'ABC' and
T3.[PriceList] = '2' and
T4.[WhsCode] = '100'
Group by
T2.[ItemCode], T2.[ItemName], T4.[OnHand],T4.[IsCommited], T4.[OnOrder], T3.[Price]
Union all
SELECT
T2.[ItemCode], T2.[ItemName], isnull(T4.[OnHand],0) as 'OnHand', T4.[IsCommited], T4.[OnOrder], ISNULL(SUM(T1.[Quantity])*-1,0) as 'Quantity', T3.[Price]
FROM
ORIN T0 INNER JOIN RIN1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] INNER JOIN ITM1 T3 ON T2.[ItemCode] = T3.[ItemCode] INNER JOIN OITW T4 ON T2.[ItemCode] = T4.[ItemCode] INNER JOIN OMRC T5 ON T2.[FirmCode] = T5.[FirmCode]
WHERE
T0.[CANCELED] = 'N' and
T0.[DocDate] >=[%0] and
T0.[DocDate] <=[%1] and
T5.[FirmName] = 'ABC' and
T3.[PriceList] = '2' and
T4.[WhsCode] = '100'
Group by
T2.[ItemCode],
T2.[ItemName], T4.[OnHand], T4.[IsCommited], T4.[OnOrder], T3.[Price] )A
Group by
A.[ItemCode], A.[ItemName], A.[OnHand], A.[IsCommited], A.[OnOrder], A.[Price]
Hi Shahzad,
I don't really know, why this happens.
Let's try something new
/**SELECT FROM [OFPR] T0 **/
DECLARE @DocDateFrom as datetime
/* WHERE */
Set @DocDateFrom = /* T0.F_RefDate */ '[%0]'
/**SELECT FROM [OFPR] T0 **/
DECLARE @DocDateTo as datetime
/* WHERE */
Set @DocDateTo = /* T0.T_RefDate */ '[%1]'
SELECT
[OITM].[ItemCode]
,[OITM].[ItemName]
,ISNULL([OITW].[OnHand],0) AS [OnHand]
,ISNULL([OITW].[IsCommited],0) AS [IsCommited]
,ISNULL([OITW].[OnOrder],0) AS [OnOrder]
,(ISNULL([OITW].[OnHand],0) - ISNULL([OITW].[IsCommited],0) + ISNULL([OITW].[OnOrder],0)) AS [Available]
,(
SELECT
ISNULL(SUM([INV1].[Quantity]),0)
FROM
[INV1]
INNER JOIN [OINV] ON [OINV].[DocEntry] =[INV1].[DocEntry] AND [OINV].[CANCELED] = 'N' AND [OINV].[DocDate] between @DocDateFrom AND @DocDateTo
WHERE
[INV1].[ItemCode] = [OITM].[ItemCode]
)
-
(
SELECT
ISNULL(SUM([RIN1].[Quantity]),0)
FROM
[RIN1]
INNER JOIN [ORIN] ON [ORIN].[DocEntry] = [RIN1].[DocEntry] AND [ORIN].[CANCELED] = 'N' AND [ORIN].[DocDate] between @DocDateFrom AND @DocDateTo
WHERE
[RIN1].[ItemCode] = [OITM].[ItemCode]
)
AS [SumSold]
,[ITM1].[Price]
FROM
[OITM]
LEFT JOIN [ITM1] ON [ITM1].[ItemCode] = [OITM].[ItemCode] AND [ITM1].[PriceList] = 2
LEFT JOIN [OITW] ON [OITW].[ItemCode] = [OITM].[ItemCode] AND [OITW].[WhsCode] = '100'
LEFT JOIN [OMRC] ON [OMRC].[FirmCode] = [OITM].[FirmCode]
WHERE
[OMRC].[FirmName] = 'ABC'
regards Lothar
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
It is always the same like in the Query
/**SELECT FROM [OMRC] T3 **/
DECLARE @FirmCode as smallint
/* WHERE */
Set @FirmCode = /* T3.FirmCode*/ '[%3]'
SELECT @FirmCode
regards Lothar
Hi Shahzad,
try this
SELECT
[OITM].[ItemCode]
,[OITM].[ItemName]
,ISNULL([OITW].[OnHand],0) AS [OnHand]
,ISNULL([OITW].[IsCommited],0) AS [IsCommited]
,ISNULL([OITW].[OnOrder],0) AS [OnOrder]
,(ISNULL([OITW].[OnHand],0) - ISNULL([OITW].[IsCommited],0) + ISNULL([OITW].[OnOrder],0)) AS [Available]
,(
SELECT
ISNULL(SUM([INV1].[Quantity]),0)
FROM
[INV1]
INNER JOIN [OINV] ON [OINV].[DocEntry] =[INV1].[DocEntry] AND [OINV].[CANCELED] = 'N' AND [OINV].[DocDate] between '[%0]' AND '[%1]'
WHERE
[INV1].[ItemCode] = [OITM].[ItemCode]
)
-
(
SELECT
ISNULL(SUM([RIN1].[Quantity]),0)
FROM
[RIN1]
INNER JOIN [ORIN] ON [ORIN].[DocEntry] = [RIN1].[DocEntry] AND [ORIN].[CANCELED] = 'N' AND [ORIN].[DocDate] between '[%0]' AND '[%1]'
WHERE
[RIN1].[ItemCode] = [OITM].[ItemCode]
)
AS [SumSold]
,[ITM1].[Price]
FROM
[OITM]
LEFT JOIN [ITM1] ON [ITM1].[ItemCode] = [OITM].[ItemCode] AND [ITM1].[PriceList] = 2
LEFT JOIN [OITW] ON [OITW].[ItemCode] = [OITM].[ItemCode] AND [OITW].[WhsCode] = '100'
LEFT JOIN [OMRC] ON [OMRC].[FirmCode] = [OITM].[FirmCode]
WHERE
[OMRC].[FirmName] = 'ABC'<br>
regards
Lothar
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thank you, Lothar.
but i am getting this error message once i run the above query.
very interesting, I used the above query on three different servers and each time it give me different error.
1- ''(ITW1)
2 -[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near select
3- [Microsoft][ODBC Driver 13 for SQL Server][SQL Server] invalid column name OnOrder.
Hi,
your query will not work since you are starting from Invoices / Credit Notes. As this is the starting table, any item which is not present on them will not be shown. You need to have OITM as starting table, and then left join from OITM to INV1 / RIN1 (inner join will also remove any items which are not present on the documents).
Lothar gave you a good solution on this, using subselect for getting INV / RIN data into your select, I just wanted to clarify what is wrong so that you learn for next time.
BR,
Matija
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thank you Matija.
i tried following the steps you reffered above. but i am still missing the those SKU for which i have stocks but there is no invoice history. here is the revised code:
SELECT A.[ItemCode], A.[ItemName], A.[OnHand], A.[IsCommited], A.[OnOrder], isnull(SUM(A.[Quantity]),0) as 'Quantity', A.[Price]
FROM (
SELECT
T0.[ItemCode], T0.[ItemName], isnull(T5.[OnHand],0) [OnHand], isnull(T5.[IsCommited],0) [IsCommited], isnull(T5.[OnOrder],0) [OnOrder], isnull(SUM(T2.[Quantity]),0) as 'Quantity', T1.[Price]
FROM
OITM T0 LEFT JOIN ITM1 T1 ON T0.[ItemCode] = T1.[ItemCode] LEFT JOIN INV1 T2 ON T0.[ItemCode] = T2.[ItemCode] LEFT JOIN OINV T3 ON T2.[DocEntry] = T3.[DocEntry] LEFT JOIN OMRC T4 ON T0.[FirmCode] = T4.[FirmCode] LEFT JOIN OITW T5 ON T0.[ItemCode] = T5.[ItemCode]
WHERE
T4.[FirmName] = 'ABC' and T5.[WhsCode] = '100' and T1.[PriceList] = '2' and T3.[CANCELED] = 'N'
GROUP BY T0.[ItemCode], T0.[ItemName], T5.[OnHand], T5.[IsCommited], T5.[OnOrder], T1.[Price]
Union ALL
SELECT
T0.[ItemCode], T0.[ItemName], isnull(T5.[OnHand],0) [OnHand], isnull(T5.[IsCommited],0) [IsCommited], isnull(T5.[OnOrder],0) [OnOrder], isnull(SUM(T2.[Quantity])*-1,0) as 'Quantity', T1.[Price]
FROM
OITM T0 LEFT JOIN ITM1 T1 ON T0.[ItemCode] = T1.[ItemCode] LEFT JOIN RIN1 T2 ON T0.[ItemCode] = T2.[ItemCode] LEFT JOIN ORIN T3 ON T2.[DocEntry] = T3.[DocEntry] LEFT JOIN OMRC T4 ON T0.[FirmCode] = T4.[FirmCode] LEFT JOIN OITW T5 ON T0.[ItemCode] = T5.[ItemCode]
WHERE
T4.[FirmName] = 'ABC' and T5.[WhsCode] = '100' and T1.[PriceList] = '2' and T3.[CANCELED] = 'N'
GROUP BY T0.[ItemCode], T0.[ItemName], T5.[OnHand], T5.[IsCommited], T5.[OnOrder], T1.[Price] ) A
GROUP BY
A.[ItemCode], A.[ItemName], A.[OnHand], A.[IsCommited], A.[OnOrder], A.[Price]
User | Count |
---|---|
91 | |
7 | |
7 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.