[prev in list] [next in list] [prev in thread] [next in thread]
List: mifos-developer
Subject: Re: [Mifos-developer] Interest Outstanding Report
From: Sughosh Bharadwaj <sughosh () confluxtechnologies ! com>
Date: 2016-07-25 12:44:32
Message-ID: CACfgXutoo_93s=kkdADsaAT1kbaJ5b0NUvvMdF==_euuhj6yWQ () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/related)]
[Attachment #4 (multipart/alternative)]
Hello Zayyad,
May be this helps.
select
ifnull(l.interest_charged_derived,0) -
(select sum(ifnull(interest_portion_derived,0) interestOS
from m_loan_transaction t
where t.loan_id = l.id
and t.transaction_type_enum=2
and t.is_reversed=0
and t.transaction_date <= 'asOndate')
from m_loan l
where l.loan_status_id in (300,600,601,700)
Regards,
Sughosh
On Mon, Jul 25, 2016 at 1:01 PM, Shreyaank Byadagi <
shreyaank@confluxtechnologies.com> wrote:
> Dear Zayyad,
>
> It is not that tricky also :)
>
> In m_loan, you get the total interest charged (*interest_charged_derived*)
> and from m_loan_transaction you get *interest_portion_derived*, just use
> below logic
>
> m_loan.interest_charged_derived -
> sum(m_loan_transaction.interest_portion_derived) where
> m_loan_transaction.transaction_date <= <given date>
>
> Let me know if still need clarification.
>
>
>
>
> On Mon, Jul 25, 2016 at 4:09 PM, Zayyad A. Said <
> zayyad@intrasofttechnologies.com> wrote:
>
>> Dear Shreyaank,
>>
>>
>>
>> I had seen the columns on *m_loan_repayment_schedule *but noticed that I
>> could only get latest position from them.
>>
>>
>>
>> The challenge is on getting *interest outstanding as of a given date*
>> which yes it's tricky with the current structure.
>>
>>
>>
>> I thought there was a workaround that I didn't know about.
>>
>>
>>
>> Perhaps this could be a trigger to make us include this column in future
>> releases as it's a need of most MFIs when it comes to Performance
>> Management.
>>
>>
>>
>> Thanks & Regards;
>>
>>
>>
>> *********
>>
>> *Zayyad A. Said | Chairman & C.E.O*
>>
>>
>>
>> Cell No.: +254 716 615274 | Skype: *zsaid2011*
>>
>> Email: zayyad@intrasofttechnologies.com
>>
>>
>>
>> [image: Email banner]
>>
>>
>>
>> *From:* Shreyaank Byadagi [mailto:shreyaank@confluxtechnologies.com]
>> *Sent:* 25 July 2016 13:20
>> *To:* Mifos software development
>> *Subject:* Re: [Mifos-developer] Interest Outstanding Report
>>
>>
>>
>> Zayyad,
>>
>> You can derive the interest outstanding from *m_loan_repayment_schedule*
>> table,
>>
>>
>>
>> Please refer the columns interest_amount, interest_completed_derived,
>> interest_writtenoff_derived, interest_waived_derived,
>>
>> basically interest out standing is sum(interest_amount -
>> interest_completed_derived - interest_writtenoff_derived -
>> interest_waived_derived) for given loan
>>
>> this will give the present/current interest outstanding, if you are
>> looking for as of given date then it will bit tricky :)
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> On Sun, Jul 24, 2016 at 7:25 PM, Zayyad A. Said <
>> zayyad@intrasofttechnologies.com> wrote:
>>
>> Devs,
>>
>>
>>
>> I am developing a report for a client who needs *Interest Outstanding*
>> as on a particular date that the user will select from the parameters.
>>
>>
>>
>> I don't seem to see a table that I can call this field in my SQL script.
>>
>>
>>
>> One of the table I thought would help is `m_loan_transaction` but I can
>> only get `outstanding_loan_balance_derived` which is the Principal balance.
>>
>>
>>
>> Is there any other workaround I can be able to extract outstanding
>> interest periodic wise?
>>
>>
>>
>> Your assistance will highly be appreciated.
>>
>>
>>
>> Regards;
>>
>>
>>
>>
>>
>> *********
>>
>> *Zayyad A. Said | Chairman & C.E.O*
>>
>>
>>
>> Cell No.: +254 716 615274 | Skype: *zsaid2011*
>>
>> Email: zayyad@intrasofttechnologies.com
>>
>>
>>
>> [image: Email banner]
>>
>>
>>
>>
>>
>> ------------------------------------------------------------------------------
>> What NetFlow Analyzer can do for you? Monitors network bandwidth and
>> traffic
>> patterns at an interface-level. Reveals which users, apps, and protocols
>> are
>> consuming the most bandwidth. Provides multi-vendor support for NetFlow,
>> J-Flow, sFlow and other flows. Make informed decisions using capacity
>> planning
>> reports.http://sdm.link/zohodev2dev
>> Mifos-developer mailing list
>> mifos-developer@lists.sourceforge.net
>> Unsubscribe or change settings at:
>> https://lists.sourceforge.net/lists/listinfo/mifos-developer
>>
>>
>>
>>
>> --
>>
>> Thanks and Regards
>>
>> Shreyaank
>>
>> E-mail:shreyaank@confluxtechnologies.com
>>
>>
>>
>>
>> ------------------------------------------------------------------------------
>> What NetFlow Analyzer can do for you? Monitors network bandwidth and
>> traffic
>> patterns at an interface-level. Reveals which users, apps, and protocols
>> are
>> consuming the most bandwidth. Provides multi-vendor support for NetFlow,
>> J-Flow, sFlow and other flows. Make informed decisions using capacity
>> planning
>> reports.http://sdm.link/zohodev2dev
>> Mifos-developer mailing list
>> mifos-developer@lists.sourceforge.net
>> Unsubscribe or change settings at:
>> https://lists.sourceforge.net/lists/listinfo/mifos-developer
>>
>
>
>
> --
> Thanks and Regards
> Shreyaank
> E-mail:shreyaank@confluxtechnologies.com
>
>
>
> ------------------------------------------------------------------------------
> What NetFlow Analyzer can do for you? Monitors network bandwidth and
> traffic
> patterns at an interface-level. Reveals which users, apps, and protocols
> are
> consuming the most bandwidth. Provides multi-vendor support for NetFlow,
> J-Flow, sFlow and other flows. Make informed decisions using capacity
> planning
> reports.http://sdm.link/zohodev2dev
> Mifos-developer mailing list
> mifos-developer@lists.sourceforge.net
> Unsubscribe or change settings at:
> https://lists.sourceforge.net/lists/listinfo/mifos-developer
>
[Attachment #7 (text/html)]
<div dir="ltr"><div>Hello Zayyad,<br><br></div>May be this helps.<br><br>select \
<br>ifnull(l.interest_charged_derived,0) - <br>(select \
sum(ifnull(interest_portion_derived,0) interestOS<br>from m_loan_transaction \
t<br>where t.loan_id = <a href="http://l.id">l.id</a><br>and \
t.transaction_type_enum=2<br>and t.is_reversed=0<br>and t.transaction_date <= \
'asOndate')<br>from m_loan l<br>where l.loan_status_id in \
(300,600,601,700)<br><br></div><div class="gmail_extra"><br clear="all"><div><div \
class="gmail_signature" data-smartmail="gmail_signature"><div \
dir="ltr"><div>Regards,<br>Sughosh<br></div></div></div></div> <br><div \
class="gmail_quote">On Mon, Jul 25, 2016 at 1:01 PM, Shreyaank Byadagi <span \
dir="ltr"><<a href="mailto:shreyaank@confluxtechnologies.com" \
target="_blank">shreyaank@confluxtechnologies.com</a>></span> \
wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px \
#ccc solid;padding-left:1ex"><div dir="ltr"><div><div><div>Dear \
Zayyad,<br><br></div>It is not that tricky also :) <br><br></div>In m_loan, you get \
the total interest charged (<b>interest_charged_derived</b>) and from \
m_loan_transaction you get <b>interest_portion_derived</b>, just use below \
logic<br><br>m_loan.interest_charged_derived - \
sum(m_loan_transaction.interest_portion_derived) where \
m_loan_transaction.transaction_date <= <given date><br><br></div>Let me know \
if still need clarification.<br><div><br><br><br></div></div><div class="HOEnZb"><div \
class="h5"><div class="gmail_extra"><br><div class="gmail_quote">On Mon, Jul 25, 2016 \
at 4:09 PM, Zayyad A. Said <span dir="ltr"><<a \
href="mailto:zayyad@intrasofttechnologies.com" \
target="_blank">zayyad@intrasofttechnologies.com</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div link="blue" vlink="purple" lang="EN-GB"><div><p \
class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Dear \
Shreyaank,<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">I \
had seen the columns on <b>m_loan_repayment_schedule </b>but noticed that I could \
only get latest position from them.<u></u><u></u></span></p><p \
class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">The \
challenge is on getting <b><u>interest outstanding as of a given date</u></b> which \
yes it's tricky with the current structure.<u></u><u></u></span></p><p \
class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">I \
thought there was a workaround that I didn't know about.<u></u><u></u></span></p><p \
class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Perhaps \
this could be a trigger to make us include this column in future releases as it's a \
need of most MFIs when it comes to Performance Management.<u></u><u></u></span></p><p \
class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Thanks \
& Regards;<u></u><u></u></span></p><span><p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> \
<u></u></span></p><p class="MsoNormal" style="background:white"><b><span \
style="font-family:"Cambria","serif";color:#222222" \
lang="EN-US">*******</span></b><span \
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#222222" \
lang="EN-US"><u></u><u></u></span></p><p class="MsoNormal" \
style="background:white"><b><span \
style="font-size:14.0pt;font-family:"Cambria","serif";color:#222222" \
lang="EN-US">Zayyad A. Said | Chairman & C.E.O</span></b><span \
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#222222" \
lang="EN-US"><u></u><u></u></span></p><p class="MsoNormal" \
style="line-height:10.5pt;background:white"><span \
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#222222" \
lang="EN-US"><u></u> <u></u></span></p><p class="MsoNormal" \
style="line-height:10.5pt;background:white"><span \
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#222222" \
lang="EN-US">Cell No.: <a href="tel:%2B254%20716%20615274" value="+254716615274" \
target="_blank">+254 716 615274</a> | Skype: \
<b>zsaid2011</b><u></u><u></u></span></p><p class="MsoNormal" \
style="line-height:10.5pt;background:white"><span \
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#222222" \
lang="EN-US">Email: <a href="mailto:zayyad@intrasofttechnologies.com" \
target="_blank">zayyad@intrasofttechnologies.com</a> <u></u><u></u></span></p><p \
class="MsoNormal"><span \
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#222222" \
lang="EN-US"><u></u> <u></u></span></p><p class="MsoNormal"><span \
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#222222"><img \
src="cid:image004.jpg@01D1E679.F5FFFA40" alt="Email banner" border="0" height="156" \
width="624"></span><span \
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u><u></u></span></p><p \
class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> \
<u></u></span></p></span><p class="MsoNormal"><b><span \
style="font-size:10.0pt;font-family:"Tahoma","sans-serif"" \
lang="EN-US">From:</span></b><span \
style="font-size:10.0pt;font-family:"Tahoma","sans-serif"" \
lang="EN-US"> Shreyaank Byadagi [mailto:<a \
href="mailto:shreyaank@confluxtechnologies.com" \
target="_blank">shreyaank@confluxtechnologies.com</a>] <br><b>Sent:</b> 25 July 2016 \
13:20<br><b>To:</b> Mifos software development<br><b>Subject:</b> Re: \
[Mifos-developer] Interest Outstanding Report<u></u><u></u></span></p><div><div><p \
class="MsoNormal"><u></u> <u></u></p><div><div><div><p \
class="MsoNormal">Zayyad,<br><br>You can derive the interest outstanding from \
<b>m_loan_repayment_schedule</b> table,<u></u><u></u></p></div><p \
class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal" \
style="margin-bottom:12.0pt">Please refer the columns interest_amount, \
interest_completed_derived, interest_writtenoff_derived, interest_waived_derived, \
<br><br>basically interest out standing is sum(interest_amount - \
interest_completed_derived - interest_writtenoff_derived - interest_waived_derived) \
for given loan<u></u><u></u></p></div><div><p class="MsoNormal" \
style="margin-bottom:12.0pt">this will give the present/current interest outstanding, \
if you are looking for as of given date then it will bit tricky \
:)<br><br><u></u><u></u></p></div><div><p class="MsoNormal"><u></u> \
<u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p \
class="MsoNormal" style="margin-bottom:12.0pt"><br><br><u></u><u></u></p></div></div><div><p \
class="MsoNormal"><u></u> <u></u></p><div><p class="MsoNormal">On Sun, Jul 24, 2016 \
at 7:25 PM, Zayyad A. Said <<a href="mailto:zayyad@intrasofttechnologies.com" \
target="_blank">zayyad@intrasofttechnologies.com</a>> \
wrote:<u></u><u></u></p><div><div><p class="MsoNormal">Devs,<u></u><u></u></p><p \
class="MsoNormal"> <u></u><u></u></p><p class="MsoNormal">I am developing a report \
for a client who needs <b>Interest Outstanding</b> as on a particular date that the \
user will select from the parameters.<u></u><u></u></p><p class="MsoNormal"> \
<u></u><u></u></p><p class="MsoNormal">I don't seem to see a table that I can call \
this field in my SQL script. <u></u><u></u></p><p class="MsoNormal"> \
<u></u><u></u></p><p class="MsoNormal">One of the table I thought would help is \
`m_loan_transaction` but I can only get `outstanding_loan_balance_derived` which is \
the Principal balance.<u></u><u></u></p><p class="MsoNormal"> <u></u><u></u></p><p \
class="MsoNormal">Is there any other workaround I can be able to extract outstanding \
interest periodic wise?<u></u><u></u></p><p class="MsoNormal"> <u></u><u></u></p><p \
class="MsoNormal">Your assistance will highly be appreciated.<u></u><u></u></p><p \
class="MsoNormal"> <u></u><u></u></p><p \
class="MsoNormal">Regards;<u></u><u></u></p><p class="MsoNormal"> \
<u></u><u></u></p><p class="MsoNormal"> <u></u><u></u></p><p class="MsoNormal" \
style="background:white"><b><span \
style="font-family:"Cambria","serif";color:#222222" \
lang="EN-US">*******</span></b><u></u><u></u></p><p class="MsoNormal" \
style="background:white"><b><span \
style="font-size:14.0pt;font-family:"Cambria","serif";color:#222222" \
lang="EN-US">Zayyad A. Said | Chairman & C.E.O</span></b><u></u><u></u></p><p \
class="MsoNormal" style="line-height:10.5pt;background:white"><span \
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#222222" \
lang="EN-US"> </span><u></u><u></u></p><p class="MsoNormal" \
style="line-height:10.5pt;background:white"><span \
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#222222" \
lang="EN-US">Cell No.: <a href="tel:%2B254%20716%20615274" value="+254716615274" \
target="_blank">+254 716 615274</a> | Skype: \
<b>zsaid2011</b></span><u></u><u></u></p><p class="MsoNormal" \
style="line-height:10.5pt;background:white"><span \
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#222222" \
lang="EN-US">Email: <a href="mailto:zayyad@intrasofttechnologies.com" \
target="_blank">zayyad@intrasofttechnologies.com</a> </span><u></u><u></u></p><p \
class="MsoNormal"><span \
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#222222" \
lang="EN-US"> </span><u></u><u></u></p><p class="MsoNormal"><span \
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#222222"><img \
src="cid:image003.jpg@01D1E679.F34CC120" alt="Email banner" border="0" height="156" \
width="624"></span><u></u><u></u></p><p class="MsoNormal"> \
<u></u><u></u></p></div></div><p \
class="MsoNormal"><br>------------------------------------------------------------------------------<br>What \
NetFlow Analyzer can do for you? Monitors network bandwidth and traffic<br>patterns \
at an interface-level. Reveals which users, apps, and protocols are<br>consuming the \
most bandwidth. Provides multi-vendor support for NetFlow,<br>J-Flow, sFlow and other \
flows. Make informed decisions using capacity planning<br>reports.<a \
href="http://sdm.link/zohodev2dev" \
target="_blank">http://sdm.link/zohodev2dev</a><br>Mifos-developer mailing list<br><a \
href="mailto:mifos-developer@lists.sourceforge.net" \
target="_blank">mifos-developer@lists.sourceforge.net</a><br>Unsubscribe or change \
settings at:<br><a href="https://lists.sourceforge.net/lists/listinfo/mifos-developer" \
target="_blank">https://lists.sourceforge.net/lists/listinfo/mifos-developer</a><u></u><u></u></p></div><p \
class="MsoNormal"><br><br clear="all"><br>-- <u></u><u></u></p><div><div><p \
class="MsoNormal">Thanks and Regards<u></u><u></u></p><div><p \
class="MsoNormal">Shreyaank<u></u><u></u></p></div><div><p \
class="MsoNormal">E-mail:<a href="mailto:shreyaank@confluxtechnologies.com" \
target="_blank">shreyaank@confluxtechnologies.com</a><u></u><u></u></p></div><div><p \
class="MsoNormal"><u></u> \
<u></u></p></div></div></div></div></div></div></div></div><br>------------------------------------------------------------------------------<br>
What NetFlow Analyzer can do for you? Monitors network bandwidth and traffic<br>
patterns at an interface-level. Reveals which users, apps, and protocols are<br>
consuming the most bandwidth. Provides multi-vendor support for NetFlow,<br>
J-Flow, sFlow and other flows. Make informed decisions using capacity planning<br>
reports.<a href="http://sdm.link/zohodev2dev" rel="noreferrer" \
target="_blank">http://sdm.link/zohodev2dev</a><br>Mifos-developer mailing list<br> \
<a href="mailto:mifos-developer@lists.sourceforge.net" \
target="_blank">mifos-developer@lists.sourceforge.net</a><br> Unsubscribe or change \
settings at:<br> <a href="https://lists.sourceforge.net/lists/listinfo/mifos-developer" \
rel="noreferrer" target="_blank">https://lists.sourceforge.net/lists/listinfo/mifos-developer</a><br></blockquote></div><br><br \
clear="all"><br>-- <br><div data-smartmail="gmail_signature"><div dir="ltr">Thanks \
and Regards<div>Shreyaank</div><div>E-mail:<a \
href="mailto:shreyaank@confluxtechnologies.com" \
target="_blank">shreyaank@confluxtechnologies.com</a></div><div><br></div></div></div>
</div>
</div></div><br>------------------------------------------------------------------------------<br>
What NetFlow Analyzer can do for you? Monitors network bandwidth and traffic<br>
patterns at an interface-level. Reveals which users, apps, and protocols are<br>
consuming the most bandwidth. Provides multi-vendor support for NetFlow,<br>
J-Flow, sFlow and other flows. Make informed decisions using capacity planning<br>
reports.<a href="http://sdm.link/zohodev2dev" rel="noreferrer" \
target="_blank">http://sdm.link/zohodev2dev</a><br>Mifos-developer mailing list<br> \
<a href="mailto:mifos-developer@lists.sourceforge.net">mifos-developer@lists.sourceforge.net</a><br>
Unsubscribe or change settings at:<br>
<a href="https://lists.sourceforge.net/lists/listinfo/mifos-developer" \
rel="noreferrer" target="_blank">https://lists.sourceforge.net/lists/listinfo/mifos-developer</a><br></blockquote></div><br></div>
--001a113e35b0b6d69c05387527d2--
["image004.jpg" (image/jpeg)]
["image003.jpg" (image/jpeg)]
------------------------------------------------------------------------------
What NetFlow Analyzer can do for you? Monitors network bandwidth and traffic
patterns at an interface-level. Reveals which users, apps, and protocols are
consuming the most bandwidth. Provides multi-vendor support for NetFlow,
J-Flow, sFlow and other flows. Make informed decisions using capacity planning
reports.http://sdm.link/zohodev2dev
Mifos-developer mailing list
mifos-developer@lists.sourceforge.net
Unsubscribe or change settings at:
https://lists.sourceforge.net/lists/listinfo/mifos-developer
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic