Question

Using Model-III discuss the series of SQL statements to produce a trial balance up to a particular date.

Answer 1

The following series of SQL statement retrieves a record set for producing trial balance when data base is design for Model $1$.
(i) To find the total amount by which the account have been debited. Then following SQL Statement will be formed
SELECT Debit AS Code, Sum (amount), AS Total
FROM vouchers
GROUP BY Debit;
GROUP BY Clause retrieves the rows of vouchers table accounts wise because the debit field refers to account code. This SQL statement is saved as Query $01$. The Total of debit amount in this query is given by total field with positive amounts.
(ii) To find the total amount by which the accounts have been credited.
The following SQL statement will be formed
SELECT Credit AS Code, Sum (amount)${}^{\ast }(-1)$ AS Total
FROM vouchers
GROUP BY Credit;
The sum of amount generated by Sum (amount) is multiplied by $-1$ so that the final amount assigned to total field is always negative. The purpose of using negative values is to differentiate between debit and credit totals for each account and also to facilitate the simple arithmetic summation for obtaining the net amount.
This SQL statement is saved as Query $02$.
(iii) To generate a collective record set of accounts with their debit and credit totals.
Well this collective record set will be generated by executing a union query between Query $01$ and Query $02$. The following SQL statement will be followed in this case
SELECT ${}^{\ast }$
FROM Query $01$
UNION SELECT ${}^{\ast }$
FROM Query $02$
This SQL statement is saved as Query $03$.
(iv) To generate the net amount with which an account has been debited and credited. The following SQL statement will be formed
SELECT Code, Sum (Total) AS Net
FROM Query $03$
GROUP BY Code
A positive net amount implies a debit and negative amount means a credit balance corresponding to an account code. This SQL statement is saved as Query $04$ used in generating record set for trial balance.
(v) To find that record set which consists of account code, name of account, debit amount and credit amount. The following SQL Statement will be formed
SELECT a. Code, b. Name AS [Name of Account],
IIF $(a,Net>0,a.Net,null)$ AS Debit,
IIF $(a.Net<0,abs(a.Net),null)$ AS Credit,
FROM Query $04$ AS a, Accounts ASb
WHERE a. code $=$ b.code;
This SQL statement is saved as Query $05$ for providing the necessary information content for Trial Balance Report.