Create Trial Balance via Sql

ahmed_one

Member
Joined
Nov 29, 2012
Messages
23
Programming Experience
Beginner
Dear All Experts

I am new to Sql, and need some guidance to create a Trial Balance via Sql query in MySql. Consider the following scenario:

Two Tables:

1.Accounts
2.Transactions

Accounts Table fields details:

AccNo (PK)(varchar) (5)
AccName (varchar)(50)
AccOpBal (double)

Transactions Table fields details:

TransID (int) (Auto Increment) (PK)
AccNo (varchar) (5)
TransDt (DateTime)
TransDebit (Double)
TransCredit (Double)

Now I need a SQL query based on transDt date range(for e.g 01st Jan-14 to 31sth Jan-2014) which will return:

AccNo
AccOpBal
TransDebit (Sum of monthly transaction i.e Jan-2014)
TransCredit (Sum of monthly transaction i.e Jan-2014)
TransDebit (Sum of Yearly transaction i.e from 01st July-2013 to 31st Jan 2014 or YTD)
TransCredit (Sum of Yearly transaction i.e from 01st July-2013 to 31st Jan 2014 or YTD)

It is not necessary that every AccNo has opening balance (AccOpBal), likewise, it is also not necessary that every AccNo has transactions (TransDebit or TransCredit). But if an AccNo has any, it should be in query.

Any suggestion would be much appreciated.

Ahmed
 
Top Bottom