i want the earliest unpaid installment, but i am not sure if loan_schedule.payment_status = 0 is ok because, i guess, that will retrieve ALL records before the loan_schedule.action_date and i jus t want the earliest date before action_date, but i don't know exactly how mifos works that.
mmm, sometimes posting on developer and send me the user, and sometimes posting on user and developers send me =S thank you 2010/5/30 John Woodlock <john.woodl...@gmail.com> > Shekko, > > You might want to post further query questions to the developer list... > although sometimes the answer isn't so technical so it would depend on > context probably. > > And depending on your intentions... I think you have a few things to look > at in your query. > > min(loan_schedule.action_date) > This is ok if you want to find the the first installment date. More usual > is to want to find the earliest unpaid installment (use > loan_schedule.payment_status = 0 in the where clause) > > you reference a lot of loan_schedule amount fields... as you are grouping > by account_id I'm assuming you meant to put a sum() or other aggregate > around them. > > "for (principal due, interest due, fees due, penalty due)" > This is usually when someone wants to find out how much is due as of a > certain date:- > something like this is typical (can't say it will match exactly what you > want): > > select account.account_id, > sum(loan_schedule.principal - loan_schedule.principal_paid) "principal > due", > sum(loan_schedule.interest - loan_schedule.interest_paid) "interest due", > sum(loan_schedule.misc_fees - loan_schedule.misc_fees_paid) "misc. fees > due", > sum(loan_schedule.misc_penalty - loan_schedule.misc_penalty_paid) "misc. > penalty due", > sum(loan_fee_schedule.amount - loan_fee_schedule.amount_paid) "periodic > fees due" > from account > join loan_schedule on account.account_id = loan_schedule.account_id > left join loan_fee_schedule on loan_fee_schedule.id = loan_schedule.id > where loan_schedule.action_date <= '2010-05-29' and > loan_schedule.payment_status = 0 > and account.account_state_id in (5,9) /*active and on-hold loan > accounts*/ > group by account.account_id > > > John > > > On Fri, May 28, 2010 at 10:48 AM, Shekko <krilin.checo.ser...@gmail.com>wrote: > >> ok this is my query >> select >> >> account.account_id,reg.display_name,office.display_name,customer.global_cust_num,customer.display_name,account.global_account_num,account.external_id, >> >> loanproduct.prd_offering_short_name,loanproduct.prd_offering_name,la.disbursement_date,loan_summary.orig_principal, >> >> loan_schedule.principal,loan_schedule.interest,min(loan_schedule.action_date),loan_schedule.misc_fees,loan_schedule.penalty, >> (loan_schedule.principal+loan_schedule.interest+loan_schedule.misc_fees >> +loan_schedule.penalty) saldo_vigente >> from account >> join office on account.office_id = office.office_id >> join office reg on office.parent_office_id = reg.office_id >> join customer on account.customer_id = customer.customer_id >> join loan_account la on la.account_id = account.account_id >> left join prd_offering loanproduct on loanproduct.prd_offering_id = >> la.prd_offering_id >> join loan_summary on account.account_id = loan_summary.account_id >> join loan_schedule on account.account_id = loan_schedule.account_id >> where loan_schedule.action_date >= '2010-06-18' >> group by account.account_id >> order by account.account_id asc >> >> also i need include: >> principal due, interest due, fees due, penalty due (all due, but by >> every entry) >> >> >> i really thank you >> >> On 27 mayo, 09:44, Adam Monsen <hair...@gmail.com> wrote: >> > > there is any model database documentation >> > >> > Yes! >> > >> > Seehttp://ci.mifos.org/schema/ >> > >> > (http://article.gmane.org/gmane.comp.finance.mifos.devel/8680) >> > >> > signature.asc >> > < 1 KBVerDescargar >> > >> > >> ------------------------------------------------------------------------------ >> > >> > _______________________________________________ >> > Mifos-users mailing list >> > mifos-us...@lists.sourceforge.nethttps:// >> lists.sourceforge.net/lists/listinfo/mifos-users >> >> >> ------------------------------------------------------------------------------ >> >> _______________________________________________ >> Mifos-users mailing list >> Mifos-users@lists.sourceforge.net >> https://lists.sourceforge.net/lists/listinfo/mifos-users >> > > > > ------------------------------------------------------------------------------ > > > _______________________________________________ > Mifos-users mailing list > Mifos-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/mifos-users > >
------------------------------------------------------------------------------
_______________________________________________ Mifos-users mailing list Mifos-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/mifos-users
