Opening qty and Opening Weight,add in query?

Analyst_SQL 3,551 Reputation points
2021-01-15T11:57:19.977+00:00

I want to add opening qty and Opening Balance in below query,mean that i want yesterday data in Opening,then carry forward ,means today closing will be next day opening.

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 ,Del 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',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);  
  
  
  
  
  
;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)  
          
    -- 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,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  

@EchoLiu-MSFT @Tom Cooper @MelissaMa-MSFT

57137-sbstk.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,977 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,652 questions
{count} votes

Accepted answer
  1. EchoLiu-MSFT 14,581 Reputation points
    2021-01-18T07:23:41.283+00:00

    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:
    57417-image.png

    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.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.