Hitung Balance query MySQL


SQL Dump

CREATE TABLE `accountdeposit` (
`id` int(11) NOT NULL auto_increment,
`memCode` char(9) NOT NULL,
`transCode` char(100) NOT NULL,
`stat` enum(‘D’,’C’) NOT NULL,
`balance` float(9,3) NOT NULL,
`transDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`author` int(11) NOT NULL,
PRIMARY KEY  (`id`),
KEY `memCode` (`memCode`)
);

insert  into `accountdeposit`(`id`,`memCode`,`transCode`,`stat`,`balance`,`transDate`,`author`) values (5,’0321001′,’Buka Account’,’C’,100000.000,’2009-03-21 00:05:44′,1),(6,’0321001′,’open list 3 Form’,’D’,15000.000,’2009-03-23 10:51:16′,1),(7,’0321001′,’open list 4 Form’,’C’,20000.000,’2009-03-23 13:05:00′,1);

Query:

select transCode,
case when stat=’D’ then (-balance) else 0 end as Debit,
case when stat=’C’ then balance else 0 end as Credit,
(@LB := @LB + if (stat=’D’, -balance, balance)) as LastBalance
from (select @LB := 0) as dddd, accountdeposit
where memCode = ‘0321001’
group by memcode, transdate
order by transdate;

Hasil :

zzz

 

 

 

Thnks.2.Manztiara n NorYahya

About blackphp

All about programming php, ajax, jquery, mysql, etc, tips n trick. Now, i'm concern in php programming with codeigniter framework n also training my design skill with photoshop or corel... wish me luck! ('o')

Posted on October 31, 2009, in mYsQL. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: