Opening and closing Query ?

Analyst_SQL 3,576 Reputation points
2021-05-24T13:10:42.6+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,492 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,707 questions
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,221 Reputation points
    2021-05-25T07:05:00.76+00:00

    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  
    

    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.


3 additional answers

Sort by: Most helpful
  1. Tom Phillips 17,771 Reputation points
    2021-05-24T21:01:27.947+00:00

    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.

    0 comments No comments

  2. MelissaMa-MSFT 24,221 Reputation points
    2021-05-25T01:20:26.043+00:00

    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:
    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.


  3. MelissaMa-MSFT 24,221 Reputation points
    2021-05-25T09:31:46.56+00:00

    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  
    

    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.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.