SQL Query Repository
Navigating the Mifos database and understanding the Mifos schema has a steep learning curve. There's a large amount of rich and powerful data in Mifos that can be difficult to extract if you're not familiar with the data model. This page is targeted towards Mifos Users, Specialists, report-writers, and developers needing help extracting information from the database.
Use the queries on this page to:
Build custom reports
Quickly find information for management or Mifos users without having to build a new report.
Find specific information that's "hiding" in the Mifos database.
Help Build our Query Repository
Request a Query - If you're a user or specialist seeking information, feel free to request a query by entering in the name of the query and a description of what client/portfolio information you need to extract from the database and your name in the requested by field.
Submit a Query - Written reports or have useful queries you'd like to share with the community? Want to help answer an outstanding query request? Add it here in the appropriate table (if it's run against Mifos database (and what version) or run against the data warehouse).
These queries can be inserted into your report designer tool or run directly from MySQL (through SQL Development, Query Browser, etc) to quickly pull up clients and other needed data.
See also: Mifos database naming conventions
Database Query Repository
Queries listed here reflect the data model for the Mifos database.
Query | Additional Description: | Applies to Database Version | Date Added | Requested By | Submitted By |
|---|---|---|---|---|---|
Number of Loans outstanding
select count(*) as loans_currently_in_good_and_bad_standing
from account a
where a.account_state_id in (5,9)
Number of Loans outstanding, by Branch select office.DISPLAY_NAME AS Branch,
count(account.ACCOUNT_ID) as loans_currently_in_good_and_bad_standing
from account
INNER JOIN office ON account.OFFICE_ID = office.OFFICE_ID
where account.account_state_id in (5,9)
GROUP BY BranchNumber of Loans Outstanding, by Branch and by Loan Officer select office.DISPLAY_NAME AS Branch,
lookup_value.LOOKUP_NAME AS Title,
personnel.DISPLAY_NAME AS LOAN_OFFICER,
count(account.ACCOUNT_ID) as loans_currently_in_good_and_bad_standing
from account
INNER JOIN office ON account.OFFICE_ID = office.OFFICE_ID
INNER JOIN personnel ON account.PERSONNEL_ID = personnel.PERSONNEL_ID
INNER JOIN lookup_value ON personnel.TITLE = lookup_value.LOOKUP_ID
where account.account_state_id in (5,9)
GROUP BY Branch, LOAN_OFFICER
|
|
| Nov 11, 2010 |
| John Woodloc, Ryan Whitney |
Branchwise outstanding principal as of today
select o.office_id, o.display_name, sum(orig_principal - principal_paid) as principal_outstanding
from loan_summary ls
join account a on a.account_id = ls.account_id
join office o on o.office_id = a.office_id
where a.account_state_id in (5,9)
group by o.office_id , o.display_name
|
|
| Nov 11, 2010 |
| John Woodlock |
Branch-wise new loans (count and amount) disbursed during a period
select count(loan_account.account_id),sum(account_trxn.amount),account.office_id,office.display_name
from office
inner join account on account.office_id=office.office_id
inner join loan_account on account.account_id=loan_account.account_id
inner join account_trxn on account_trxn.account_id=account.account_id
where account_trxn.account_action_id=10
and date(ifnull(account_trxn.action_date,account_trxn.created_date)) between adddate(curdate(),-120) and curdate()
and account_trxn.account_trxn_id not in
(select related_trxn_id
from account_trxn
where account_trxn.account_action_id=19)
group by account.office_id
order by office.display_name
In place of adddate(curdate(),-120) and curdate(), you can place your from and to dates. |
|
| Nov 11, 2010 |
| Ramya Toshniwal |
Loan outstanding for each member with its group name, center name, branch name i.e. full path select(select d.display_name from office d where d.office_id=c.branch_id) as $Branch_Name,
BigBoss.display_name as $Center, Boss.display_name as
$Group ,
c.Global_cust_num as Cstmr_ID,c.display_name as Mem_Name,cn.display_name as Husband_name ,a.global_account_num as globalAccountNum,
po.prd_offering_short_name as prdOfferingName,
sa.disbursement_date as Disbus_Date,
min( loanactivi0_.balance_principal_amount) as Outstanding
from loan_account sa
inner join account a on a.account_id = sa.account_id
inner join account_state ast on ast.account_state_id = a.account_state_id
inner join prd_offering po on po.prd_offering_id = sa.prd_offering_id
inner join customer c on c.customer_id = a.customer_id
inner join lookup_value lv on lv.lookup_id = ast.lookup_id
inner join loan_activity_details loanactivi0_ on loanactivi0_.account_id = a.account_id
inner join customer AS Boss on c.parent_customer_id=Boss.customer_id
inner join customer AS BigBoss on Boss.parent_customer_id=BigBoss.customer_id
inner join customer_family_detail as cf on cf.customer_id = c.customer_id
inner join customer_name_detail as cn on cf.customer_name_id = cn.customer_name_id
group by Cstmr_ID,c.display_name, globalAccountNum,prdOfferingName, Disbus_Date
ORDER BY 1,2,3,4,5 limit 100000
|
|
|
|
| Trilok J. Pandya |
Number of loans disbursed and amount disbursed during period
SELECT ac.office_id,
office.DISPLAY_NAME as OFFICE_NAME,
COUNT(DISTINCT(LA.ACCOUNT_ID)) LOANS_DISBURSED,
SUM(COALESCE(LOAN_AMOUNT,0)) LOAN_AMOUNT_DISBURSED
FROM LOAN_ACCOUNT LA
INNER JOIN ACCOUNT AC ON LA.ACCOUNT_ID= AC.ACCOUNT_ID
INNER JOIN customer C ON C.CUSTOMER_ID=AC.CUSTOMER_ID
INNER JOIN office ON AC.OFFICE_ID = office.OFFICE_ID
WHERE AC.ACCOUNT_STATE_ID NOT IN ('10','2','3')
AND (LA.DISBURSEMENT_DATE between '2010-01-05' AND '2011-03-10')
GROUP BY AC.OFFICE_ID
| Added in display of Branch Name | | Nov 11, 2010 |
| Lokesh Sajjan & Ramesh Nayak |
Number of loans disbursed and amount disbursed during period, group by Branch and Loan Officer |
SELECT ac.office_id,
office.DISPLAY_NAME as OFFICE_NAME,
lookup_value.LOOKUP_NAME AS Title,
personnel.DISPLAY_NAME AS LOAN_OFFICER,
COUNT(DISTINCT(LA.ACCOUNT_ID)) LOANS_DISBURSED,
SUM(COALESCE(LOAN_AMOUNT,0)) LOAN_AMOUNT_DISBURSED
FROM LOAN_ACCOUNT LA
INNER JOIN ACCOUNT AC ON LA.ACCOUNT_ID= AC.ACCOUNT_ID
INNER JOIN customer C ON C.CUSTOMER_ID=AC.CUSTOMER_ID
INNER JOIN office ON AC.OFFICE_ID = office.OFFICE_ID
INNER JOIN personnel ON AC.PERSONNEL_ID = personnel.PERSONNEL_ID
INNER JOIN lookup_value ON personnel.TITLE = lookup_value.LOOKUP_ID
WHERE AC.ACCOUNT_STATE_ID NOT IN ('10','2','3')
AND (LA.DISBURSEMENT_DATE between '2010-01-05' AND '2011-03-10')
GROUP BY AC.OFFICE_ID, LOAN_OFFICER
| | 1.6.2 | | | Ryan Whitney
|
Branchwise outstanding principal as of today
select loanamount.officeid as officeid,sum(COALESCE(loanamount.loanamt,0)) asloanamt,sum(COALESCE
(repaidamt.principalpaidamt,0)) as principalpaidamt,
(sum(COALESCE(loanamount.loanamt,0))-sum(COALESCE(repaidamt.principalpaidamt,0))) as
principaloutstandingamt
from account
join
(select ac.account_id as ai, COALESCE(la.loan_amount,0) as loanamt,ac.office_id as officeid
from account ac,loan_account la
where ac.account_id=la.account_id
and ac.account_state_id not in(10,2,3)
and la.disbursement_date <=CURDATE())loanamount
on account.account_id=loanamount.ai
left join
(select ac.account_id as ai, sum(COALESCE(ls.principal_paid,0)) as principalpaidamt
from account ac,loan_schedule ls
where ac.account_id=ls.account_id
and ac.account_state_id not in(10,2,3)
and ls.payment_status='1'
and (ls.payment_date is not null and ls.payment_date<=CURDATE())
group by ls.account_id)repaidamt
on account.account_id=repaidamt.ai
group by account.office_id
|
|
| Nov 11, 2010 |
| Lokesh Sajjan & Ramesh Nayak |
Active Loans by Date
SELECT count(if((account_1.customer_id is null),
account.CUSTOMER_ID, account_1.CUSTOMER_ID)) as ACTIVE_LOANS,
account.OFFICE_ID
FROM account account_1 RIGHT OUTER JOIN loan_account loan_account_1 ON
account_1.ACCOUNT_ID = loan_account_1.ACCOUNT_ID
RIGHT OUTER JOIN loan_account
INNER JOIN account_trxn
ON loan_account.ACCOUNT_ID = account_trxn.ACCOUNT_ID
INNER JOIN account
ON loan_account.ACCOUNT_ID = account.ACCOUNT_ID
ON loan_account_1.PARENT_ACCOUNT_ID = loan_account.ACCOUNT_ID
WHERE account_trxn.ACCOUNT_ACTION_ID = 10
AND ACCOUNT.ACCOUNT_STATE_ID<>10
AND loan_account.DISBURSEMENT_DATE<= @REPORTDATE
and (Account.Closed_date is null or Account.Closed_date> @REPORTDATE )
GROUP BY account.OFFICE_ID
This will work for MFIs that don't use back-dated transactions. See mailing list discussion on 7/28. |
|
| Jul 28, 2010 |
| Lassâad Ben Hadj |
Active loans as on a certain date
select a.account_id,
if(ppaid.amt is null,la.loan_amount, la.loan_amount - ppaid.amt) as
amt_due,
if (c.discriminator='GROUP',t1.num,1) as num
from
account a
join
loan_account la
on a.account_id=la.account_id
left outer join
(select account_id, sum(if(principal_paid is null,0,principal_paid))
as amt
from loan_schedule ls
where ls.payment_date <= @REPORTDATE
group by account_id) as ppaid
on la.account_id = ppaid.account_id
left outer join
(select count(a.account_id) as num,la.parent_account_id account_id
from loan_account la ,account a
where la.parent_account_id=a.account_id
and a.account_state_id in (5,9,6,7)
group by la.parent_account_id) as t1
on t1.account_id=la.account_id
join customer c
on c.customer_id=a.customer_id
where if(ppaid.amt is null, la.loan_amount, la.loan_amount -
ppaid.amt) > 0
and a.account_state_id in (5,6,7,9)
and la.disbursement_date <= @REPORTDATE
and a.account_type_id = 1
|
|
| Jul 28, 2010 |
| Sam Birney |
Active Clients on a particular day
select allActive.clients - notActive.clients as activeClients
from (select customer.customer_level_id as level_id,
count(*) as clients from customer
where customer_level_id=1
and customer_activation_date < '2010-07-10'
) allActive,
(select count(*) as clients
from (select *
from (select entity_id as client_id,new_value
from change_log,change_log_detail
where change_log.change_log_id=change_log_detail.change_log_id
and entity_type=1 and field_name='Status'
and new_value != 'Active'
and changed_date < '2010-07-10'
order by change_log.change_log_id desc) s
group by client_id) a) notActive
|
|
| Jul 28, 2010 |
| Udai Gupta |
Client Exit information between two dates
select br.display_name as branch_name, c.customer_id as client_id, c.display_name as client_name, pargroup.display_name as group_name,
c.government_id as national_id, cl.changed_date as client_closed_date, cldreason.new_value as exit_reason, cn.comment as exit_notes,
ifnull(count(la.account_id), 0) as number_of_loans,
ifnull(sum(la.loan_amount), 0.0) as total_amount_disbursed, lo.display_name as loan_officer
from customer c
/*change_log records customer status changes. When a client is closed an extra change_log_detail entry is created to explain the status change*/
join change_log cl on cl.entity_id = c.customer_Id and cl.entity_type = 1
join change_log_detail cld on cld.change_log_id = cl.change_log_id and cld.field_name = 'Status' and cld.new_value = 'Closed'
join change_log_detail cldreason on cldreason.change_log_id = cl.change_log_id and cldreason.field_name = 'Status Change Explanation'
join customer_note cn on cn.customer_id = c.customer_id
and cn.comment_id = (select max(cnlast.comment_id)
from customer_note cnlast
where cnlast.customer_id = cn.customer_id)
join personnel lo on lo.personnel_id = c.loan_officer_id
join office br on br.office_id = c.branch_id
left join customer pargroup on pargroup.customer_id = c.parent_customer_id
left join account a on a.customer_id = c.customer_id and a.account_type_id = 1
/*loans matching the account_state_id below would have been disbursed */
and a.account_state_id >= 5 /* has been active in good standing at one time */
and a.account_state_id <> 10 /* but not cancelled*/
left join loan_account la on la.account_id = a.account_id
where c.status_id = 6 /*closed clients */
and cl.changed_date between '2009-01-01' and '2011-01-01'
group by c.customer_id
order by br.display_name, pargroup.display_name, c.display_name
|
|
| Jan 13, 2011 |
| John woodlock |
i want list of all clients on particular date disbursement amount, according to its loan fee, mem fee and insurance fee. |
|
|
|
|
|
Active loan accounts as of current date of a particular client/group/center/branch/organization:
Select
office.display_name as OFFICE_NAME,
center.display_name as CENTER_NAME,
grp.display_name as GROUP_NAME,
client.display_name as CUSTOMER_NAME,
account.global_account_num as LOAN_ACCOUNT_NO
From office
Inner join customer client on client.branch_id=office.office_id
Inner join customer grp on grp.customer_id=client.parent_customer_id
Inner join customer center on center.customer_id=grp.parent_customer_id
Inner join account on account.customer_id=client.customer_id
Inner join loan_account on loan_account.account_id=account.account_id
Where account.account_state_id in (5,9)
And office.office_id=?
And center.customer_id=?
And grp.customer_id=?
And client.customer_id=?
Order by office.display_name,center.display_name,grp.display_name,client.display_name,account.account_id;
Note: The And statements are optional and are required when you want to view any specific client/center/group/office active loan accounts. |
|
| December 2010 |
| SunGard |
List of active savings accounts as of current date for a particular client/group/center/branch/organization: Select\\
office.display_name as OFFICE_NAME,\\
center.display_name as CENTER_NAME,\\
grp.display_name as GROUP_NAME,\\
client.display_name as CUSTOMER_NAME,\\
account.global_account_num as SAVINGS_ACCOUNT_NO\\
From office\\
Inner join customer client on client.branch_id=office.office_id\\
Inner join customer grp on grp.customer_id=client.parent_customer_id\\
Inner join customer center on center.customer_id=grp.parent_customer_id\\
Inner join account on account.customer_id=client.customer_id\\
Inner join savings_account on savings_account.account_id=account.account_id\\
Where account.account_state_id =16\\
And office.office_id=?\\
And center.customer_id=?\\
And grp.customer_id=?\\
And client.customer_id=?\\
Order by office.display_name,center.display_name,grp.display_name,client.display_name,account.account_id;Note: The And statements are optional and are required when you want to view any specific client/center/group/office active savings accounts |
|
|
|
|
|
|
|
|
|
|
|
Data Warehouse Query Repository
Queries listed here reflect the data model for the data warehouse being released with Leila E.
Query | Additional Description | Applies to Data Warehouse Version | Date Added | Requested By | Submitted By |
|---|---|---|---|---|---|
No. of active (in good and bad standing) loans for an mfi:
select count(distinct(l.loan_account_id)) as active_loans
from dim_loan l
where l.loan_status in ('AccountState-ActiveInGoodStanding', 'AccountState-ActiveInBadStanding')
and l.valid_from <= curdate() and l.valid_to > curdate()
|
|
| Nov 11, 2010 |
| John Woodlock |
Active (and on hold) Clients on a Particular Day
select count(*)
from dim_customer
where customer_status in ('ClientStatus-Active', 'ClientStatus-OnHold')
and valid_from <= '2010-07-10' and valid_to > '2010-07-10'
|
|
| Nov 7, 2010 |
| John Woodlock |
|
|
|
|
|
|