on 04-28-2017 10:29 AM
Dear Experts,
I am stuck in Daily Stock Reports:-Requirement
1.ItemCode -----> OITM Table
2.ItemName----->OITM Table
3.In Stock ---> As on Date----->OITW Table
4.Billed Qty--->As on Date------>OINM Table
Parameter should be Date.
Kindly please help me in this report by joining these tables.
I am getting wrong stock if I execute below report.
select Itemcode,Itemname,sum([In Stock]) as 'In Stock',[Manufacturer Name],sum([Billed Qty]) as 'Billed Qty' from ( SELECT t4.docdate,t3.whscode, T2.[ItemCode], T2.[ItemName],T3.[OnHand] as 'In Stock', T5.[FirmName] AS 'Manufacturer Name',t4.OutQty as 'Billed Qty' FROM [dbo].[ITM1] T1 INNER JOIN [dbo].[OITM] T2 ON T1.ItemCode = T2.ItemCode Inner join oitw t3 on t3.itemcode = t1.itemcode INNER JOIN [dbo].[omrc] T5 ON T2.[FirmCode] = T5.[FirmCode] Inner Join OINM T4 on T4.ItemCode=T2.ItemCode AND T3.WhsCode = T4.Warehouse where T3.Onhand > 0 and t4.DocDate between '20170428' and '20170428' and t3.whscode in ('01','CMS','DMS','RMS') GROUP BY t4.docdate,t3.whscode, T2.[ItemCode], T2.[ItemName],T3.[OnHand] ,T5.[FirmName],t4.OutQty) T8 Group By Itemcode,Itemname,[Manufacturer Name]
Thanks in Advance
Hi,
Based on your query you won't get In stock as on date because T3.[OnHand] is on hand in present. To get actual stock as on date, you need to change T3.[Onhand] with sum of InQty-OutQty in OINM Table.
Hope this helps 🙂
Regards,
Rizki Wicaksana
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
86 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.