1,357
I have create a report of Inventory stock in hand of each item year and period wise and also filtered by LOCNCODE.
I can find a view of this from the following LINK:
https://mahmoudsaadi.blogspot.com/2014/01/hitb-per-period-cumulative-inventory.html
But somehow this report is not giving exact numbers if I verify this report with any item.
Anyone can please provide the complete query of above requirement?
*This post is locked for comments
Hi Beat,
Yes, you got it right. TWO demo company data belongs to 2027 year and I have hard quoted the year 2018.
Although it gives me the results of most LocationCodes because here I am using Location Codes as Projects, I see that the fiscal year 2012 of my company contain 10 periods from 1/1/2012 to 10/31/2012 and fiscal year 2013 contain 8 periods from 11/1/2012 to 6/1/2013. There where the problem starts. The above query seems to be parsing dates from the transactions and making them into periods. If I got the query correct? So if any data falling in above period will not be added and thus the result will be garbage.
What are your thoughts on this?
Hi Fahad,
I'm glad if that script worked for you.. Just by curiosity I ran this against the TWO (Fabrikam) database and it returns zero records.. which is quite strange, considering the "Where" clause was left out ..
Somehow there must a JOIN that's preventing to return proper data.
EDIT: figured out that the current date is playing a role in your script.. and Fabrikam's transactions are all in the future since the new transaction dates are in 2027 for the demo company.
Please see below Ma'am,
SELECT SEE.YEAR ,
SEE.MONTH ,
CONVERT(DATETIME, CAST(SEE.YEAR AS VARCHAR(4)) + '-'
+ CAST(SEE.MONTH AS VARCHAR(2)) + '-' + CAST('01' AS VARCHAR(2)), 120) AS LongDate ,
SEE.ItemNumber AS 'Item Number' ,
IV.ITEMDESC AS 'Item Description' ,
SEE.LocationCode AS 'Location Code' ,
ST.LOCNDSCR AS 'Location Description' ,
SEE.ThisMonthQTY AS 'This Month Quantity' ,
SEE.QTYBalance AS 'Quantity Balance' ,
SEE.ThisMonthCost AS 'This Month Cost' ,
SEE.CostBalance
FROM ( SELECT A.[YER] AS 'YEAR' ,
A.[MOT] AS 'Month' ,
A.[ITM] AS 'ItemNumber' ,
A.[LOCNCODE] AS 'LocationCode' ,
ISNULL(A.[QTY], 0) AS ThisMonthQTY ,
SUM(ISNULL(B.[QTY], 0)) AS QTYBalance ,
ISNULL(A.[Cost], 0) AS ThisMonthCost ,
SUM(ISNULL(B.[COST], 0)) AS CostBalance
FROM ( SELECT E.[ITEMNMBR] AS ITM ,
E.[Year1] AS YER ,
E.[PeriodID] AS MOT ,
ISNULL(F.[QTY], 0) AS QTY ,
ISNULL(F.COST, 0) AS Cost ,
E.LOCNCODE ,
( E.[Year1] * 365 ) + ( E.[PeriodID] * 30 ) AS DT
FROM ( SELECT C.[ITEMNMBR] ,
C.LOCNCODE ,
D.[Year1] ,
D.[PeriodID]
FROM ( SELECT
DISTINCT
Year1 ,
PeriodID ,
DATEADD(mm, 1,
CONVERT(DATETIME, '01/'
+ CAST(PeriodID AS VARCHAR(2))
+ '/'
+ CAST(YEar1 AS VARCHAR(4)), 103)) AS DT
FROM SY40100 AS A
WHERE PeriodID <> 0
) AS D
INNER JOIN ( SELECT
B.[ITEMNMBR] ,
[LOCNCODE] ,
MIN(B.[DOCDATE]) AS DT
FROM
[SEE30303] AS B
GROUP BY B.[ITEMNMBR] ,
[LOCNCODE]
) AS C ON D.DT > C.DT
) AS E
LEFT OUTER JOIN ( SELECT YEAR([DOCDATE]) AS YER ,
MONTH([DOCDATE]) AS MOT ,
[ITEMNMBR] AS ITM ,
SUM([TRXQTYInBase]) AS QTY ,
SUM([EXTDCOST]) AS COST ,
[LOCNCODE]
FROM SEE30303
GROUP BY YEAR([DOCDATE]) ,
MONTH([DOCDATE]) ,
[ITEMNMBR] ,
[LOCNCODE]
) AS F ON E.ITEMNMBR = F.ITM
AND E.PeriodID = F.MOT
AND E.Year1 = F.YER
AND E.locncode = f.locncode
) AS A
LEFT OUTER JOIN ( SELECT YEAR([DOCDATE]) AS YER ,
MONTH([DOCDATE]) AS MOT ,
( YEAR([DOCDATE]) * 365 )
+ ( MONTH([DOCDATE]) * 30 ) AS DT ,
[ITEMNMBR] AS ITM ,
SUM([TRXQTYInBase]) AS QTY ,
SUM([EXTDCOST]) AS COST ,
LOCNCODE
FROM SEE30303
GROUP BY YEAR([DOCDATE]) ,
MONTH([DOCDATE]) ,
[ITEMNMBR] ,
LOCNCODE
) AS B ON A.[ITM] = B.[ITM]
AND A.[DT] >= B.[DT]
AND A.LOCNCODE = B.LOCNCODE
GROUP BY A.[YER] ,
A.[MOT] ,
A.[ITM] ,
ISNULL(A.[LOCNCODE], '') ,
ISNULL(A.[QTY], 0) ,
ISNULL(A.[Cost], 0) ,
A.LOCNCODE
) AS SEE
LEFT OUTER JOIN IV00101 AS IV ON SEE.ItemNumber = IV.ITEMNMBR
LEFT OUTER JOIN IV40700 AS ST ON SEE.LocationCode = ST.LOCNCODE
-- WHERE SEE.ItemNumber = 'contac036 ' AND ***If you want to filter records on specific item***
-- SEE.LocationCode IN ('NSNTPO&M','HWAUFNSTH','ZTTPO&MSTH','ZTEZONGO&M') ***If Locationcode filter is required***
AND CONVERT(DATETIME, CAST(SEE.YEAR AS VARCHAR(4)) + '-'
+ CAST(SEE.MONTH AS VARCHAR(2)) + '-' + CAST('01' AS VARCHAR(2)), 120) <= CONVERT(DATETIME, CAST('2018' AS VARCHAR(4))
+ '-' + CAST('12' AS VARCHAR(2)) + '-'
+ CAST('01' AS VARCHAR(2)), 120)
ORDER BY ItemNumber, YEAR, MONTH, LocationCode
Thanks for the reply Beat Bucher. I think now I am able to fetch the desired information from another query (query of a procedure) provided by Mr. Mahmoud Saadi. I had to add few things in the query to get the desired data. If anyone willing to know I can paste the query here.
Hi Fahad,
Maybe the original author of the blog post wants to chime in (Mahmoud Saadi), but have you checked if the data that is in the HITB table (SEE30303) is consistent with your inventory status & transactions ? You may have to reset the HITB status to re-populate the data..
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (