Rank() SQL Or Something like that-CodePudding

hello there is any way to get an Account Balance for these like I would like to calculate Credit and Debit like

1 - 1000 
2 - 1000-500 = 500
3 - 500-700  = -200

and so on depend on date

There is any solution like rank or something to help? thanks too much/

enter image description here

CodePudding user response：

Use the LAG analytic function:

SELECT "Date",
       GREATEST(
         account_balance - LAG(account_balance, 1, 0) OVER (ORDER BY "Date"),
         0
       ) AS credit,
       GREATEST(
         LAG(account_balance, 1, 0) OVER (ORDER BY "Date") - account_balance,
         0
       ) AS debit,
       account_balance
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name ( "Date", account_balance ) AS
SELECT Date '2021-01-01',  1000 FROM DUAL UNION ALL
SELECT Date '2021-01-02',   500 FROM DUAL UNION ALL
SELECT Date '2021-01-03', - 200 FROM DUAL;

Outputs:

Date CREDIT DEBIT ACCOUNT_BALANCE

01-JAN-21 1000 0 1000

02-JAN-21 0 500 500

03-JAN-21 0 700 -200

Date	CREDIT	DEBIT	ACCOUNT_BALANCE
01-JAN-21	1000	0	1000
02-JAN-21	0	500	500
03-JAN-21	0	700	-200

To calculate in the opposite direction:

SELECT "Date",
       credit,
       debit,
       SUM(credit-debit) OVER (ORDER BY "Date") AS account_balance
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name ( "Date", credit, debit ) AS
SELECT Date '2021-01-01', 1000,   0 FROM DUAL UNION ALL
SELECT Date '2021-01-02',    0, 500 FROM DUAL UNION ALL
SELECT Date '2021-01-03',    0, 700 FROM DUAL;

Outputs:

Date CREDIT DEBIT ACCOUNT_BALANCE

01-JAN-21 1000 0 1000

02-JAN-21 0 500 500

03-JAN-21 0 700 -200

Date	CREDIT	DEBIT	ACCOUNT_BALANCE
01-JAN-21	1000	0	1000
02-JAN-21	0	500	500
03-JAN-21	0	700	-200

db<>fiddle here