question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked MelissaMa-msft commented

Opening and closing Query ?

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


99145-closing.png


sql-server-generalsql-server-transact-sql
closing.png (9.4 KiB)
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.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft commented

Hi @akhterhussain-3167,

I made some changes on the query and please refer updated query from below:

 ;WITH wte1 AS
 (
 SELECT a.EntryDate, a.pWeight, a.prdqty, b.Bweight, b.Bpqty, c.iWeight, c.QTY, 35400+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 Closing, LAG(CLOSING,1,35400) 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

99411-output.png

In my output,the last three rows of my result is different from yours.

Could you please double check the expected output? Since you mentioned that Opening = Closing will forward next day.

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


output.png (9.9 KiB)
· 4
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.

Yes ,i made mistake in output,your output is correct, I have to little bit make it change,
When i Input Opening in my data ,then it is calculating from day first, is it Possible to fix Opening against specific Date .
Like 35400 will be closing before 2020-06-01 ,so i want to fix opening ,what ever date exit before 2020-06-01 .You may consider 2020-05-30

0 Votes 0 ·

Hi @akhterhussain-3167,

Please provide your exact expected output. Thanks.

It is also recommended to provide all details about your requirement at the beginning so that we would not keep updating the query.

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·

OK ,i will ask in another question about same requirement ,to define closing again specific date,

0 Votes 0 ·
Show more comments
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

I am not clear on your question.

However, the simplest way to handle opening balances is to simply insert them into your data with something like a date of '1900-01-01'. Then your code just works.

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.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered akhterhussain-3167 commented

Hi @akhterhussain-3167,

Could you please provide the source of '35400'? Is it in another table or you just would like to hard code it in the query?

Please refer below and check whether it is working.

 ;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,35400) 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

Output:
99156-output.png

We could use LAG(CLOSING,1,35400) return the default value of 35400 if there is no lag value available for the first row.

If above is not working, please provide more details or sample data. Thanks.

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


output.png (9.3 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.

Hi @MelissaMa-msft ,

Please review below rule of calculation of columns.

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

Yes,35400 it will be hard core first time and second thing is that ,Opening will be get add in Iweight ,then 2020-06-01 floor will be (35400+3000)=38400
99381-image.png


0 Votes 0 ·
image.png (17.8 KiB)
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @akhterhussain-3167,

Please refer below updated query:

 ;with cte as (
  select DATEADD(dd,-2,min(EntryDate)) EntryDate,0 pWeight,0 prdqty, 0 Bweight,0 Bpqty,0 iWeight,0 QTY, 35400  Closing
  from #Probale)
 ,wte1 AS
 (
 select * from cte
 union
 SELECT a.EntryDate, a.pWeight, a.prdqty, b.Bweight, b.Bpqty, c.iWeight, c.QTY,(select closing from cte)+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

99377-output.png
Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



output.png (10.7 KiB)
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.