Akhter HUssain

Akhter HUssain

  • 712
  • 1.3k
  • 103.2k

Date Filter not proper working

Feb 18 2020 4:21 AM
I have one table DispatchBM which column are
  1. CREATE TABLE #DispatchBM (DID INT,CustomerID int,entrydate date)   
 i have to join DispatchBM to Dispatch_BID which column are below
  1. 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
  1. select f.Category,f.Name,(f.Bigbale_QTY-f.Dispatch_QTY) as [Balance],(f.Bigbale_weight-f.Dispatch_Weight) as [W_Balance] from(  
  2. 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 (  
  3. 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]  
  4. from ItemMasterFile a  
  5. inner join Catagory ca on ca.CID=a.CID  
  6. left join Bigbalprd b on a.CodeItem=b.CodeItem  
  7.  where a.Packsize ='bigbale' and b.delID is null  and (b.trans is null or b.Trans='b')  
  8.   group by a.Descriptionitem, ca.CName)e  
  9. left outer join DispatchBM M on M.DID=c.DID  
  10. and  M.date between '01/01/2019' and '01/12/2019'  
  11. group by e.Name,e.Category  
  12. )f

Answers (2)