none
How to use a value previously stored in a variable?

    Question

  • Hi,

    Can you pls advice how to achieve the below kind of scenario in T-SQL ?

    --------

    Initial_Value

    Used_Value

    Balance

    20

    0

    20

    1

    19 (=20-1)

    1

    18 (=19-1)

    2

    16 (=18-2)

    As every time it calculates the "Balance" part which is my final output it should subtract the "Used_Value" from the previous calculated "Balance"

    How can I achieve this (to include it in my stored proc to calculate "Balance" part)

    Thanks

    Monday, June 24, 2013 11:03 AM

Answers

  • You mention a variable in the title of your post so I assume that is the Initial_Value.  Below are a couple of approaches depending on the version of SQL Server you are using.  If you need additional help, please post DDL (CREATE TABLE) statements so that we can provide tested solution.

    CREATE TABLE dbo.InventoryTransaction(
    	InventoryTransactionID int NOT NULL IDENTITY
    		CONSTRAINT PK_InventoryTransaction PRIMARY KEY
    	,Used_Value int NOT NULL
    	);
    
    INSERT INTO dbo.InventoryTransaction (Used_Value) VALUES
    	(1),(1),(2);
    GO
    
    DECLARE @Initial_Value int = 20;
    
    --pre SQL 2012
    SELECT
    	a.Used_Value
    	,@Initial_Value - (SELECT SUM(Used_Value) 
    		FROM dbo.InventoryTransaction AS b 
    		WHERE b.InventoryTransactionID <= a.InventoryTransactionID
    		) AS Balance
    FROM dbo.InventoryTransaction AS a
    ORDER BY a.InventoryTransactionID;
    
    --SQL 2012 and later
    SELECT
    	Used_Value
    	,@Initial_Value - SUM(Used_Value) OVER(ORDER BY InventoryTransactionID
    		ROWS UNBOUNDED PRECEDING)
    AS Balance
    FROM dbo.InventoryTransaction;


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    Monday, June 24, 2013 11:20 AM

All replies

  • You mention a variable in the title of your post so I assume that is the Initial_Value.  Below are a couple of approaches depending on the version of SQL Server you are using.  If you need additional help, please post DDL (CREATE TABLE) statements so that we can provide tested solution.

    CREATE TABLE dbo.InventoryTransaction(
    	InventoryTransactionID int NOT NULL IDENTITY
    		CONSTRAINT PK_InventoryTransaction PRIMARY KEY
    	,Used_Value int NOT NULL
    	);
    
    INSERT INTO dbo.InventoryTransaction (Used_Value) VALUES
    	(1),(1),(2);
    GO
    
    DECLARE @Initial_Value int = 20;
    
    --pre SQL 2012
    SELECT
    	a.Used_Value
    	,@Initial_Value - (SELECT SUM(Used_Value) 
    		FROM dbo.InventoryTransaction AS b 
    		WHERE b.InventoryTransactionID <= a.InventoryTransactionID
    		) AS Balance
    FROM dbo.InventoryTransaction AS a
    ORDER BY a.InventoryTransactionID;
    
    --SQL 2012 and later
    SELECT
    	Used_Value
    	,@Initial_Value - SUM(Used_Value) OVER(ORDER BY InventoryTransactionID
    		ROWS UNBOUNDED PRECEDING)
    AS Balance
    FROM dbo.InventoryTransaction;


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    Monday, June 24, 2013 11:20 AM
  • Please provide the DDL and DML to help you better. However you can use CTE to get the row_Number and self join the cte  and subtract the values.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, June 24, 2013 11:21 AM
  • Dan 

    a.InventoryTransactionID

    Alias do not work, we need to remove it. I see it is just copied /pasted mistake :-))))


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, June 24, 2013 11:29 AM
  • Hi Dan,

    How are you updating Initial_Value after the first balance is calculated.

    i.e,

    DECLARE @Initial_Value int = 20;

    then balance = @Initial_Value - Used_Value

    Afterwards, when next balance is calculated now my @Initial_Value should be updated to 19 (=20-1)

    Then only it will calculate next balance as 19-1 = 18

    Thanks

    Monday, June 24, 2013 11:29 AM
  • How are you updating Initial_Value after the first balance is calculated.

    I am not updating the variable value or the table data in the queries I posted.  The queries simply calculated the running balance for each row in order based on the provided @Initial_Value.

    Do you need to update data in the table, return a result, update a variable (with the final balance), or some combination thereof?  I think it will help if you provide DDL.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, June 24, 2013 12:38 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. Does your boss make you work from ASCII pictures? Look up the Window clause for aggregate functions. Why should I post DML, if you don't have to post DDL? 

    SUM(..) 
    OVER (ORDER BY .. ROWS UNBOUNDED PRECEDING AND CURRENT ROW)

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, June 24, 2013 3:17 PM
  • Hi Dan,

    Yes I want to update the Balance field every time and Balance should be calculated based on the Previously calculated balance - Used Value

    Thanks

    Tuesday, June 25, 2013 4:17 AM
  • Thanks, Uri.  I removed the extraneous column alias.

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Tuesday, June 25, 2013 10:57 AM
  • Yes I want to update the Balance field every time and Balance should be calculated based on the Previously calculated balance - Used Value

    Are you saying you table has a Balance column that you need to update every time a Used_Value is inserted or updated?  Or is the Balance value to be calculated during the query?

    Please provide DDL so we can answer your question more clearly.  You can use the CREATE TABLE statement I posted earlier and modify it for your actual situation. 


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Tuesday, June 25, 2013 11:02 AM
  • Hi Dan,

    Yes I want to update the Balance field every time and Balance should be calculated based on the Previously calculated balance - Used Value

    Thanks

    Hi ,

    Try below code and let me know any concerns.

     --SAMPLE TABLE
    CREATE TABLE TEST_CUM
    (
          ID INT IDENTITY(1,1),EID INT,NAME VARCHAR(10),INITIAL_VAL INT,PAY_VAL INT,BALANCE INT
    )
    
     --SAMPLE DATA
    
    INSERT INTO TEST_CUM(EID,NAME,INITIAL_VAL ,PAY_VAL ) VALUES (100,'KRISH',500,85)
    INSERT INTO TEST_CUM(EID,NAME,INITIAL_VAL ,PAY_VAL ) VALUES (101,'MANOJ',656,21)
    INSERT INTO TEST_CUM(EID,NAME,INITIAL_VAL ,PAY_VAL ) VALUES (102,'KUMAR',324,12)
    INSERT INTO TEST_CUM(EID,NAME,INITIAL_VAL ,PAY_VAL ) VALUES (103,'SANTOSH',545,32)
    INSERT INTO TEST_CUM(EID,NAME,INITIAL_VAL ,PAY_VAL ) VALUES (104,'BULLSHIT',654,35)
    INSERT INTO TEST_CUM(EID,NAME,INITIAL_VAL ,PAY_VAL ) VALUES (105,'KAMESH',288,0)
    INSERT INTO TEST_CUM(EID,NAME,INITIAL_VAL ,PAY_VAL ) VALUES (105,'KAMESH',288,40)
    INSERT INTO TEST_CUM(EID,NAME,INITIAL_VAL ,PAY_VAL ) VALUES (105,'KAMESH',288,70)
    INSERT INTO TEST_CUM(EID,NAME,INITIAL_VAL ,PAY_VAL ) VALUES (105,'KAMESH',288,100)
    INSERT INTO TEST_CUM(EID,NAME,INITIAL_VAL ,PAY_VAL ) VALUES (105,'KAMESH',288,50)
    INSERT INTO TEST_CUM(EID,NAME,INITIAL_VAL ,PAY_VAL ) VALUES (105,'KAMESH',288,28)
    
    
    
    -- COMPLETE CODE
    ---------------------------------------------------------------------------------------------------------------
    DROP TABLE #TEMP 
    CREATE TABLE #TEMP(T_ID INT IDENTITY (1,1),EID INT)
    INSERT INTO #TEMP SELECT DISTINCT EID FROM TEST_CUM 
    
    DECLARE @W_START INT,@W_END INT,@EID INT,@BALANCE INT,@CUR_PAY INT,@TOTAL_BAL INT,@UPDATE_FIRST INT
    DECLARE @E_START INT,@E_END INT
    SET @W_START =(SELECT MIN(T_ID) FROM #TEMP)
    SET @W_END =(SELECT MAX(T_ID) FROM #TEMP)
    
    WHILE (@W_START<=@W_END )
    BEGIN
          
          SET @EID    =(SELECT EID  FROM #TEMP WHERE T_ID =@W_START )
          
          SET @E_START=(SELECT MIN(ID) FROM TEST_CUM WHERE EID=@EID)
          SET @E_END  =(SELECT MAX(ID) FROM TEST_CUM WHERE EID=@EID)
          
          UPDATE TOP (1) TEST_CUM SET  BALANCE =  INITIAL_VAL -PAY_VAL  WHERE EID = @EID  
    
          WHILE(@E_START<=@E_END)
                BEGIN 
                                        
                      
                      SET @CUR_PAY = (SELECT SUM(PAY_VAL) FROM TEST_CUM WHERE EID =@EID AND ID=@E_START)
                      SET @BALANCE =(SELECT BALANCE FROM TEST_CUM WHERE EID =@EID AND ID=@E_START-1 AND BALANCE IS NOT NULL ) 
                      SET @TOTAL_BAL=@BALANCE-@CUR_PAY
                      
                      
                      UPDATE TEST_CUM SET BALANCE =@TOTAL_BAL  WHERE EID =@EID AND ID=@E_START AND BALANCE IS NULL
                      SET @E_START=@E_START+1
                END
    SET @W_START=@W_START+1
    END
    ---------------------------------------------------------------------------------------------------------------------
    
    -- TEST IT OUT
    SELECT * FROM TEST_CUM WHERE EID=105

    Thanks.


    bala krishna

    Tuesday, June 25, 2013 11:26 AM
  • try as below:

     --SAMPLE TABLE
    CREATE TABLE TEST_CUM
    (
          ID INT IDENTITY(1,1),EID INT,NAME VARCHAR(10),INITIAL_VAL INT,PAY_VAL INT,BALANCE INT
    )
    
     --SAMPLE DATA
    
    INSERT INTO TEST_CUM(EID,NAME,INITIAL_VAL ,PAY_VAL ) VALUES (100,'KRISH',500,85)
    INSERT INTO TEST_CUM(EID,NAME,INITIAL_VAL ,PAY_VAL ) VALUES (101,'MANOJ',656,21)
    INSERT INTO TEST_CUM(EID,NAME,INITIAL_VAL ,PAY_VAL ) VALUES (102,'KUMAR',324,12)
    INSERT INTO TEST_CUM(EID,NAME,INITIAL_VAL ,PAY_VAL ) VALUES (103,'SANTOSH',545,32)
    INSERT INTO TEST_CUM(EID,NAME,INITIAL_VAL ,PAY_VAL ) VALUES (104,'BULLSHIT',654,35)
    INSERT INTO TEST_CUM(EID,NAME,INITIAL_VAL ,PAY_VAL ) VALUES (105,'KAMESH',288,0)
    INSERT INTO TEST_CUM(EID,NAME,INITIAL_VAL ,PAY_VAL ) VALUES (105,'KAMESH',288,40)
    INSERT INTO TEST_CUM(EID,NAME,INITIAL_VAL ,PAY_VAL ) VALUES (105,'KAMESH',288,70)
    INSERT INTO TEST_CUM(EID,NAME,INITIAL_VAL ,PAY_VAL ) VALUES (105,'KAMESH',288,100)
    INSERT INTO TEST_CUM(EID,NAME,INITIAL_VAL ,PAY_VAL ) VALUES (105,'KAMESH',288,50)
    INSERT INTO TEST_CUM(EID,NAME,INITIAL_VAL ,PAY_VAL ) VALUES (105,'KAMESH',288,28)
    
    
    
    -- COMPLETE CODE
    ---------------------------------------------------------------------------------------------------------------
    DROP TABLE #TEMP 
    CREATE TABLE #TEMP(T_ID INT IDENTITY (1,1),EID INT)
    INSERT INTO #TEMP SELECT DISTINCT EID FROM TEST_CUM 
    
    DECLARE @W_START INT,@W_END INT,@EID INT,@BALANCE INT,@CUR_PAY INT,@TOTAL_BAL INT,@UPDATE_FIRST INT
    DECLARE @E_START INT,@E_END INT
    SET @W_START =(SELECT MIN(T_ID) FROM #TEMP)
    SET @W_END =(SELECT MAX(T_ID) FROM #TEMP)
    
    WHILE (@W_START<=@W_END )
    BEGIN
          
          SET @EID    =(SELECT EID  FROM #TEMP WHERE T_ID =@W_START )
          
          SET @E_START=(SELECT MIN(ID) FROM TEST_CUM WHERE EID=@EID)
          SET @E_END  =(SELECT MAX(ID) FROM TEST_CUM WHERE EID=@EID)
          
          UPDATE TOP (1) TEST_CUM SET  BALANCE =  INITIAL_VAL -PAY_VAL  WHERE EID = @EID  
    
          WHILE(@E_START<=@E_END)
                BEGIN 
                                        
                      
                      SET @CUR_PAY = (SELECT SUM(PAY_VAL) FROM TEST_CUM WHERE EID =@EID AND ID=@E_START)
                      SET @BALANCE =(SELECT BALANCE FROM TEST_CUM WHERE EID =@EID AND ID=@E_START-1 AND BALANCE IS NOT NULL ) 
                      SET @TOTAL_BAL=@BALANCE-@CUR_PAY
                      
                      
                      UPDATE TEST_CUM SET BALANCE =@TOTAL_BAL  WHERE EID =@EID AND ID=@E_START AND BALANCE IS NULL
                      SET @E_START=@E_START+1
                END
    SET @W_START=@W_START+1
    END
    ---------------------------------------------------------------------------------------------------------------------
    
    -- TEST IT OUT
    --SELECT * FROM TEST_CUM WHERE EID=105

    Thanks.


    bala krishna

    Tuesday, June 25, 2013 11:27 AM