SQLTeam.com | Weblogs | Forums

Opening and Closing Value calculate

Below is data

CREATE TABLE #Containerno(CID INT,Contno VARCHAR(50),ConWeight nvarchar(50),Entrydate date ,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',null)  
   INSERT INTO #Containerno VALUES(1,'ABC1111',2000,'2022-01-28',null) 
   INSERT INTO #Containerno VALUES(2,'ABC1222',1500,'2022-01-30',null) 
   INSERT INTO #Containerno VALUES(3,'ABC1333',7800,'2022-02-01',null) 
   INSERT INTO #Containerno VALUES(4,'ABC1444',4500,'2022-02-02',null) 
   INSERT INTO #Containerno VALUES(5,'ABC1555',4700,'2022-02-15',null) 
   INSERT INTO #Containerno VALUES(6,'ABC1666',5000,'2022-02-15',null) 
   INSERT INTO #Containerno VALUES(7,'ABC1777',6000,'2022-02-16',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) 
 Note: About Columns;
 Contno = Will be retrieve from table #Containerno according to Date between Filter,
 Opening_Weight =What ever closing value before Starting Date Parameter (Opening_Weigt-Isu_Weight)
 Isu_weight = Will be retrieve from table #ConIssuance  according to Date Between filter  ,
 Closing_Weight = Opening_Weight - Isue_Weight

Expected output( i am giving date filter from 01-02-2022 to 15-02-2022

Openingclosing

DECLARE @StartDate date = '20220201'
	,@EndDate date = '20220215';

WITH PrevInsurance
AS
(
	SELECT CID
		,MAX(EntryDate) AS EntryDate
		,SUM(IWeight) AS PWeight
	FROM #ConIssuance
	WHERE Entrydate < @StartDate
	GROUP BY CID
)
SELECT C.Contno, X.EntryDate, X.ConWeight
	,COALESCE(SUM(I.IWeight), 0) AS IWeight
	,X.ConWeight - COALESCE(SUM(I.IWeight), 0) AS CWeight
FROM #Containerno C
	LEFT JOIN #ConIssuance I
		ON C.CID = I.CID
	LEFT JOIN PrevInsurance P
		ON I.CID = P.CID
	CROSS APPLY
	(
		VALUES
		(
			COALESCE(P.EntryDate, C.EntryDate)
			,C.ConWeight - COALESCE(P.PWeight, 0)
		)
	) X (EntryDate, ConWeight)
WHERE C.Entrydate BETWEEN '20220201' AND '20220215'
	OR I.Entrydate BETWEEN '20220201' AND '20220215'
GROUP BY C.Contno, X.EntryDate, X.ConWeight
ORDER BY Contno;