SQL Query Repository

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

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

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

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

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

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

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;

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;

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()

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'