Date Filter not proper working

Akhter HUssain

6y720SQL

I have one table DispatchBM which column are

i have to join DispatchBM to Dispatch_BID which column are below

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.