Post
Ask Question
Akhter HUssain

Akhter HUssain

  • 680
  • 1.3k
  • 96k

Opening and Closing Value calculate

Feb 24 2022 10:17 AM

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)
  1. Note: About Columns;
  2. Contno = Will be retrieve from table #Containerno according to Date between Filter,
  3. Opening_Weight =First time opening will get from #Containerno table ,then Closing will be carry forward (Opening_Weigt-Isu_Weight) accordingly date filter.
  4. Isu_weight = Will be retrieve from table #ConIssuance according to Date Between filter ,
  5. Closing_Weight = Opening_Weight - Isue_Weight accordingly date filter.


Answers (2)