cancel
Showing results for 
Search instead for 
Did you mean: 

Daily Stock Report

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (1)

Answers (1)

0 Kudos

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

Former Member
0 Kudos

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.