SQL Database Table to Perl Script

[prithviraj]

I have a 'transaction' table created in SQL like this:

                
TranID  Date        AccNum   Type    Amount ChequeNo DDNo  
657520  02-07-1999  0181432  Debit   16000  465774     
657524  02-07-1999  0181432  Debit   13000           569086
657538  09-07-1999  0181432  Credit  11000  
657548  18-07-1999  0181432  Credit  15500  
657519  02-07-1999  0181432  Debit   12000  
657523  02-07-1999  0181432  Credit  11000  
657529  03-07-1999  0181432  Debit   15000  466777
657539  10-07-1999  0181432  Credit  10000  
657541  11-07-1999  0181432  Debit   12000  
657525  03-07-1999  0181432  Debit   15000           569999
657533  05-07-1999  0181432  Credit  12500

The question is: Query data from transaction table and Calculate the total debit amount and total credit amount for each account. My script is like this:

#!/usr/bin/perl
use DBI;
use strict;
use warnings;
$dbh = DBI->connect('dbi:database','prithvi','prithvi') or die "Couldn
+'t connect";
my $tran_cur = $dbh->prepare("SELECT AccountNumber, Type, SUM(Amount) 
+FROM transaction GROUP BY AccountNumber, Type;");
$tran_cur->execute;
print "<table><tr>";
map {print "<td>$_</td>"}qw(Account Number-Total Debit Amount);
print "<br/>";
print "</tr>";
print "</table>";
while( my @data = $tran_cur->fetchrow_array)
{
 my $rec = join ('-',@data);
 print "$rec\n";
}
$dbh->disconnect;

I am getting the exact output for this. But I have two more questions.

1) Query data from transaction table and calculate the total  balance 
+in each account
2) Query data from transaction table and calculate total amount debite
+d by cheque, dd and by cash for each account.

How can I do this? I just have to replace the SQL query in the above script for the 2 new questions. Please help. Thanks in advance

[Polyglot]

This is really an SQL question. Are you using MySQL? If so, they have some helpful pages online, e.g. HERE for how to construct your queries (though I must say there's a bit of a learning curve to figure out their "help"). Whichever database you're using should be able to do the calculation you need right from the query.

[james2vegas]

Just include in your SELECT and GROUP BY your additional fields.

[prithviraj]

What are the additional fields Sir? Please help with the full SQL query.

[james2vegas]

I do not know the additional fields (how would I). They would be in your schema.

[roboticus]

prithviraj:

You'll want something like:

-- compute the totals for each account:
select AccNum, sum(DB_amt) ttl_DB_amt, sum(CR_amt) ttl_CR_amt
from (
   -- transform the table from (accnum, type, amount) into
   -- (accnum, cr_amt, db_amt)
   select accnum,
          case when type='Credit' then Amount else 0 end CR_amt,
          case when type='Debit'  then Amount else 0 end DB_amt
   from YOUR_TABLE_NAME
   where CHOOSE_WHICH_ACCOUNT(S)_YOU_WANT
)
group by AccNum

The inner select converts your table into a simpler view of account number, credit amount and debit amount. The outer select generates the totals for the simple view.

Update: For your other questions, simply make the appropriate view transformations and accumulate the totals as above.

...roboticus

When your only tool is a hammer, all problems look like your thumb.

[erix]

You say:

I am getting the exact output for this.

But how is that possible when the script doesn't compile?

[Generoso]

Maybe this is what you are looking for.

!/usr/bin/perl

use DBI;
use strict;
use warnings;
my $dbh = DBI->connect('dbi:mysql:database','user','password') or die 
+"Couldn't connect";
my @sql =();
$sql[0] = qq[SELECT AccNum as Account, max(date) as Date, Type, count(
+*) as Total_No, sum(Amount) as Amount
FROM transaction GROUP BY AccNum, Type
union
SELECT AccNum, max(date),'Balance', count(*), sum(if(type='Debit',-1*A
+mount,Amount))
FROM transaction GROUP BY AccNum;];
$sql[1] = qq[SELECT AccNum as Account, max(date) as Date, Type, 'CHEQU
+ENO' as Subtype, count(*) as Total_No, sum(Amount) as Amount
FROM transaction where chequeno is not null GROUP BY AccNum, Type
union
SELECT AccNum as Account, max(date) as Date, Type, 'DDNO' as Subtype, 
+count(*) as Total_No, sum(Amount) as Amount
FROM transaction where DDNO is not null GROUP BY AccNum, Type;];
$sql[2] = qq[desc transaction;];
open (FH, ">".$ENV{"TMP"}."\\mytmp.html") || die "Cannot open temporar
+y file: $!\n";
print FH '<HTML><Body>';
foreach (@sql) {
    my $tran_cur = $dbh->prepare($_);
    $tran_cur->execute;
    print FH '<table border=1 cellspacing=0 cellpadding=2><tr>';
    map {print FH "<td>$_</td>"}@{$tran_cur->{NAME}};
    print FH "</tr><br/>";
    while( my @data = $tran_cur->fetchrow_array)
    {
        print FH "<tr>";
        map {print FH "<td>$_</td>"}@data;
        print FH "</tr>";
    }
    print FH "</table>";
    }
print FH "</Body></HTML>\n";
my $url = "file://".$ENV{"TMP"}."\\mytmp.html";
my $commandline = qq{start "$url" "$url"};
system($commandline) == 0
    or die qq{Couldn't launch '$commandline': $!/$?};
close FH;    
$dbh->disconnect;
sleep(5);
unlink $ENV{"TMP"}."\\mytmp.html" or warn "Could not unlink ".$ENV{"TM
+P"}."\\mytmp.html: $!";

Result:




Account	Date	Type	Total_No	Amount
181432	1999-07-18	Credit	5	60000.00
181432	1999-07-11	Debit	6	83000.00
181432	1999-07-18	Balance	11	-23000.00




Account	Date	Type	Subtype	Total_No	Amount
181432	1999-07-03	Debit	CHEQUENO	2	31000.00
181432	1999-07-03	Debit	DDNO	2	28000.00




Field	Type	Null	Key	Default	Extra
TranID	int(10) unsigned	NO	PRI
Date	date	NO
AccNum	int(10) unsigned	NO
Type	enum('Credit','Debit')	NO
Amount	decimal(10,2)	NO
ChequeNo	int(10) unsigned	YES
DDNo	int(10) unsigned	YES