Announcements
No record found.
i need to find opening and closing quantity through sql any suggest how can i find
*This post is locked for comments
Hi,
What do you mean by opening and closing quantity exactly? Can you elaborate?
Best regards
Ludwig
I NEED TO FIND ONHAND QUANTITY BEFORE FROM DATE FROM INVENTTRANS
OPENING STOCK OF THE PRODUCT DEPENDING UPON FROM DATE SPECIFIED IN THE PARAMETER
The only thing that might come close to your requirement is the inventory value report.
Have you tested whether this report includes all the information that you need?
Best regards,
Hi Muhammad,
Try (and test) this:
DECLARE @Company nvarchar(4)
DECLARE @Date datetime
DECLARE @Partition bigint
SELECT @Partition = Par.RECID FROM dbo.PARTITIONS AS Par WHERE Par.PARTITIONKEY = 'initial'
SET @Company='USMF'
SET @Date='2017-01-01 00:00:00.000'
SELECT IT.ITEMID, sum(ITr.QTY) AS 'Quantity', sum(CASE WHEN ITr.STATUSISSUE = 2 THEN ITr.COSTAMOUNTPHYSICAL WHEN ITr.STATUSRECEIPT = 2 THEN ITr.COSTAMOUNTPHYSICAL ELSE ITr.COSTAMOUNTPOSTED + ITr.COSTAMOUNTADJUSTMENT END) AS 'Value'
FROM dbo.INVENTTABLE AS IT
JOIN dbo.INVENTTRANS AS ITr ON IT.PARTITION = ITr.PARTITION AND IT.DATAAREAID = ITr.DATAAREAID AND IT.ITEMID = ITr.ITEMID
WHERE ITr.STATUSISSUE <=2 and ITr.STATUSRECEIPT <=2 AND ITr.DATAAREAID = @Company AND ITr.PARTITION = @Partition AND ITr.DATEPHYSICAL <= @Date
GROUP BY IT.ITEMID
Under review
Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.
Congratulations to our 2026 Super Stars!
We are thrilled to have these Champions in our Community!
These are the community rock stars!
Stay up to date on forum activity by subscribing.
Joris dG 5
Andrew Jones a1x 2
Basit 1
