SQL: Quantity Difference Between Rows and Columns

Question

I have a table with the data below

Table X             
Seq_no    A    Claim    Payment     Balance (to be calculated)
1        abc    100     10          90 (100-10)
2        abc            50          40 (90-50)
3        abc            20          20 (40-20)
1        xyz    150     10          140 (150-10)
1        qwe    200     10          190 (200-10)

I need to calculate the balance of a column.

I am trying to use the following SQL query>

Select
Seq_no, a, Claim, Payment, 
CASE 
    When Seq_no =1
    then (claim-Payment) 
    Else ( lag(Balance)- Payment over (order by Balance))
END as  Balance
from table X

However, I get an error

ORA-00904: "Balance": invalid identifier
00904. 00000 -  "%s: invalid identifier"

I believe this is because Balance is not an existing column name.

Is there a proper way to achieve results.?

Update: * I missed an important role. *

The data that I have is in the following format:

Table X             
Seq_no    A    Claim    Payment    
1        abc    100     10         
2        abc    100     50         
3        abc    100     20         
1        xyz    150     10          
1        qwe    200     10          

I need results in the following format.

Table X             
Seq_no    A    Claim    Payment     Balance (to be calculated)
1        abc    100     10          90 (100-10)
2        abc            50          40 (90-50)
3        abc            20          20 (40-20)
1        xyz    150     10          140 (150-10)
1        qwe    200     10          190 (200-10)

The calculation of Seq_no was performed to make the claim column zero for cases of duplicate claims that I have already found out.

Answer 1

.

" " , .

, A, A.

Select
    Seq_no, a, Claim, Payment, 
    sum(nvl(claim,0) - payment) over (partition by A order by seq_no) as Balance
from X;

:

SEQ_NO  A   CLAIM   PAYMENT BALANCE
1       abc 100     10      90
2       abc         50      40
3       abc         20      20
1       qwe 200     10      190
1       xyz 150     10      140

EDIT: nvl- , seq = 1:

Select
    Seq_no, 
    a, 
    case when seq_no=1 then claim else 0 end as Claim, 
    Payment, 
    sum(case when seq_no=1 then claim else 0 end - payment) 
          over (partition by A order by seq_no) as Balance
from X;

Answer 2

balance , , .. .

:

SQL> WITH sample_data AS(
  2  SELECT 1 Seq_no, 'abc' A, 100 Claim, 10 Payment FROM dual UNION ALL
  3  SELECT 2 Seq_no, 'abc' A, NULL Claim, 50 FROM dual UNION ALL
  4  SELECT 3 Seq_no, 'abc' A, NULL Claim, 20 FROM dual UNION ALL
  5  SELECT 1 Seq_no, 'xyz' A, 150 Claim, 10 FROM dual UNION ALL
  6  SELECT 1 Seq_no, 'qwe' A, 200 Claim, 10 FROM dual
  7  )
  8  -- end of sample_data mimicking real table
  9  SELECT seq_no, A, claim, payment,
 10    CASE
 11      WHEN lag(balance) OVER(PARTITION BY A ORDER BY seq_no) IS NULL
 12      THEN balance
 13      ELSE lag(balance) OVER(PARTITION BY A ORDER BY seq_no) - payment
 14    END balance
 15  FROM
 16    (SELECT seq_no, A, claim, payment,
 17      CASE
 18        WHEN seq_no = 1
 19        THEN claim     - payment
 20        ELSE lag(claim - payment) OVER(PARTITION BY A ORDER BY seq_no) - payment
 21      END balance
 22    FROM sample_data
 23    );

    SEQ_NO A        CLAIM    PAYMENT    BALANCE
---------- --- ---------- ---------- ----------
         1 abc        100         10         90
         2 abc                    50         40
         3 abc                    20         20
         1 qwe        200         10        190
         1 xyz        150         10        140

SQL>

. claim, diff:

SQL> WITH sample_data AS(
  2    SELECT 1 Seq_no, 'abc' A, 100 Claim, 10 Payment FROM dual UNION ALL
  3    SELECT 2 Seq_no, 'abc' A, 100 Claim, 50 FROM dual UNION ALL
  4    SELECT 3 Seq_no, 'abc' A, 100 Claim, 20 FROM dual UNION ALL
  5    SELECT 4 Seq_no, 'abc' A, 100 Claim, 10 FROM dual UNION ALL
  6    SELECT 5 Seq_no, 'abc' A, 100 Claim, 10 FROM dual UNION ALL
  7    SELECT 1 Seq_no, 'xyz' A, 150 Claim, 10 FROM dual UNION ALL
  8    SELECT 1 Seq_no, 'qwe' A, 200 Claim, 10 FROM dual
  9    )
 10  -- end of sample_data mimicking real table
 11  SELECT Seq_no,
 12    a,
 13    Claim,
 14    Payment,
 15    CASE
 16      WHEN seq_no = 1
 17      THEN claim     - payment
 18      ELSE SUM(claim - payment) over (partition BY A order by seq_no) - claim*(seq_no-1)
 19    END balance
 20  FROM sample_data;

    SEQ_NO A        CLAIM    PAYMENT    BALANCE
---------- --- ---------- ---------- ----------
         1 abc        100         10         90
         2 abc        100         50         40
         3 abc        100         20         20
         4 abc        100         10         10
         5 abc        100         10          0
         1 qwe        200         10        190
         1 xyz        150         10        140

7 rows selected.

SQL>

Answer 3

, - , . .

, , , , SQL, . SQL . , , . , SQL Server ( , ):

DECLARE PaymentCursor CURSOR FOR SELECT A, Claim, Payment FROM X ORDER BY A, Seq_no
OPEN PaymentCursor

DECLARE @A VARCHAR(16)
DECLARE @Claim MONEY
DECLARE @Payment MONEY
DECLARE @Balance MONEY
DECLARE @PreviousA VARCHAR(16)

SET @PreviousA = ''

FETCH NEXT FROM PaymentCursor INTO @A, @Claim, @Payment
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @A <> @PreviousA
    BEGIN
        SET @Balance = 0
        SET @PreviousA = @A
    END

    SET @Balance = @Balance + @Claim - @Payment

    UPDATE X SET Balance = @Balance WHERE CURRENT OF PaymentCursor

    FETCH NEXT FROM PaymentCursor INTO @A, @Claim, @Payment
END

CLOSE PaymentCursor
DEALLOCATE PaymentCursor

, , . :

CREATE PROCEDURE InsertAndComputeBalance
    @A VARCHAR(16),
    @Claim MONEY,
    @Payment MONEY
AS
    DECLARE @MAX_Seq_no INTEGER = (SELECT MAX(Seq_no) FROM X WHERE A = @A)

    INSERT INTO X
    SELECT @MAX_Seq_no + 1, @A, @Claim, @Payment, Balance + @Claim - @Payment FROM X
    WHERE A = @A AND Seq_no = @MAX_Seq_no

In theory, you can call similar logic from a trigger so that it runs automatically every time a row is inserted, but triggers can be evil, so be careful with this approach.