Running Opening and Closing query

Question

Below is data

     CREATE TABLE #Containerno(CID INT,Contno VARCHAR(50),ConWeight nvarchar(50),Entrydate date ,Cat_ID int,DelID int)    
                     
    CREATE TABLE #ConIssuance (IID INT,CID INT,QTY INT,IWeight int,Entrydate DATETIME,DelID int)    
           
    INSERT INTO #Containerno VALUES(0,'ABC0000',2000,'2022-01-28',1,null)    
    INSERT INTO #Containerno VALUES(1,'ABC1111',2000,'2022-01-28',1,null)   
    INSERT INTO #Containerno VALUES(2,'ABC1222',1500,'2022-01-30',4,null)   
    INSERT INTO #Containerno VALUES(3,'ABC1333',7800,'2022-02-01',5,null)   
    INSERT INTO #Containerno VALUES(4,'ABC1444',4500,'2022-02-02',7,null)   
    INSERT INTO #Containerno VALUES(5,'ABC1555',4700,'2022-02-15',7,null)   
    INSERT INTO #Containerno VALUES(6,'ABC1666',5000,'2022-02-15',8,null)   
    INSERT INTO #Containerno VALUES(7,'ABC1777',6000,'2022-02-16',6,null)    
            
    INSERT INTO #ConIssuance VALUES(1001,1,1,1000,'2022-01-29',null)   
      INSERT INTO #ConIssuance VALUES(1002,2,1,500,'2022-01-30',null)   
      INSERT INTO #ConIssuance VALUES(1003,2,1,500,'2022-02-01',null)   
      INSERT INTO #ConIssuance VALUES(1004,3,1,2000,'2022-02-03',null)   
      INSERT INTO #ConIssuance VALUES(1005,4,1,1000,'2022-02-03',null)   
      INSERT INTO #ConIssuance VALUES(1006,4,1,1000,'2022-02-03',null)   
      INSERT INTO #ConIssuance VALUES(1007,3,1,1000,'2022-02-03',null)   
        INSERT INTO #ConIssuance VALUES(1008,6,1,1000,'2022-02-16',null)

Query which is not giving me expected output

Declare @StartDate date='2022-02-02'  
Declare @EndDate date='2022-02-03'  
  
;With CTE_C_O as (  
select CID, ContNo,  
case when entrydate=@StartDate then  0 else ConWeight end ConWeight,  
  
entrydate IN_Date from #Containerno where entrydate<=@StartDate and Cat_ID<> 16  
)  
,CTE_I_O as (  
select c.CID,ContNo,isnull(C.ConWeight-sum(I.IWeight),C.ConWeight) Opening_Weight,C.entrydate IN_Date  from #Containerno C   
full outer join #ConIssuance I on I.CID=C.CID   
where (I.EntryDate<@StartDate) and I.DelID is null and C.Cat_ID<> 16   
group by ContNo,C.entrydate,c.conweight,c.CID  
)  
,CTE_I_Con as (  
select c.CID,ContNo,isnull(sum(I.IWeight),0) Issue_Weight,C.entrydate IN_Date  from #Containerno C   
full outer join #ConIssuance I on I.CID=C.CID   
where (I.EntryDate between @StartDate and @EndDate) and I.DelID is null and C.Cat_ID<> 16   
group by ContNo,C.entrydate,c.conweight,c.CID  
)  
,Cte_Get_IN as   
(  
select c.CID,ContNo,  
  
case when c.entrydate between @startdate and @enddate then (C.ConWeight) else 0 end IN_Weight,  
  
C.entrydate IN_Date  from #Containerno C   
full outer join #ConIssuance I on I.CID=C.CID   
where (C.EntryDate between @startdate and @enddate) and I.DelID is null and C.Cat_ID<> 16   
group by ContNo,C.entrydate,c.conweight,c.CID  
)  
,CTE_F as (  
select CTE_C_O.CID ,CTE_C_O.ContNo,Isnull(Cte_C_O.IN_Date,Cte_I_O.IN_Date)IN_Date,  
Case When Isnull(Cte_C_O.IN_Date,Cte_I_O.IN_Date)=@StartDate then  0 else isnull(Opening_Weight,conweight)    end Opening_Weight   
,isnull(Issue_Weight,0)Issue_Weight   
,(Opening_Weight-Issue_Weight) Closing_Weight  
  from CTE_C_O   
  full  join CTE_I_O on CTE_C_O.CID=CTE_I_O.CID  
  left join CTE_I_Con on CTE_I_Con.CID=CTE_C_O.CID  
 )  
select  isnull(C.CID,Cte_Get_IN.CID)ID ,isnull(C.ContNo,Cte_Get_IN.Contno) Barcode,Isnull(cte_F.IN_Date,Cte_Get_IN.IN_Date),  
 (Opening_Weight)Opening_Weight,isnull(IN_Weight,0)IN_Weight,isnull(Issue_Weight,0)Issue_Weight,  
 Case when Issue_Weight !=0 then ( Opening_Weight-Issue_Weight)  
 when IN_Weight is not NUll then ((Opening_Weight-Issue_Weight) -Issue_Weight) else isnull(Closing_Weight,ConWeight)    
 end Closing_Weight1  from CTE_F  
 left join #Containerno C on C.CID=CTE_F.CID  
 full outer join Cte_Get_IN   on Cte_Get_IN.CID=C.CID  
 order by Cte_Get_IN.IN_Date asc

Below is expected output

Note :

Accepted Answer

Hi @Analyst_SQL
Check this query:

Declare @StartDate date='2022-02-01'  
Declare @EndDate date='2022-02-05'  
;WITH CTE AS  
(  
SELECT Contno,C.Entrydate,0 AS Opening_Weight,CASE WHEN C.Entrydate BETWEEN @StartDate AND @EndDate THEN ConWeight ELSE 0 END AS IN_Weight,  
	   SUM(CASE WHEN I.Entrydate BETWEEN @StartDate AND @EndDate THEN IWeight ELSE 0 END)AS Issue_Weight  
FROM #Containerno C LEFT JOIN #ConIssuance I ON C.CID=I.CID  
WHERE (C.Entrydate < @StartDate AND I.Entrydate BETWEEN @StartDate AND @EndDate)OR C.Entrydate BETWEEN @StartDate AND @EndDate  
GROUP BY Contno,C.Entrydate,ConWeight  
UNION  
SELECT Contno,C.Entrydate,ConWeight-SUM(ISNULL(IWeight,0)) AS Opening_Weight,0 AS IN_Weight,0 AS Issue_Weight  
FROM #Containerno C LEFT JOIN #ConIssuance I ON C.CID=I.CID AND ISNULL(I.Entrydate,0)< @StartDate  
WHERE C.Entrydate < @StartDate   
GROUP BY Contno,C.Entrydate,ConWeight  
)  
SELECT Contno AS Barcode,Entrydate AS IN_Date,SUM(Opening_Weight)AS Opening_Weight,SUM(IN_Weight)AS IN_Weight,SUM(Issue_Weight)AS Issue_Weight,  
       CASE WHEN SUM(Opening_Weight) =0 THEN SUM(IN_Weight)-SUM(Issue_Weight) ELSE SUM(Opening_Weight)-SUM(Issue_Weight) END AS Closing_Weight  
FROM CTE  
GROUP BY Contno,Entrydate

Best regards,
LiHong

Share via

Running Opening and Closing query

0 additional answers