User-367318540 posted
When I insert in table #DispatchSM Column Del value 1,then it display Null,i want Display 0 on Null.
Create Table #itemmasterfile(CodeItem int,ALID varchar(50),Descriptionitem varchar(50),Packsize varchar(50))
CREATE TABLE #Probale (BID INT,CodeItem int,Weigth int,prdqty int,EntryDate date,DelID int)
Create table #DispatchSM (SMID int,date date,Del int)
Create Table #Dispatch_SD(ID int,codeitem int,SMID int,BID int,qty int ,Weight int ,Delidd int)
INSERT INTO #itemmasterfile VALUES(1,'AS','Adult Sweater','Small')
INSERT INTO #itemmasterfile VALUES(2,'HV','HAEAVY SS','Small')
INSERT INTO #itemmasterfile VALUES(3,'LMB','LADIES MIX BLOUSES','Small')
INSERT INTO #itemmasterfile VALUES(4,'LP','LAPU','Small')
INSERT INTO #itemmasterfile VALUES(5,'LT','LAPU TROUSER','Small')
INSERT INTO #itemmasterfile VALUES(6,'L2','LL #2','Small')
INSERT INTO #itemmasterfile VALUES(7,'NS','N4S MXT','Small')
INSERT INTO #itemmasterfile VALUES(8,'OC','Over Coat','Small')
INSERT INTO #itemmasterfile VALUES(9,'SS','ST MIX T SHIRTS','Small')
INSERT INTO #itemmasterfile VALUES(10,'WW','W / WIPER ','Small')
INSERT INTO #itemmasterfile VALUES(11,'WP','WHITE PANTS','Small')
INSERT INTO #Probale VALUES(10006,4,270,1,'2020-10-20',null)
INSERT INTO #Probale VALUES(10007,5,270,1,'2020-10-20',null)
INSERT INTO #Probale VALUES(10008,6,270,1,'2020-10-20',null)
INSERT INTO #Probale VALUES(10000,1,270,1,'2020-10-21',null)
INSERT INTO #Probale VALUES(10001,2,270,1,'2020-10-21',null)
INSERT INTO #Probale VALUES(10002,3,270,1,'2020-10-21',null)
INSERT INTO #Probale VALUES(10003,4,270,1,'2020-10-21',null)
INSERT INTO #Probale VALUES(10004,5,270,1,'2020-10-21',null)
INSERT INTO #Probale VALUES(10005,6,270,1,'2020-10-21',null)
Insert into #DispatchSM values(1001,'2020-10-20',null);
insert into #Dispatch_SD values(11,4,1001,10008,1,270,null)
insert into #Dispatch_SD values(12,5,1001,10006,1,270,null);
Insert into #DispatchSM values(1002,'2020-10-21',1);
insert into #Dispatch_SD values(11,4,1002,10003,1,270,null)
insert into #Dispatch_SD values(12,5,1002,10004,1,270,null);
;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.Delidd 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
where
a.Packsize ='Small' and P.delID is null
group by a.Descriptionitem,a.ALID,a.CodeItem) e
left join #Dispatch_SD c on e.Code=c.CodeItem
and c.Delidd is null
left join #DispatchSM M on M.SMID=c.SMID
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 <'2020-10-21'
where D.Delidd 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 <'2020-10-21'
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.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.CodeItem=c2.CodeItem
right join cte3 c3 on c2.CodeItem=c3.Code
right join cte32 c32 on c2.CodeItem=c32.CodeItem
Output
https://ibb.co/RPDp6YC
