Announcements
No record found.
What is the sql to calculate Physical amount posted in the inventory value report?
*This post is locked for comments
you can use view inventvaluereportview and set condtions and grouping based on demand
Thanks Crispin
Hello Jeremy,
If your question is answered, please be so kind and verify the answer.
Many thanks and best regards,
Ludwig
Crispin,
That get me the overall quantity and amount. I am looking to calculate the Financial quantity and Financial amount also.
What would be the filtering for that?
Cheers,
Jeremy
SELECT-- V.UNIONALLBRANCHID,
sum(V.qty) FinancialQty
,SUM(V.AMOUNT) FinancialAmount
FROM
[INVENTVALUETRANSUNIONALL] V JOIN INVENTTABLE i ON i.[PARTITION] = v.[PARTITION]
AND i.DATAAREAID = v.DATAAREAID
AND i.ITEMID = v.ITEMID
WHERE UNIONALLBRANCHID IN (5,6) AND TRANSDATE<=CAST('2017-11-30' AS DATE) AND i.ITEMTYPE = 0
To get Physical Quantity and Amount
sum(V.qty) PhysicalQty
,SUM(V.AMOUNT) PhysicalAmount
WHERE UNIONALLBRANCHID NOT IN (5,6) AND TRANSDATE<=CAST('2017-11-30' AS DATE) AND i.ITEMTYPE = 0
To get the Overall Inventory Summary
SELECT
v.[PARTITION],
v.DATAAREAID,
SUM(v.QTY) AS QTY,
SUM(v.AMOUNT) AS AMOUNT
FROM INVENTVALUEREPORTVIEW v
JOIN INVENTTABLE i ON i.[PARTITION] = v.[PARTITION]
WHERE v.TRANSDATE <= cast('2017-11-30' as date)
AND i.ITEMTYPE = 0
and v.ISPOSTED=1
GROUP BY
v.DATAAREAID--,
ORDER BY 1, 2
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
GL-01081504-0 1
