Skip to Content
avatar image
Former Member

Daily Stock Report

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Apr 28, 2017 at 10:34 AM

    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

    Add comment
    10|10000 characters needed 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.