question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked EchoLiu-msft edited

Query required in with opening and closing?

i have data below

 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) 
    
 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,'05-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')  




Output

Probale columns Pweight and prdqty Sum date wise.


Bigbalprd Columns Bweight and Bpqty sum date wise.



ConIssuance Columns Iweight and QTY sum date wise

Floor= (Opening + Iweight)

Closing = Floor-Bweight-Pweight

Opening = Closing will forward next day


98486-image.png


sql-server-generalsql-server-transact-sql
image.png (18.6 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Since the last three columns are a cyclic calculation, tsql is not easy to implement, do you mind using excel, I used excel to easily achieve the calculation of the last three columns.
98542-image.png


0 Votes 0 ·
image.png (19.4 KiB)

1 Answer

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited
 ;WITH cte
 as(
 SELECT SUM(pWeight) pWeight,SUM(prdqty) prdqty,EntryDate
 FROM #Probale 
 GROUP BY EntryDate)
 ,cte2 as(
 SELECT SUM(BWeight) BWeight,SUM(Bpqty) Bpqty,EntryDate
 FROM #Bigbalprd
 GROUP BY EntryDate)
 ,cte3 as(
 SELECT SUM(iWeight) IWeight,SUM(qty) qty,EntryDate
 FROM #ConIssuance 
 GROUP BY EntryDate)
 ,cte4 as(
 SELECT '2020-05-30' Date,null pWeight,null prdqty,null BWeight,
 null Bpqty,null IWeight,null qty,0  as Closing
 UNION ALL
 SELECT c.EntryDate,pWeight,prdqty,BWeight,Bpqty,IWeight,qty,null
 FROM cte c
 JOIN cte2 c2 ON c.EntryDate=c2.EntryDate
 JOIN cte3 c3 ON c.EntryDate=c3.EntryDate)
    
 SELECT * FROM cte4

I used the data returned by the above code to do a simple calculation in excel, and then I got the final result.

This method is for reference only.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

is it not possible to Display last three column?

0 Votes 0 ·

Good morning akhterhussain, if I remember correctly, you posted a similar problem a long time ago, and I couldn't solve it at the time, and I asked a senior Microsoft tsql engineer, and he didn't have a solution.

Last Friday I spent a lot of time trying to display the last three columns correctly, but I still did not find a solution. I know that the last three columns are easy to implement in excel, because if you set a formula for a column in excel, the column will change with the changes of the columns involved in the formula. But as far as I know, it seems that this function cannot be implemented in tsql.

Regards
Echo

1 Vote 1 ·