Formulated Closing Balance Column

Transact-SQL
1

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.

2 
select 
*
,sum(cast(isnull(Debit_amount_LCY,'0.0') as money) - cast(isnull(credit_amount_LCY,'0.0') as money)) OVER(Order by Posting_date, document_no,external_document_no asc rows between unbounded preceding and current row) as [Closing BALANCE]
from
 mytable

http://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=eafb2799289737b3e6e6e92a434eb7a8