Describe the series of SQL statements to produce trial balance data base design for Model-II is used.
Describe the series of SQL statements to produce trial balance data base design for Model-II is used.
In the following set of SQL statement, the accounts have been categorised within the Trial Balance according to the Account Type as: Expenses, Revenues, Assets and Liabilities.
The below mentioned series of SQL Statement retrieve the record set for producing Trial Balance when database design for Model-II is used.
SQL Statements
1. To ascertain the total amount by which the accounts have been debited
The transacted accounts have been stored AccCode of vouchers main and code of Vouchers Details.
SQL Statement for Vouchers Details
SELECT Code, SUM (amount), As total
FROM vouchers Main INNER JOIN voucher Details
ON Voucher Main. VNO=voucher Details. VNO
WHERE Type=0
GROUP BY Code;
SQL Statement for Vouchers Main Table
SELECT AccCode As Code, sum (amount), As total
FROM vouchers Main INNER JOIN voucher Details ON
Voucher Main. VNO=vouchers Details. VNO
WHERE Type=1
GROUP BY AccCode;
Now, we need to join the above mentioned SQL statements using UNION clause. This is done by the SQL command.
SQL Statement for Merging Voucher Details and Vouchers Main Table
SELECT Code, sum (amount), As total
FROM vouchers Main INNER JOIN voucher Details ON
Voucher Main. VNO=voucher Details. VNO
WHERE Type=0
GROUP BY Code;
UNION ALL
SELECT AccCode As Code, sum (amount), As total
FROM vouchers Main INNER JOIN voucher Details ON
Voucher Main. VNO=voucher Details. VNO
WHERE Type=1
GROUP BY AccCode;
This SQL Statement is saved on Query 01 for its subsequent use.
The total of debit amount in this query represents a positive total.
2. To ascertain the total amount by which the accounts have been credited
The following series of SQL statements help in ascertaining the total by which every transacted account has been credited.
SELECT Code, sum (amount)* –1, As total
FROM vouchers Main INNER JOIN voucher Details ON
Voucher Main. VNO=voucher Details. VNO
WHERE Type=1 GROUP BY Code, Amount
UNION
SELECT AccCode As Code, sum (amount)* –1, As total
FROM vouchers Main INNER JOIN voucher Details ON
Voucher Main. VNO=vouchers Details. VNO
WHERE Type=0 GROUP BY AccCode, Amount;
We can see that the total of the credit amount has been multiplied by (–) 1. This is to ensure that the total credit amount should be in negative on contrary to the positive total of debit.
This query is saved as Query 02.
3. To compute a collective record set of accounts with their debit and credit totals
In order to pool out a collective record set of accounts, a union query between the saved Query 01 and Query 02 is passed. This is done to ensure that the debit and the credit amount with respect to each account become available for generating the net amount (Debit – Credit).
The required SQL Statement for Union query
SELECT*
FROM Query 01
UNION Select*
FROM Query 02
This statement results in a horizontal merger of the above queries, viz. Query 01 and Query 02. This SQL statement is saved as Query 03.
4. To ascertain the net amount with which an account has been debited or credited
SQL Statement
SELECT Code, Sum (total), As Net
FROM Query 03
UNION Select*
GROUP BY Code;
This SQL statement is saved as Query 04.
5. To form the record set which consists of Account Code, Name of Account, Debit Amount and Credit Amount
This query provides relevant information to the Trial Balance report.
SQL Statement
SELECT a. Code b. name As [Name of Account],
IIF (a. Net>0, null) As Debit, IIF (a. Net<0, abs(a.Net), null)
As Credit FROM Query 04 As a, Account As b/
WHERE a.code=b.code;
This SQL statement is saved as Query 05
Trial Balance with sorting and Grouping levels
6. To ascertain the record set of accounts with their category and category ID
SQL Statement
SELECT Accounts.Code, Accounts Name, Category, CatId
FROM Accounts
INNER JOIN Account Type ON
Accounts. Type=Account type, CatId;
This SQL statement is saved as Query 06.
7. To ascertain the record set consisting of Account Code, Name of Account, Debit Amount and Credit Amount along with their category details
SQL Statement
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, Category, CatId
FROM Query 04 As a, Query 06 As b
WHERE a.code=b.code;
This SQL statement is saved as Query 07. This query provides information details for designing Trial Balance with grouping and sorting of the accounts.
In the following set of SQL statement, the accounts have been categorised within the Trial Balance according to the Account Type as: Expenses, Revenues, Assets and Liabilities.
The below mentioned series of SQL Statement retrieve the record set for producing Trial Balance when database design for Model-II is used.
SQL Statements
1. To ascertain the total amount by which the accounts have been debited
The transacted accounts have been stored AccCode of vouchers main and code of Vouchers Details.
SQL Statement for Vouchers Details
SELECT Code, SUM (amount), As total
FROM vouchers Main INNER JOIN voucher Details
ON Voucher Main. VNO=voucher Details. VNO
WHERE Type=0
GROUP BY Code;
SQL Statement for Vouchers Main Table
SELECT AccCode As Code, sum (amount), As total
FROM vouchers Main INNER JOIN voucher Details ON
Voucher Main. VNO=vouchers Details. VNO
WHERE Type=1
GROUP BY AccCode;
Now, we need to join the above mentioned SQL statements using UNION clause. This is done by the SQL command.
SQL Statement for Merging Voucher Details and Vouchers Main Table
SELECT Code, sum (amount), As total
FROM vouchers Main INNER JOIN voucher Details ON
Voucher Main. VNO=voucher Details. VNO
WHERE Type=0
GROUP BY Code;
UNION ALL
SELECT AccCode As Code, sum (amount), As total
FROM vouchers Main INNER JOIN voucher Details ON
Voucher Main. VNO=voucher Details. VNO
WHERE Type=1
GROUP BY AccCode;
This SQL Statement is saved on Query 01 for its subsequent use.
The total of debit amount in this query represents a positive total.
2. To ascertain the total amount by which the accounts have been credited
The following series of SQL statements help in ascertaining the total by which every transacted account has been credited.
SELECT Code, sum (amount)* –1, As total
FROM vouchers Main INNER JOIN voucher Details ON
Voucher Main. VNO=voucher Details. VNO
WHERE Type=1 GROUP BY Code, Amount
UNION
SELECT AccCode As Code, sum (amount)* –1, As total
FROM vouchers Main INNER JOIN voucher Details ON
Voucher Main. VNO=vouchers Details. VNO
WHERE Type=0 GROUP BY AccCode, Amount;
We can see that the total of the credit amount has been multiplied by (–) 1. This is to ensure that the total credit amount should be in negative on contrary to the positive total of debit.
This query is saved as Query 02.
3. To compute a collective record set of accounts with their debit and credit totals
In order to pool out a collective record set of accounts, a union query between the saved Query 01 and Query 02 is passed. This is done to ensure that the debit and the credit amount with respect to each account become available for generating the net amount (Debit – Credit).
The required SQL Statement for Union query
SELECT*
FROM Query 01
UNION Select*
FROM Query 02
This statement results in a horizontal merger of the above queries, viz. Query 01 and Query 02. This SQL statement is saved as Query 03.
4. To ascertain the net amount with which an account has been debited or credited
SQL Statement
SELECT Code, Sum (total), As Net
FROM Query 03
UNION Select*
GROUP BY Code;
This SQL statement is saved as Query 04.
5. To form the record set which consists of Account Code, Name of Account, Debit Amount and Credit Amount
This query provides relevant information to the Trial Balance report.
SQL Statement
SELECT a. Code b. name As [Name of Account],
IIF (a. Net>0, null) As Debit, IIF (a. Net<0, abs(a.Net), null)
As Credit FROM Query 04 As a, Account As b/
WHERE a.code=b.code;
This SQL statement is saved as Query 05
Trial Balance with sorting and Grouping levels
6. To ascertain the record set of accounts with their category and category ID
SQL Statement
SELECT Accounts.Code, Accounts Name, Category, CatId
FROM Accounts
INNER JOIN Account Type ON
Accounts. Type=Account type, CatId;
This SQL statement is saved as Query 06.
7. To ascertain the record set consisting of Account Code, Name of Account, Debit Amount and Credit Amount along with their category details
SQL Statement
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, Category, CatId
FROM Query 04 As a, Query 06 As b
WHERE a.code=b.code;
This SQL statement is saved as Query 07. This query provides information details for designing Trial Balance with grouping and sorting of the accounts.
