Page Comparison

select count(*) as loans_currently_in_good_and_bad_standing
from account a
where a.account_state_id in (5,9)

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 Branch

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

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

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

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

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