INVENTORY REPORT LINK WITH OITB

[shikin]

Hi all, I have a query where i want to include OITB and display out itmsgrapnam. Can anyone look at my coding below and show me how to link with OITB :

Declare @FromDate Datetime
 Declare @ToDate Datetime
 Declare @Whse nvarchar(10)
 select @FromDate = min(S0.Docdate) from dbo.OINM S0 where S0.Docdate = '20210701'
 select @ToDate = max(S1.Docdate) from dbo.OINM s1 where S1.Docdate = '20210702'
 select @Whse = Max(s2.Warehouse) from dbo.OINM S2 Where S2.Warehouse = 'P2'
Select @Whse as 'Warehouse', a.Itemcode, max(a.Dscription) as ItemName,
 sum(a.OpeningBalance) as OpeningBalance,
 sum(a.INq) as 'IN',  sum(a.OUT) as OUT,((sum(a.OpeningBalance) + sum(a.INq)) - Sum(a.OUT)) as Closing ,
 
(Select i.InvntryUom from OITM i where i.ItemCode=a.Itemcode) as UOM
 from(
Select N1.Warehouse, N1.Itemcode, N1.Dscription, (sum(N1.inqty)-sum(n1.outqty))
 as OpeningBalance, 0 as INq, 0 as OUT 
 From dbo.OINM N1  
 Where N1.DocDate < @FromDate and N1.Warehouse = @Whse 
 Group By N1.Warehouse,N1.ItemCode,
 N1.Dscription 
 
 Union All 
 select N1.Warehouse, N1.Itemcode, N1.Dscription, 0 as OpeningBalance,
 sum(N1.inqty) , 0 as OUT 
 From dbo.OINM N1 
  
 Where N1.DocDate >= @FromDate and N1.DocDate <= @ToDate
 and N1.Inqty >0 and N1.Warehouse = @Whse 
 Group By N1.Warehouse,N1.ItemCode,N1.Dscription
 
 Union All 
 
 select N1.Warehouse, N1.Itemcode, N1.Dscription, 0 as OpeningBalance, 0 , sum(N1.outqty) as OUT
 From dbo.OINM N1  
 Where N1.DocDate >= @FromDate and N1.DocDate <=@ToDate and N1.OutQty > 0
 and N1.Warehouse = @Whse Group By N1.Warehouse,N1.ItemCode,N1.Dscription) a, 
 
 dbo.OITM I1


  where a.ItemCode=I1.ItemCode


 Group By a.Itemcode 
 Having sum(a.OpeningBalance) + sum(a.INq) + sum(a.OUT) > 0 
 Order By a.Itemcode

[chubchub · Accepted solution]

to link inventory to OITB is ItmsGrpCod

sample:

OITM T0

LEFT JOIN OITB T2 on T0.ItmsGrpCod = T2.ItmsGrpCod