Skip to Content
0

Daily Stock Report

Apr 28, 2017 at 09:29 AM

58

avatar image
Former Member

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Rizki Wicaksana Apr 28, 2017 at 10:34 AM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi,

My Client requirement is, he has to know:-

1. what is the Instock at present.

2.what is the Outqty

For that particular Date.

Your changes I applied but not working.

0