Hi @EchoLiu-MSFT
Please check below query ,which is giving expected output ,but i want to input/hardcore once time opening value ,currently is coming 0 ,but i want to fix it 35400,please review
CREATE TABLE #Probale (Prdno INT,pWeight int,prdqty int,EntryDate date)
CREATE TABLE #ConIssuance (CID INT,iWeight int,QTY int,EntryDate date)
CREATE TABLE #Bigbalprd (BID INT,Bweight int,Bpqty int,EntryDate date)
-- set date format
SET DATEFORMAT dmy
INSERT INTO #Probale VALUES(10010,100,1,'01-06-2020')
INSERT INTO #Probale VALUES(10011,100,1,'01-06-2020')
INSERT INTO #Probale VALUES(10012,300,1,'02-06-2020')
INSERT INTO #Probale VALUES(10016,200,1,'02-06-2020')
INSERT INTO #Probale VALUES(10013,110,1,'03-06-2020')
INSERT INTO #Probale VALUES(10014,150,1,'03-06-2020')
INSERT INTO #Probale VALUES(10015,100,1,'04-06-2020')
INSERT INTO #Probale VALUES(10016,150,1,'04-06-2020')
INSERT INTO #Probale VALUES(10017,800,1,'05-06-2020')
INSERT INTO #Probale VALUES(10018,800,1,'05-06-2020')
INSERT INTO #Probale VALUES(10019,900,1,'06-06-2020')
INSERT INTO #Probale VALUES(10018,900,1,'06-06-2020')
INSERT INTO #Bigbalprd VALUES(20010,500,1,'01-06-2020')
INSERT INTO #Bigbalprd VALUES(20011,600,1,'01-06-2020')
INSERT INTO #Bigbalprd VALUES(20012,700,1,'02-06-2020')
INSERT INTO #Bigbalprd VALUES(20013,200,1,'02-06-2020')
INSERT INTO #Bigbalprd VALUES(20014,410,1,'03-06-2020')
INSERT INTO #Bigbalprd VALUES(20015,250,1,'03-06-2020')
INSERT INTO #Bigbalprd VALUES(20016,200,1,'04-06-2020')
INSERT INTO #Bigbalprd VALUES(20017,250,1,'04-06-2020')
INSERT INTO #Bigbalprd VALUES(20018,400,1,'05-06-2020')
INSERT INTO #Bigbalprd VALUES(20019,200,1,'05-06-2020')
INSERT INTO #Bigbalprd VALUES(20020,300,1,'05-06-2020')
INSERT INTO #Bigbalprd VALUES(20021,350,1,'06-06-2020')
INSERT INTO #ConIssuance VALUES(1111,1000,1,'01-06-2020')
INSERT INTO #ConIssuance VALUES(1112,2000,1,'01-06-2020')
INSERT INTO #ConIssuance VALUES(1113,800,1,'02-06-2020')
INSERT INTO #ConIssuance VALUES(1114,600,1,'02-06-2020')
INSERT INTO #ConIssuance VALUES(1115,400,1,'03-06-2020')
INSERT INTO #ConIssuance VALUES(1116,100,1,'03-06-2020')
INSERT INTO #ConIssuance VALUES(1117,300,1,'04-06-2020')
INSERT INTO #ConIssuance VALUES(1118,110,1,'04-06-2020')
INSERT INTO #ConIssuance VALUES(1119,100,1,'05-06-2020')
INSERT INTO #ConIssuance VALUES(1120,800,1,'05-06-2020')
INSERT INTO #ConIssuance VALUES(1121,900,1,'05-06-2020')
INSERT INTO #ConIssuance VALUES(1122,1900,1,'06-06-2020')
-- wte1: calculate running total and get closing
;WITH wte1 AS
(
SELECT a.EntryDate, a.pWeight, a.prdqty, b.Bweight, b.Bpqty, c.iWeight, c.QTY, SUM(iWeight - pWeight - Bweight) OVER (ORDER BY a.EntryDate) Closing
FROM ( SELECT SUM(pWeight) pWeight,SUM(prdqty) prdqty, EntryDate FROM #Probale GROUP BY EntryDate) a
JOIN ( SELECT SUM(Bweight) Bweight,SUM(Bpqty) Bpqty, EntryDate FROM #Bigbalprd GROUP BY EntryDate) b ON a.EntryDate = b.EntryDate
JOIN ( SELECT SUM(iWeight) iWeight,SUM(QTY) QTY, EntryDate FROM #ConIssuance GROUP BY EntryDate) c ON a.EntryDate = c.EntryDate
),
-- wte2: lag closing to get opening
wte2 AS
(
select EntryDate, pWeight,prdqty, Bweight,Bpqty, iWeight,QTY, Closing, LAG(CLOSING,1,0) OVER(ORDER BY EntryDate) Opening FROM wte1
),
-- wte3: get floor by adding opening to iWeight of that day
wte3 AS
(
select EntryDate, pWeight,prdqty, Bweight,Bpqty, iWeight,QTY, Opening+iWeight [Floor] , Closing, Opening FROM wte2
)
-- final result
SELECT * FROM wte3