I have one table DispatchBM which column are
- CREATE TABLE #DispatchBM (DID INT,CustomerID int,entrydate date)
i have to join DispatchBM to Dispatch_BID which column are below
- CREATE TABLE (Dispatch_BD (ID int,codeitem int,qty int,BID int,Bweight int)
Now i want to apply date filter from table DispatchBM Entrydate in below mentioned query ,i tried but not giving exact result
- select f.Category,f.Name,(f.Bigbale_QTY-f.Dispatch_QTY) as [Balance],(f.Bigbale_weight-f.Dispatch_Weight) as [W_Balance] from(
- select e.Category,e.Name,isnull(min(e.[Bigbale_QTY]),0) as [Bigbale_QTY],isnull(min(e.[Bigbale_weight]),0) as [Bigbale_weight],isnull(sum(c.QTY),0) as [Dispatch_QTY],isnull(sum(c.Bweight),0) as [Dispatch_Weight] from (
- select ca.CName as Category,a.Descriptionitem as Name,min(a.CodeItem) as CodeItem,isnull(sum(b.Bpqty),0) as [Bigbale_QTY],isnull(sum(b.Bweight),0) as [Bigbale_Weight]
- from ItemMasterFile a
- inner join Catagory ca on ca.CID=a.CID
- left join Bigbalprd b on a.CodeItem=b.CodeItem
- where a.Packsize ='bigbale' and b.delID is null and (b.trans is null or b.Trans='b')
- group by a.Descriptionitem, ca.CName)e
- left outer join DispatchBM M on M.DID=c.DID
- and M.date between '01/01/2019' and '01/12/2019'
- group by e.Name,e.Category
- )f