Hi @Analyst_SQL ,
Please refer to:
;with cte1 as
(SELECT i.CodeItem, i.Descriptionitem as Artical,i.ALID as Short,Isnull(Sum(D.qty),0) OUT_QTY,Isnull(Sum(D.Weight),0) OUT_weight
FROM #Dispatch_SD D
inner join #DispatchSM M on M.SMID=D.SMID
right join #itemmasterfile i on i.CodeItem=D.codeitem
and M.date between '2020-10-21' and '2020-10-21'
where D.Del is null and M.Del is null
group by i.Descriptionitem,i.ALID,i.CodeItem)
,cte2 as
(SELECT Distinct i.CodeItem, i.Descriptionitem AS Artical,i.ALID as Short , ISNull(SUM(P.Weigth),0) AS IN_Weight, ISNull(SUM(P.prdqty),0)
IN_QTY
FROM #ItemMasterFile i LEFT outer JOIN
#Probale P ON i.CodeItem = P.CodeItem
and P.EntryDate between '2020-10-21' and '2020-10-21'
where (i.Packsize = 'Small') and delID is null
GROUP BY i.CodeItem,i.Descriptionitem, P.prdqty,i.ALID)
,cte3 as
(select f.Code,f.Name,f.Short_Name,(f.Probale_QTY-f.Dispatch_QTY) as [Balance],(f.Probale_weight-f.Dispatch_Weight) as [W_Balance] from (
select e.Code,e.Name,e.Short_Name,isnull(min(e.[Bigbale_QTY]),0) as [Probale_QTY],isnull(min(e.[Probale_Weight]),0) as [Probale_weight],
isnull(sum(c.QTY),0) as [Dispatch_QTY],isnull(sum(c.Weight),0) as [Dispatch_Weight] from (
select a.Descriptionitem as Name,a.ALID as Short_Name, (a.CodeItem) as Code,isnull(sum(P.prdqty),0) as [Bigbale_QTY],
isnull(sum(P.Weigth),0) as [Probale_Weight]
from #itemmasterfile a
left join #Probale P on a.CodeItem=P.CodeItem
--and b.EntryDate between '2020-10-13' and '2020-10-19'
where
a.Packsize ='Small' and P.delID is null --and (b.trans is null or b.Trans='b')
group by a.Descriptionitem,a.ALID,a.CodeItem) e
left join #Dispatch_SD c on e.Code=c.CodeItem
and c.Del is null
left join #DispatchSM M on M.SMID=c.SMID
-- and M.date between '2020-10-13' and '2020-10-19'
group by e.Name ,e.Short_Name,e.Code )
f)
, cte12 as
(SELECT i.CodeItem, i.Descriptionitem as Artical,i.ALID as Short,Isnull(Sum(D.qty),0) OUT_QTY,Isnull(Sum(D.Weight),0) OUT_weight
FROM #Dispatch_SD D
inner join #DispatchSM M on M.SMID=D.SMID
right join #itemmasterfile i on i.CodeItem=D.codeitem
and M.date between '2020-10-20' and '2020-10-20'
where D.Del is null and M.Del is null
group by i.Descriptionitem,i.ALID,i.CodeItem)
,cte22 as
(SELECT Distinct i.CodeItem, i.Descriptionitem AS Artical,i.ALID as Short , ISNull(SUM(P.Weigth),0) AS IN_Weight, ISNull(SUM(P.prdqty),0)
IN_QTY
FROM #ItemMasterFile i LEFT outer JOIN
#Probale P ON i.CodeItem = P.CodeItem
and P.EntryDate between '2020-10-20' and '2020-10-20'
where (i.Packsize = 'Small') and delID is null
GROUP BY i.CodeItem,i.Descriptionitem, P.prdqty,i.ALID)
,cte32 as(select c22.CodeItem,(c22.IN_QTY-c12.OUT_QTY )Opening_Qty,
(c22.IN_Weight-c12.OUT_weight )Opening_Balance
from cte12 c12
right join cte22 c22 on c12.CodeItem=c22.CodeItem)
-- select c2.Artical,c2.IN_QTY ,c2.IN_Weight, c1.Artical,c1.OUT_QTY,c1.OUT_weight,c3.Name,c3.Balance,c3.W_Balance
select c2.CodeItem,c2.Artical,c2.Short,c32.Opening_Qty,c32.Opening_Balance,c2.IN_QTY ,c2.IN_Weight, c1.OUT_QTY,c1.OUT_weight,c3.Balance,c3.W_Balance
from cte1 c1
--right join cte2 c2 on c1.Artical=c2.Artical
--right join cte3 c3 on c2.Artical=c3.[Name]
right join cte2 c2 on c1.CodeItem=c2.CodeItem
right join cte3 c3 on c2.CodeItem=c3.Code
right join cte32 c32 on c2.CodeItem=c32.CodeItem
Output:
If you have any question, please feel free to let me know.
Regards
Echo
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.