Modify query ,Opening is not display

Question

i am trying to display of item (Shoes) opening ,but it is not displaying because it did not get insert into table (Dispatch_BD) that why it is not getting display in Opening .

Shoes opening will be 966,which is not getting display

Below is query

Declare @Startdate date='2021-09-15'  
Declare @Endate date='2021-09-15'  
  
;with cte1 as   
(select  I.CodeItem as Code,I.Descriptionitem  as Articale ,C.CName  as itecat from ItemMasterFile I  
inner  join Catagory c on c.CID=I.CID  
  
where Packsize='Bigbale'),cte2 as  
(select b.CodeItem as code,c.CName as Category,isnull(sum(B.Bpqty),0) as Qty,isnull(sum(B.Bweight ),0)as Weight  from Bigbalprd  B    
inner  join Catagory c on c.CID=b.CID  
  
where b.delID is null and Status='scn1' and (b.DID is null or b.DID=1) and (b.trans is null or b.Trans='b')   
group by b.CodeItem,c.CName)  
  
  
,cte3 as (select b.CodeItem as code,c.CName as Category,isnull(sum(B.Bpqty),0) as Qty,isnull(sum(B.Bweight ),0)as Weight  
  
  from Bigbalprd  B    
inner  join Catagory c on c.CID=b.CID  
  
where   b.EntryDate between @StartDate and @Endate and b.delID is null and Status='scn1' and (b.trans is null or b.Trans='b')   
  
  
  
  
group by b.CodeItem,c.CName  )  
  
,cte4 as ( select  dd.codeitem code,sum(DD.qty) Out_qt,sum(DD.Bweight) Out_wt  
  from Dispatch_BD DD inner join DispatchBM DM on DM.DID=DD.DID   
  inner join ItemMasterFile I on I.CodeItem=DD.codeitem  
  where   DM.date between @StartDate and @Endate and DD.Delidd is  null  
  group by dd.codeitem  
  )  
  ,cte5 as (  
  
  
  
select codeitem code,sum(d.qty)Dispatchqty,sum(d.Bweight)Disptwt from DispatchBM M inner join Dispatch_BD D on D.DID=M.DID and M.Del is null and D.Delidd is null and M.date<GETDate() -1  
group by codeitem  
  
    
  )  
  ,cte6 as  
  (  
    
  
  
select CodeItem code, (sum(B.Bpqty)) Qty,isnull(sum(B.Bweight ),0)as Weight from Bigbalprd B   
where  b.EntryDate <GETDATE()-1  and delID is null and (Trans is null or Trans='b')  
group by CodeItem  
    
  )  
select isnull(cte2.Category,itecat) as Category,Articale ,isnull(cte6.Qty-cte5.Dispatchqty,0)Opening_qty,isnull(cte6.Weight- cte5.Disptwt,0) Opening_weight ,cte3.Qty,cte3.Weight  
  
,cte4.Out_qt,cte4.Out_wt,isnull(cte2.Qty,0)Close_qty,isnull(cte2.Weight,0) Close_weight   
  
 from cte1 left join cte2 on cte2.code=cte1.Code   
   
 left  join cte3 on cte3.code=cte2.code  
 left join cte4 on cte4.code=cte3.code  
 left  join cte5 on cte5.code=cte2.code  
 left  join  cte6 on cte6.code=cte5.code  
   
 order by Category,Articale  

image

Answer 1

@MelissaMa-msft
Below is data

Create table #Category (CID int, Cname varchar(50))  
  
Create Table #itemmasterfile(CodeItem int,CID  int,Descriptionitem varchar(50),Packsize varchar(50))   
  CREATE TABLE #Probale (BID INT,CodeItem int,Weigth int,prdqty int,EntryDate date,DelID int,CID 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 ,Del int)  
  
  Insert into #Category Values(11, 'Pak')  
  Insert into #Category Values(12, 'LWC')  
  Insert into #Category Values(13, 'RAC')  
    
         
  INSERT INTO #itemmasterfile VALUES(1,11,'Adult Sweater','Small')   
  INSERT INTO #itemmasterfile VALUES(2,11,'HAEAVY SS','Small')    
  INSERT INTO #itemmasterfile VALUES(3,12,'LADIES MIX BLOUSES','Small')   
  INSERT INTO #itemmasterfile VALUES(4,11,'LAPU','Small')    
  INSERT INTO #itemmasterfile VALUES(5,13,'LAPU TROUSER','Small')    
  INSERT INTO #itemmasterfile VALUES(6,13,'LL #2','Small')    
  INSERT INTO #itemmasterfile VALUES(7,12,'N4S MXT','Small')    
  INSERT INTO #itemmasterfile VALUES(8,11,'Over Coat','Small')    
  INSERT INTO #itemmasterfile VALUES(9,12,'ST MIX T SHIRTS','Small')   
  INSERT INTO #itemmasterfile VALUES(10,13,'W / WIPER ','Small')    
  INSERT INTO #itemmasterfile VALUES(11,13,'WHITE PANTS','Small')    
          
      
   INSERT INTO #Probale VALUES(10006,4,270,1,'2020-10-20',null,11)    
  INSERT INTO #Probale VALUES(10007,5,270,1,'2020-10-20',null,13)    
  INSERT INTO #Probale VALUES(10008,6,270,1,'2020-10-20',null,13)    
          
           
  INSERT INTO #Probale VALUES(10000,1,270,1,'2020-10-21',null,11)   
  INSERT INTO #Probale VALUES(10001,2,270,1,'2020-10-21',null,11)    
  INSERT INTO #Probale VALUES(10002,3,270,1,'2020-10-21',null,12)   
  INSERT INTO #Probale VALUES(10003,4,270,1,'2020-10-21',null,11)    
  INSERT INTO #Probale VALUES(10004,5,270,1,'2020-10-21',null,13)    
  INSERT INTO #Probale VALUES(10005,6,270,1,'2020-10-21',null,13)  
    INSERT INTO #Probale VALUES(10005,6,270,1,'2020-10-21',null,13)  
     INSERT INTO #Probale VALUES(10005,6,270,1,'2020-10-21',null,12)  
         
      
  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',null);  
  insert into #Dispatch_SD values(11,4,1002,10003,1,270,null)  
  insert into #Dispatch_SD values(12,5,1002,10004,1,270,null);  

Note:
I will filter Data on 2020-10-21 date.
Opening_Qty and Opening _Weight will come from #Probale table (2020-10-20)
IN_Qty and IN_Weight will come from #Probale table (2020-10-21)
Out_Qty and Out_Weight will come from #DispatchSM and #Dispatch_SD table

Closing_Qty and Closing_Weight will come from (Opening_Qty) - (Out_Qty)=Closing_qty

Please also see LL #2 Item Category got change ,so it will display separately accordingly Category Input in Probale table.

Answer 2

Hi @Analyst_SQL ，

Closing_Qty and Closing_Weight will come from (Opening_Qty) - (Out_Qty)=Closing_qty

I have some concerns about above rule compared with your expected output.

Could you please provide more details about it? Thanks.

Right now I could perform the query like below and the closing_qty and Closing_weight are not the same as you provided.

Declare @startdate date='2020-10-21'  
Declare @enddate date='2020-10-21'  
  
;with openingcte as (  
select codeitem,cid,sum(Weigth) opening_weight,sum(prdqty) opening_qty   
from #Probale  
where EntryDate >=DATEADD(D,-1,@startdate) and EntryDate<@enddate  
group by codeitem,cid)  
,incte as (  
select codeitem,cid,sum(Weigth) in_weight,sum(prdqty) in_qty from #Probale  
where EntryDate between @startdate and @enddate  
group by codeitem,cid)  
,outcte as (  
select a.id,a.codeitem,sum(qty) out_qty,sum(weight) out_weight from #Dispatch_SD a  
left join #DispatchSM b   
on a.smid=b.smid  
where date between DATEADD(D,-1,@startdate) and @enddate  
group by a.id,a.codeitem)  
,cte as (  
select a.codeitem,b.cname,a.descriptionitem description   
,isnull(c.opening_qty,0) opening_qty,isnull(c.opening_weight,0) opening_weight,  
isnull(d.in_qty,0) in_qty,isnull(d.in_weight,0) in_weight,  
isnull(e.out_qty,0) out_qty,isnull(e.out_weight,0) out_weight  
from #ItemMasterFile a  
left join #Category b on a.CID=b.CID  
left join openingcte c on a.cid=c.cid and a.codeitem=c.codeitem  
left join incte d on a.cid=d.cid and a.codeitem=d.codeitem  
left join outcte e on a.codeitem=e.codeitem )  
,cte1 as (  
select * from cte   
union  
select c.codeitem,b.cname,c.descriptionitem,0,0,a.in_qty,a.in_weight,0,0  
from incte a  
left join #Category b on a.cid=b.cid  
left join #ItemMasterFile c on a.codeitem=c.codeitem  
where not exists (select 1 from cte d where d.description=c.descriptionitem and d.cname=b.cname))  
select cname,description ,opening_qty, opening_weight,in_qty,in_weight,out_qty, out_weight   
,opening_qty-out_qty closing_qty ,opening_weight-out_weight Closing_weight  
from cte1  

Best regards,
Melissa

---

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.

Answer 3

Hi @Analyst_SQL ，

Could you please provide more details about above row?

I tried to update my query according to your latest rule but failed with one row.

Please refer below:

Declare @startdate date='2020-10-21'  
Declare @enddate date='2020-10-21'  
  
;with openingcte as (  
select codeitem,cid,sum(Weigth) opening_weight,sum(prdqty) opening_qty   
from #Probale  
where EntryDate >=DATEADD(D,-1,@startdate) and EntryDate<@enddate  
group by codeitem,cid)  
,incte as (  
select codeitem,cid,sum(Weigth) in_weight,sum(prdqty) in_qty from #Probale  
where EntryDate between @startdate and @enddate  
group by codeitem,cid)  
,outcte as (  
select a.id,a.codeitem,sum(qty) out_qty  
,sum(weight) out_weight,date outdate  
from #Dispatch_SD a  
left join #DispatchSM b   
on a.smid=b.smid  
where date between DATEADD(D,-1,@startdate) and @enddate  
group by a.id,a.codeitem,date)  
,cte as (  
select a.codeitem,b.cname,a.descriptionitem description   
,isnull(c.opening_qty,0) opening_qty,isnull(c.opening_weight,0) opening_weight,  
isnull(d.in_qty,0) in_qty,isnull(d.in_weight,0) in_weight,  
isnull(e.out_qty,0) out_qty,isnull(e.out_weight,0) out_weight,e.outdate  
from #ItemMasterFile a  
left join #Category b on a.CID=b.CID  
left join openingcte c on a.cid=c.cid and a.codeitem=c.codeitem  
left join incte d on a.cid=d.cid and a.codeitem=d.codeitem  
left join outcte e on a.codeitem=e.codeitem   
)  
,cte1 as (  
select * from cte   
union  
select c.codeitem,b.cname,c.descriptionitem,0,0,a.in_qty,a.in_weight,0,0,null  
from incte a  
left join #Category b on a.cid=b.cid  
left join #ItemMasterFile c on a.codeitem=c.codeitem  
where not exists (select 1 from cte d where d.description=c.descriptionitem and d.cname=b.cname))  
,cte2 as (  
select codeitem,cname,description ,opening_qty, opening_weight,in_qty,in_weight  
,sum(out_qty) over (partition by cname,description) out_qty  
,sum(out_weight)  over (partition by cname,description) out_weight,  
CASE WHEN outdate=@startdate or outdate is null then opening_qty+in_qty-out_qty else 0 end closing_qty,  
CASE WHEN outdate=@startdate or outdate is null then opening_weight+in_weight-out_weight else 0 end Closing_weight,outdate  
from cte1)  
select cname,description ,opening_qty, opening_weight,in_qty,in_weight,out_qty,out_weight,closing_qty,Closing_weight   
from cte2 where outdate is null or outdate=@startdate  
order by codeitem  

Best regards,
Melissa

---

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.

Answer 4

Hi @Analyst_SQL ，

I reviewed your comments again and I could understanding your requirement.

Please refer below updated one with your sample data.

Declare @date date='2020-10-21'  
  
 ;with openingcte as (  
 select codeitem,cid,sum(Weigth) opening_weight,sum(prdqty) opening_qty   
 from #Probale  
 where EntryDate =DATEADD(D,-1,@date)  
 group by codeitem,cid)  
 ,incte as (  
 select codeitem,cid,sum(Weigth) in_weight,sum(prdqty) in_qty from #Probale  
 where EntryDate=@date  
 group by codeitem,cid)  
 ,outcte as (  
 select a.id,a.codeitem,sum(qty) out_qty  
 ,sum(weight) out_weight,date outdate  
 from #Dispatch_SD a  
 left join #DispatchSM b   
 on a.smid=b.smid  
 where date between DATEADD(D,-1,@date) and @date  
 group by a.id,a.codeitem,date)  
 ,cte as (  
 select a.codeitem,b.cname,a.descriptionitem description   
 ,isnull(c.opening_qty,0) opening_qty,isnull(c.opening_weight,0) opening_weight,  
 isnull(d.in_qty,0) in_qty,isnull(d.in_weight,0) in_weight,  
 isnull(e.out_qty,0) out_qty,isnull(e.out_weight,0) out_weight,e.outdate  
 from #ItemMasterFile a  
 left join #Category b on a.CID=b.CID  
 left join openingcte c on a.cid=c.cid and a.codeitem=c.codeitem  
 left join incte d on a.cid=d.cid and a.codeitem=d.codeitem  
 left join outcte e on a.codeitem=e.codeitem   
 )  
 ,cte1 as (  
 select * from cte   
 union  
 select c.codeitem,b.cname,c.descriptionitem,0,0,a.in_qty,a.in_weight,0,0,null  
 from incte a  
 left join #Category b on a.cid=b.cid  
 left join #ItemMasterFile c on a.codeitem=c.codeitem  
 where not exists (select 1 from cte d where d.description=c.descriptionitem and d.cname=b.cname))  
 ,cte2 as (  
 select codeitem,cname,description ,opening_qty, opening_weight,in_qty,in_weight  
 ,sum(out_qty) over (partition by cname,description) out_qty  
 ,sum(out_weight)  over (partition by cname,description) out_weight,  
 CASE WHEN outdate=@date or outdate is null then opening_qty+in_qty-out_qty else 0 end closing_qty,  
 CASE WHEN outdate=@date or outdate is null then opening_weight+in_weight-out_weight else 0 end Closing_weight,outdate  
 from cte1)  
 select cname,description ,opening_qty, opening_weight,in_qty,in_weight,out_qty,out_weight,closing_qty,Closing_weight   
 from cte2 where outdate is null or outdate=@date  
 order by codeitem  

Output:

If above is not working with your actual data, I strongly recommend you to post your actual data and expected output otherwise I do not know how to proceed with this query.

Thank you for understanding!

Best regards,
Melissa

---

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.