Hi,
I wanted to know how to modify the query below to give me opening stock, receipts, issues and closing stock such that
opening stock + receipts - issues = closing stock.
SELECT DISTINCT T0.ItmsGrpNam, T2.DocDate, T2.Warehouse, T1.ItemCode, T1.ItemName,
(SUM(T1.OnHand) - SUM(T2.InQty)) AS 'Opening Stock', SUM(T2.InQty) AS 'Receipts',
SUM(T2.OutQty) AS 'Issues', (SUM(T1.OnHand) - SUM(T2.InQty)) + SUM(T2.InQty) - SUM(T2.OutQty)
AS 'Closing Stock' FROM OITB T0 INNER JOIN OITM T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod
INNER JOIN OINM T2 ON T1.ItemCode = T2.ItemCode
GROUP BY T0.ItmsGrpNam, T2.DocDate, T1.ItemCode, T1.ItemName, T2.Warehouse, T2.InQty , T2.OutQty,
T1.OnHand
ORDER BY T1.ItemCode
I run it in crystal where a user can enter parameters of start and end date, select itemgroupcode and warehouse as below
{Command.DocDate} >= {?StartDate} and
{Command.DocDate} <= {?EndDate} and
{Command.ItmsGrpNam} = {?@Select ItmsGrpNam From OITB order by ItmsGrpNam }
and {Command.Warehouse} = {?@Select WhsCode From OWHS}
Sample sql results are in the attached file.
From the results, I would like the Item code to only appear once such that I can see only one opening stock, receipts per warehouse for the given
dates. How do I achieve this?