Help required for some SQL commands

[sudip]

Hello All,

Suppose I want to get Debit Total and Credit Total of my Leger table. Is it possible to write SQL statements (as I did in VFP) with SQLite as follows :

Code: Select all

table := sql(mdb, "SELECT SUM(IF(TRAN_TYPE = "DEBIT", AMT, 0.00)) AS DEBITAMT, "+ ;
   "SUM(IF(TRAN_TYPE = "CREDIT", AMT, 0.00)) AS CREDITAMT FROM LEDGER WHERE VRDT >= "+;
   c2sql(mFromdt)+" AND VRDT <= "+c2sql(mTodt))

If now, can you please suggest how to do this

Thanks in advance

With best regards.

Sudip

[Rathinagiri]

SQLite doesn't have if() function. However, we can do conditional aggregation using sub-queries.

For example:

Run these sql commands. In the table code denotes the ledger account code. I had added some transactions.

Code: Select all

BEGIN TRANSACTION;
DROP TABLE IF EXISTS "txn";
CREATE TABLE "txn" ("date" DATETIME, "amount" REAL, "type" CHAR, "code" INTEGER);
INSERT INTO "txn" VALUES('2010-03-18',25000,'D',1);
INSERT INTO "txn" VALUES('2010-03-18',105000,'C',1);
INSERT INTO "txn" VALUES('2010-03-18',1050,'C',2);
INSERT INTO "txn" VALUES('2010-03-18',1150,'D',2);
INSERT INTO "txn" VALUES('2010-03-19',1500,'C',1);
COMMIT;

Now use this query to aggregate Debit total and Credit total.

Code: Select all

SELECT distinct t.code,(select sum(t1.amount) from txn t1 where t1.code = t.code and t1.type = "D") as debittotal, (select sum(t1.amount) from txn t1 where t1.code = t.code and t1.type = "C") as credittotal FROM txn t 

This works fine, however, I don't know about the time taken for this query for a very big table containing many number of transactions and ledger account codes in the production environment.

[sudip]

Rathi,

You are Great
Thanks a lot, friend.
Regards.
Sudip

[Rathinagiri]

You can do it in another way.

1. Create two temporary columns (one for debit amount and another for credit amount) with default value '0'
2. Use an update statement to post the values according to the transaction type to either debit field or credit field.
3. Now use aggregate function for debit and credit columns for debit/credit total.
4. Remove the temporary columns.

[Rathinagiri]

In my accounting applications, I use two different numeric columns (one for debit and another for credit) and don't use tran_type.

[sudip]

Rathi,
Very good suggestion

My old Clipper apps has following structure:

Code: Select all

CREATE TABLE ledger;
(vrtype n(2, 0), vrno c(10), vrdt d, acctcd1 n(4, 0), subaccd1 n(4, 0), drcr l, acctcd2 n(4, 0), subaccd2 n(4, 0), amt n(12, 2), narr c(80), duedt d)

My old VFP apps have following structure:

Code: Select all

CREATE TABLE ledger;
(accyrid c(10), lftype c(10), docno c(10), docdt d, dracctid c(10), drsubaccid c(10), cracctid c(10), crsubaccid c(10), amt y, banknm c(20), chqno c(10), chqdt d, cashedon d, narr1 c(40), narr2 c(40))

In both cases tables are strictly for double entry system And your given table structure is better

But, problem for which I was asking help is for a Payroll system. And I shall use the query for calculating monthly salary register from daily attendance table (working days, absent days, paid leave etc.) I created the example (about debit, credit) so that I can explain the problem simply

I have very little knowledge about Accounts (My major was Chemistry ). I am learning basics of accounts from my clients Yes, my clients are very good teachers for me

So, your Financial Accounting software is an excellent help for me

Thanks a lot friend.

With best regards.

Sudip

[sudip]

As per my very little knowledge of using SQL with SQLite and MySql, I found it is always better to run as many as as SQL statements in one call. May be it's due to the overhead of calling functions, preparation of SQL statements, Parsing .... etc.

VFP has a different story. Today I feel, using extensive VFP specific SQL statements, is not very good for a programmer. Using VFP SQL, one can't understand how to write a good SQL. Your source codes helped me a lot to overcome this gap

With best regards.

Sudip