I need to calculate closing balance (column to be created through sql) for each each row of my data.
The value in first row should be Debit_Amount_LCY- Credit_Amount_LCY
The value of closing balance in subsequent rows should be calculated as follows:-
Value of closing balance in previous row + Debit_Amount_LCY- Credit_Amount_LCY
The scripted form of my table is as follows:-
CREATE TABLE mytable (
Posting_Date DATE NOT NULL PRIMARY KEY
,Document_No VARCHAR(20) NOT NULL
,External_Document_No VARCHAR(26)
,Debit_Amount_LCY VARCHAR(10)
,Credit_Amount_LCY VARCHAR(11)
,Remaining_Amount VARCHAR(10) NOT NULL
);
INSERT INTO mytable (
Posting_Date
,Document_No
,External_Document_No
,Debit_Amount_LCY
,Credit_Amount_LCY
,Remaining_Amount
)
VALUES (
'31-03-2009'
,'PNX/T001714/08-09'
,'PNX/T001714/08-09'
,'10,096.68'
,NULL
,'0'
);
INSERT INTO mytable (
Posting_Date
,Document_No
,External_Document_No
,Debit_Amount_LCY
,Credit_Amount_LCY
,Remaining_Amount
)
VALUES (
'31-03-2009'
,'PNX/T001743/08-09'
,'PNX/T001743/08-09'
,'1,189.26'
,NULL
,'0'
);
INSERT INTO mytable (
Posting_Date
,Document_No
,External_Document_No
,Debit_Amount_LCY
,Credit_Amount_LCY
,Remaining_Amount
)
VALUES (
'31-03-2009'
,'PNX/T001848/08-09'
,'PNX/T001848/08-09'
,'2,957.09'
,NULL
,'0'
);
INSERT INTO mytable (
Posting_Date
,Document_No
,External_Document_No
,Debit_Amount_LCY
,Credit_Amount_LCY
,Remaining_Amount
)
VALUES (
'04-04-2009'
,'I/MFG/09-10/00001'
,'MAIL BY MANISH'
,'5,482.14'
,NULL
,'0'
);
INSERT INTO mytable (
Posting_Date
,Document_No
,External_Document_No
,Debit_Amount_LCY
,Credit_Amount_LCY
,Remaining_Amount
)
VALUES (
'17-04-2009'
,'I/TRD/09-10/00058'
,'MAIL BY MANISH'
,'506.24'
,NULL
,'0'
);
INSERT INTO mytable (
Posting_Date
,Document_No
,External_Document_No
,Debit_Amount_LCY
,Credit_Amount_LCY
,Remaining_Amount
)
VALUES (
'21-04-2009'
,'BR/HSBC/09-10/04/060'
,NULL
,NULL
,'11,285.00'
,'0'
);
INSERT INTO mytable (
Posting_Date
,Document_No
,External_Document_No
,Debit_Amount_LCY
,Credit_Amount_LCY
,Remaining_Amount
)
VALUES (
'18-05-2009'
,'BR/HSBC/09-10/05/048'
,NULL
,NULL
,'8,439.00'
,'0'
);
INSERT INTO mytable (
Posting_Date
,Document_No
,External_Document_No
,Debit_Amount_LCY
,Credit_Amount_LCY
,Remaining_Amount
)
VALUES (
'30-05-2009'
,'I/MFG/09-10/00358'
,'VERB BY CUSTOMER'
,'1,190.72'
,NULL
,'0'
);
INSERT INTO mytable (
Posting_Date
,Document_No
,External_Document_No
,Debit_Amount_LCY
,Credit_Amount_LCY
,Remaining_Amount
)
VALUES (
'25-06-2009'
,'I/MFG/09-10/00532'
,'VERB BY ARSHAD'
,'1,104.61'
,NULL
,'0'
);
INSERT INTO mytable (
Posting_Date
,Document_No
,External_Document_No
,Debit_Amount_LCY
,Credit_Amount_LCY
,Remaining_Amount
)
VALUES (
'28-07-2009'
,'BR/HSBC/09-10/07/122'
,NULL
,NULL
,'1,697.00'
,'0'
);
INSERT INTO mytable (
Posting_Date
,Document_No
,External_Document_No
,Debit_Amount_LCY
,Credit_Amount_LCY
,Remaining_Amount
)
VALUES (
'28-07-2009'
,'BR/HSBC/09-10/07/152'
,NULL
,NULL
,'1,105.00'
,'0'
);
INSERT INTO mytable (
Posting_Date
,Document_No
,External_Document_No
,Debit_Amount_LCY
,Credit_Amount_LCY
,Remaining_Amount
)
VALUES (
'19-09-2017'
,'PNX/17-18/G1647'
,NULL
,'604'
,NULL
,'604'
);
INSERT INTO mytable (
Posting_Date
,Document_No
,External_Document_No
,Debit_Amount_LCY
,Credit_Amount_LCY
,Remaining_Amount
)
VALUES (
'22-09-2017'
,'PNX/17-18/G1744'
,NULL
,'1,740.00'
,NULL
,'1,740.00'
);
INSERT INTO mytable (
Posting_Date
,Document_No
,External_Document_No
,Debit_Amount_LCY
,Credit_Amount_LCY
,Remaining_Amount
)
VALUES (
'27-09-2017'
,'PNX/17-18/G1831'
,NULL
,'17,438.00'
,NULL
,'17,438.00'
);
INSERT INTO mytable (
Posting_Date
,Document_No
,External_Document_No
,Debit_Amount_LCY
,Credit_Amount_LCY
,Remaining_Amount
)
VALUES (
'27-09-2017'
,'PNX/17-18/G1849'
,NULL
,'3,700.00'
,NULL
,'3,700.00'
);
INSERT INTO mytable (
Posting_Date
,Document_No
,External_Document_No
,Debit_Amount_LCY
,Credit_Amount_LCY
,Remaining_Amount
)
VALUES (
'27-09-2017'
,'PNX/17-18/G1850'
,NULL
,'1,361.00'
,NULL
,'1,361.00'
);
INSERT INTO mytable (
Posting_Date
,Document_No
,External_Document_No
,Debit_Amount_LCY
,Credit_Amount_LCY
,Remaining_Amount
)
VALUES (
'28-09-2017'
,'PNX/17-18/G1867'
,NULL
,'2,199.00'
,NULL
,'2,199.00'
);
INSERT INTO mytable (
Posting_Date
,Document_No
,External_Document_No
,Debit_Amount_LCY
,Credit_Amount_LCY
,Remaining_Amount
)
VALUES (
'23-10-2017'
,'PNX/17-18/G2159'
,NULL
,'4,803.00'
,NULL
,'4,803.00'
);
The desired output is as follows:-
+--------------+----------------------+-----------------------+--------------------+---------------------+------------------+-----------------+
| Posting Date | Document No. | External Document No. | Debit Amount (LCY) | Credit Amount (LCY) | Remaining Amount | Closing BALANCE |
+--------------+----------------------+-----------------------+--------------------+---------------------+------------------+-----------------+
| 31-03-2009 | PNX/T001714/08-09 | PNX/T001714/08-09 | 10,096.68 | | 0 | 10,096.68 |
| 31-03-2009 | PNX/T001743/08-09 | PNX/T001743/08-09 | 1,189.26 | | 0 | 11,285.94 |
| 31-03-2009 | PNX/T001848/08-09 | PNX/T001848/08-09 | 2,957.09 | | 0 | 14,243.03 |
| 04-04-2009 | I/MFG/09-10/00001 | MAIL BY MANISH | 5,482.14 | | 0 | 19,725.17 |
| 17-04-2009 | I/TRD/09-10/00058 | MAIL BY MANISH | 506.24 | | 0 | 20,231.41 |
| 21-04-2009 | BR/HSBC/09-10/04/060 | | | 11,285.00 | 0 | 8,946.41 |
| 18-05-2009 | BR/HSBC/09-10/05/048 | | | 8,439.00 | 0 | 507.41 |
| 30-05-2009 | I/MFG/09-10/00358 | VERB BY CUSTOMER | 1,190.72 | | 0 | 1,698.13 |
| 25-06-2009 | I/MFG/09-10/00532 | VERB BY ARSHAD | 1,104.61 | | 0 | 2,802.74 |
| 28-07-2009 | BR/HSBC/09-10/07/122 | | | 1,697.00 | 0 | 1,105.74 |
| 28-07-2009 | BR/HSBC/09-10/07/152 | | | 1,105.00 | 0 | 0.74 |
| 19-09-2017 | PNX/17-18/G1647 | | 604 | | 604 | 604.74 |
| 22-09-2017 | PNX/17-18/G1744 | | 1,740.00 | | 1,740.00 | 2,344.74 |
| 27-09-2017 | PNX/17-18/G1831 | | 17,438.00 | | 17,438.00 | 19,782.74 |
| 27-09-2017 | PNX/17-18/G1849 | | 3,700.00 | | 3,700.00 | 23,482.74 |
| 27-09-2017 | PNX/17-18/G1850 | | 1,361.00 | | 1,361.00 | 24,843.74 |
| 28-09-2017 | PNX/17-18/G1867 | | 2,199.00 | | 2,199.00 | 27,042.74 |
| 23-10-2017 | PNX/17-18/G2159 | | 4,803.00 | | 4,803.00 | 31,845.74 |
+--------------+----------------------+-----------------------+--------------------+---------------------+------------------+-----------------+
Sql Version - 2014
Please advice.