Opening And closing balance in for all Accounts

Transact-SQL
1

Hi, I have a view Where the client stores the debit and credit transactions. using this view i want to calculate the opening and closing balance of each account but the problem is the view has parent account and child account concept can you please suggest me some solution for my problem.
My database view structure is.
VOUCHER_NO, DATED, ACCOUNT_NO, DETAILS, DEBIT, CREDIT

2
1 Like
3

Sorry for my bad English or posting my question in wrong pattern

4

Here is my table similar type

CREATE TABLE Tranction(
VOUCHER_NO VARCHAR(71) NOT NULL PRIMARY KEY
,DATED DATE
,ACCOUNT_NO VARCHAR(30)
,DETAIL VARCHAR(30)
,DEBIT VARCHAR(30)
,CREDIT VARCHAR(30)
,USER VARCHAR(30)
);
INSERT INTO Tranction(VOUCHER_NO,DATED,ACCOUNT_NO,DETAIL,DEBIT,CREDIT,USER) VALUES ('BD40852 30-MAR-24 111006 CASH DEP. C/O RAUF TRADERS 5595 1000000 HANIF',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Tranction(VOUCHER_NO,DATED,ACCOUNT_NO,DETAIL,DEBIT,CREDIT,USER) VALUES ('BD40852 30-MAR-24 1191 CASH DEP. C/O RAUF TRADERS 5595 1000000 HANIF',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Tranction(VOUCHER_NO,DATED,ACCOUNT_NO,DETAIL,DEBIT,CREDIT,USER) VALUES ('BD40853 30-MAR-24 111006 CASH DEP. C/O RAUF TRADERS 5400 38250 HANIF',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Tranction(VOUCHER_NO,DATED,ACCOUNT_NO,DETAIL,DEBIT,CREDIT,USER) VALUES ('BD40853 30-MAR-24 1194 CASH DEP. C/O RAUF TRADERS 5400 38250 HANIF',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Tranction(VOUCHER_NO,DATED,ACCOUNT_NO,DETAIL,DEBIT,CREDIT,USER) VALUES ('BD40854 30-MAR-24 111006 CASH DEP. C/O RAUF TRADERS 5400 130000 HANIF',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Tranction(VOUCHER_NO,DATED,ACCOUNT_NO,DETAIL,DEBIT,CREDIT,USER) VALUES ('BD40854 30-MAR-24 1194 CASH DEP. C/O RAUF TRADERS 5400 130000 HANIF',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Tranction(VOUCHER_NO,DATED,ACCOUNT_NO,DETAIL,DEBIT,CREDIT,USER) VALUES ('BD40855 30-MAR-24 111006 CASH DEP. C/O RAUF TRADERS 1855 200000 HANIF',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Tranction(VOUCHER_NO,DATED,ACCOUNT_NO,DETAIL,DEBIT,CREDIT,USER) VALUES ('BD40855 30-MAR-24 1192 CASH DEP. C/O RAUF TRADERS 1855 200000 HANIF',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Tranction(VOUCHER_NO,DATED,ACCOUNT_NO,DETAIL,DEBIT,CREDIT,USER) VALUES ('BD40856 30-MAR-24 111006 CASH DEP. C/O RAUF TRADERS 5400 521000 HANIF',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Tranction(VOUCHER_NO,DATED,ACCOUNT_NO,DETAIL,DEBIT,CREDIT,USER) VALUES ('BD40856 30-MAR-24 1194 CASH DEP. C/O RAUF TRADERS 5400 521000 HANIF',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Tranction(VOUCHER_NO,DATED,ACCOUNT_NO,DETAIL,DEBIT,CREDIT,USER) VALUES ('S59163 28-MAR-24 111549 SALES 244100 HANIF',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Tranction(VOUCHER_NO,DATED,ACCOUNT_NO,DETAIL,DEBIT,CREDIT,USER) VALUES ('S59170 31-MAR-24 111246 SALES 84000 HANIF',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Tranction(VOUCHER_NO,DATED,ACCOUNT_NO,DETAIL,DEBIT,CREDIT,USER) VALUES ('S59170 31-MAR-24 41 SALES 84000 HANIF',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Tranction(VOUCHER_NO,DATED,ACCOUNT_NO,DETAIL,DEBIT,CREDIT,USER) VALUES ('S59171 31-MAR-24 111422 SALES 439900 HANIF',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Tranction(VOUCHER_NO,DATED,ACCOUNT_NO,DETAIL,DEBIT,CREDIT,USER) VALUES ('S59171 31-MAR-24 41 SALES 439900 HANIF',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Tranction(VOUCHER_NO,DATED,ACCOUNT_NO,DETAIL,DEBIT,CREDIT,USER) VALUES ('S59172 31-MAR-24 111422 SALES 834500 HANIF',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Tranction(VOUCHER_NO,DATED,ACCOUNT_NO,DETAIL,DEBIT,CREDIT,USER) VALUES ('S59172 31-MAR-24 41 SALES 834500 HANIF',NULL,NULL,NULL,NULL,NULL,NULL);

5

Have you tried running the code you posted?
So, your table has all the fields NULL except the first one which would need to be parsed to get the others?

1 Like
6

We cannot see your system and the data you have posted does not make sense. Also, your initial question mentioned parent and child accounts; there is no sign of this in the posted data.

1 Like
7

you need to fix your DML

INSERT INTO Tranction(VOUCHER_NO,DATED,
ACCOUNT_NO,DETAIL,DEBIT,CREDIT,[USER]) 
VALUES ('S591', '31-MAR-24', 1, 'SALES', 834500, 'HANIF');
8

I think everyone understood that he had made a mess in his DML, but keep in mind that the VOUCHER_NO VARCHAR(71) field was declared as NOT NULL PRIMARY KEY.
So, your proposal is not correct either.

1 Like