Skip to Content
0
Nov 02, 2012 at 07:18 AM

Stock Turnover Analysis query

260 Views

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?

Attachments

results.jpg (346.1 kB)