Accounts balances

11 posts
Accounts balances

Nikolay
MMEX Developer

Posts: 2270
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
Just algoritm. The same results you can see on the home page.

Code: Select all
    select * from (
    select a.accountname as AccountName
    , ifnull(a.initialbal,0) as initialbal
    , round (ifnull (i.amount,0)+ifnull (o.toamount,0)+ifnull (a.initialbal,0),2) as balance
    , c.baseconvrate
    , c.currency_symbol
    , a.notes
    from  accountlist_v1 a
    left join currencyformats_v1 c on c.currencyid=a.currencyid
    left join (select sum (case transactiontype when 'Transfer' then -amount else amount end) as amount, accountid from alldata where status not in ('V') group by accountid ) i on a.accountid=i.accountid
    left join (select sum (totransamount) as toamount, toaccountid from alldata where transactiontype='Transfer' and status not in ('V') group by toaccountid ) o on a.accountid=o.toaccountid
    where a.accounttype='Checking' and a.status<>'Closed'
    group by a.accountname
    order by a.accountname) t
   
    union all
    select 'Total:'
    , sum (ifnull(a.initialbal,0)) as initialbal
    , round ( sum (ifnull (i.amount,0)+ifnull (o.toamount,0)+ifnull (a.initialbal,0)),2) as balance
    , ''
    , bc.currencyname
    , a.notes
    from  accountlist_v1 a
    left join currencyformats_v1 c on c.currencyid=a.currencyid
    left join (select sum (case transactiontype when 'Transfer' then -amount else amount end) as amount, accountid from alldata where status not in ('V') group by accountid ) i on a.accountid=i.accountid
    left join (select sum (totransamount) as toamount, toaccountid from alldata where transactiontype='Transfer' and status not in ('V') group by toaccountid ) o on a.accountid=o.toaccountid
    left join infotable_v1 info on info.infoname='BASECURRENCYID'
    left join currencyformats_v1 bc on info.infovalue=bc.currencyid
    where a.accounttype='Checking' and a.status<>'Closed'
   
   
Re: Accounts balances

yampis
New MMEX User

Posts: 12
Joined: Sun Jul 19, 2009 2:41 pm
Summary about currencies. Updated version of your SQL. Actually, this summary I'd like to see in main page under accounts.


Code: Select all
select detail.Currency as Currency, sum(detail.Amount) as TotalAmount from (select a.accountname as AccountName ,round(ifnull((select sum(amount*(case transactiontype when 'Transfer' then -1 else 1 end)) from alldata where accountid=a.accountid and  status not in ('V','D')),0) +a.initialbal +ifnull((select sum(ifnull(totransamount,0)) from alldata where toaccountid=a.accountid and  status not in ('V','D')),0),2) as Amount , c.currency_symbol as Currency from  accountlist_v1 a left join currencyformats_v1 c on c.currencyid=a.currencyid where a.accounttype='Checking' and a.status<>'Closed' group by a.accountname) as detail group by Currency order by Currency
Re: Accounts balances

Nikolay
MMEX Developer

Posts: 2270
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
Code: Select all
    select detail.Currency as Currency, sum(detail.Amount) as TotalAmount
    from (select a.accountname as AccountName ,
    round((
    select total(amount*(case transactiontype when 'Transfer' then -1 else 1 end))
    from alldata
    where accountid=a.accountid and  status not in ('V')) +a.initialbal +(
   
    select total(ifnull(totransamount,0)) from alldata where toaccountid=a.accountid and  status not in ('V')),2) as Amount ,
    c.currency_symbol as Currency from  accountlist_v1 a left join currencyformats_v1 c on c.currencyid=a.currencyid
    where a.accounttype in ('Checking','Term') and a.status<>'Closed' group by a.accountname) as detail
    group by Currency order by Currency
Re: Accounts balances

rajeesh
New MMEX User

Posts: 1
Joined: Sat Oct 29, 2011 1:19 pm
Dear,
Is it possible to see the account balance for specific date ?
Re: Accounts balances

Nikolay
MMEX Developer

Posts: 2270
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
This sql script will return balance for today for account id=21
Code: Select all
select ifnull(total, 0) + ifnull(toamount,0) + INITIALBAL as BALANCE from
(
    select a.INITIALBAL,
    total(case transcode when 'Deposit' then transamount else -transamount end) as total, o.toamount
    from checkingaccount_v1 c
    left join
        (select accountid, total(totransamount) as toamount, toaccountid
        from checkingaccount_v1 where transcode='Transfer' and status not in ('V')
            and TRANSDATE <= date('now', 'localtime') -- date('2011-11-01')
        group by toaccountid) o on o.toaccountid=c.accountid
 left join accountlist_v1 a on a.ACCOUNTID=c.accountid
where c.status not in ('V')
    and c.TRANSDATE <= date('now', 'localtime') -- date('2011-11-01')
    and c.accountid=21
group by c.accountid
)

Use, for example for first of november 2011, date('2011-11-01') instead date('now', 'localtime')
Re: Accounts balances

kayaleee
New MMEX User

Posts: 4
Joined: Wed Feb 01, 2012 12:27 pm
MMX.JPG
(20.04 KiB) Downloaded 6243 times
I try to get the balance account for 1 specific date but not working fine ... it wrote only balance and nothing below ... and what is the account id=21 ?

:cry:
Re: Accounts balances

Nikolay
MMEX Developer

Posts: 2270
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
try this code:
Code: Select all
select
accountname,
ifnull(total, 0) + ifnull(toamount,0) + INITIALBAL as BALANCE from
(
    select
a.accountname,
a.INITIALBAL,
    total(case transcode when 'Deposit' then transamount else -transamount end) as total, o.toamount
    from checkingaccount_v1 c
    left join
        (select accountid, total(totransamount) as toamount, toaccountid
        from checkingaccount_v1 where transcode='Transfer' and status not in ('V')
            and TRANSDATE <=date('2011-11-01')
        group by toaccountid) o on o.toaccountid=c.accountid
 left join accountlist_v1 a on a.ACCOUNTID=c.accountid
where c.status not in ('V')
    and c.TRANSDATE <= date('2011-11-01')
group by c.accountid
)

Change
date('2011-11-01')
to any othe date
Re: Accounts balances

kayaleee
New MMEX User

Posts: 4
Joined: Wed Feb 01, 2012 12:27 pm
Bonjour,
Merci beaucoup - it works fine now

Have a nice evening
Cheers
Kayaleee
Re: Accounts balances

Nikolay
MMEX Developer

Posts: 2270
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
I have a bit updated the script. Please reload it.
Re: Accounts balances

eleanorb
Super MMEX User

Posts: 60
Joined: Sat Jan 14, 2012 9:42 am
How can I get the account balance using only reconciled transactions?

Eleanor
Who is online

Users browsing this forum: No registered users and 1 guest

cron