CODEWITHSUNDEEP
sql-server
Im Khan
Im Khan

Reputation: 71

How to Minus Amount

I am try to query in sql server but result was not produce

my query, table and expected result mention

USE [bank] 
GO 

/****** Object: StoredProcedure [dbo].[Pr_Rpt_BankPlaning] ******/ 
SET ANSI_NULLS ON 
GO 

SET QUOTED_IDENTIFIER ON 
GO 

ALTER PRODCEDURE [dbo].[Pr_Rpt_BankPlaning] AS 
BEGIN ;
  WITH cte AS
    ( 
      SELECT 
          TotalAmount, 
          BankAmount,
          (TotalAmount-BankAmount) AS minusamount, 
          rownum = ROW_NUMBER() OVER (order BY BankAmount) 
      FROM Bank AS bc 
    ) 

  SELECT
    CASE
        WHEN cte.TotalAmount = prev.TotalAmount THEN prev.minusamount 
        ELSE cte.TotalAmount 
    END AS TotalAmount1, 
    cte.BankAmount,
    CASE 
        WHEN cte.TotalAmount = prev.TotalAmount THEN prev.minusamount 
        ELSE cte.TotalAmount 
    END
    -cte.BankAmount AS Amount,
    cte.minusamount,
    cte.rownum 
    FROM CTE 
    LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1 
    LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1 
END

this query gives this result enter image description here

but i want this type of result carry forward minus amount in next totalamount enter image description here

Upvotes: 1

Views: 58

Answers (1)

Gaurav Rajput
Gaurav Rajput

Reputation: 647

Although you have not provided any sample data as requested by other also, i used output displayed in your first screenshot, as sample data to provide you the required output. You need to integrate my CTE in your query to get the output.

Below query gives you the output required by you.

DECLARE @AMOUNT_DATA TABLE(TA INT, BA INT,AM INT,MAMT INT,ROWNUM INT )
INSERT INTO @AMOUNT_DATA VALUES
(458800,25000,433800,433800,1),
(433800,25000,408800,433800,2),
(433800,100000,333800,358800,3)

;WITH AMOUNT_DATA
AS
(
    SELECT ROWNUM,TA,BA,TA-BA RESULT FROM @AMOUNT_DATA WHERE ROWNUM=1
    UNION ALL
    SELECT T2.ROWNUM,T1.RESULT TA,T2.BA,T1.RESULT-T2.BA FROM AMOUNT_DATA T1 
    JOIN @AMOUNT_DATA T2 ON T1.ROWNUM=T2.ROWNUM-1
)
    SELECT * FROM AMOUNT_DATA

Output

------------------------------
--ROWNUM    TA  BA  RESULT
------------------------------
1   458800  25000   433800
2   433800  25000   408800
3   408800  100000  308800
------------------------------

Upvotes: 1

Related Questions