Question

Briefly explain the set of SQL statements to produce the receipts side of a cash book for Model-I.

Answer 1

The following are the set of SQL statements that are used to produce the receipts side of a Cash Book for Model-I.

Step1: To Ascertain the Total Amount by which the Cash Account is Debited

In order to find out the total amount by which Cash Account has been debited in all the accounts the SELECT clause is executed. This clause has two following fields.

(a) A code to identify the account

(b) A code to generate the total by which the account has been debited

SQL Statement

SELECT Credit As Code, Amount, Date

FROM Vouchers

WHERE Debit Like “231*

This SQL statement is saved as query 01.

This statement provides the code and amount of cash received.

Step2: To Generate the Total of Receipts Side

SQL Statement

SELECT Code Sum (Amount) As Total

FROM Query 01

GROUP BY Code

This SQL statement is saved as Query 02

Step3: To Generate the Record Set Consisting of Account Code, Name of Account, Credit Amount and Date

SQL Statement

SELECT a.code b.name As [Name of Account], IIF (a = Total > 0, a. Total, null as Amount)

FROM Query 02 As a Accounts As b

WHERE a code=b code