Opening and closing Query ?

Question

Opening and closing Query ?

Analyst_SQL 3,576

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

Accepted answer

3 additional answers

Your answer

Answer 1

MelissaMa-MSFT 24,221

Hi @Analyst_SQL ,

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

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.

Analyst_SQL 3,576 Reputation points

2021-05-25T08:58:57.34+00:00

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
MelissaMa-MSFT 24,221 Reputation points

2021-05-25T09:20:47.067+00:00

Hi @Analyst_SQL ,

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
Analyst_SQL 3,576 Reputation points

2021-05-25T09:44:33.96+00:00

OK ,i will ask in another question about same requirement ,to define closing again specific date,
MelissaMa-MSFT 24,221 Reputation points

2021-05-25T09:46:50.7+00:00

Hi @Analyst_SQL ,

Please check the latest answer I posted and check whether it is working.

If it is still working, you could post a new question with new requirement and expected output.

Thanks for your understanding.

Best regards,
Melissa

Answer 2

Tom Phillips 17,771

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.

Answer 3

Hi @Analyst_SQL ,

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:

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.

Analyst_SQL 3,576 Reputation points

2021-05-25T06:38:38.917+00:00

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

Answer 4

Hi @Analyst_SQL ,

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

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.

Share via

Opening and closing Query ?

3 additional answers

Your answer