Hi Mike,

Thank you for sharing all this with us.

... but when I see an SQL like that I think that time has come to revert to more traditional procedural coding that at least allows proper documentation.

Or is it just me?

Marc



>From time to time we all hit a really knotty patch. The following is offered as one possible solution for selecting overdue invoices so that they can be charged interest.
>
>Our customer has an extremely punitive requirement for the calculation of interest. In fact, that was what got us the jobb in the first place. They levy tonnage assessments on behalf of employers and the dockworker unions in the Port of Halifax, and to keep potential delinquents in line the interest calculation scheme is very harsh.
>
>Once a ship completes it's cargo the agent has 37 days to pay the assessment. If payment is missed, by as much as 1 day (pay on day 38), interest is calculated on the first period, and is also imposed for the second period. If the invoice continues unpaid, then interest is charged for each additional 30 day period or part thereof.
>
>The situation is further complicated by a difference in the length of the first period (37 days), and subsequent periods (30 days). Commercial accounting programs don't accommodate this scenario. The claim that "Sure, this can be done, no sweat." was a major factor in landing the work.
>
>Nick Nekloiudov (well known to UT members) and I tackled this problem, trying to devise a SQL statement which would reliably return a list of invoices on which interest had to be calculated. The harder we worked the bigger and more unwieldy amd unreliable the statement became. And it was always wrong, somewhere. Crunch time was coming.
>
>Finally I drew up a chart which showed all the possible payment and calculation combinations for 8 possible scenarios and e-mailed it to Nick. He couldn't make sense of it, called me up, and forced me to explain myself. From that he derived a beautifully elegant solution.
>
>Its essence is simple: determine the number of days until an invoice is paid. Subtract 37 days from that, divide the remainder by 30, force it to its ceiling, and add 1. If the result is 1, then no interest invoice need be calculated. Anything else gives the number of interest invoices which should be calculated for that invoice. So - this was a great way to start the process. Now how to stop it?
>
>A test against the number of times the invoice in question has been included in one or more interest invoices does that. When an interest invoice is generated the keys for all the invoices on which interest has been calculated are stored in an interest details table. Two subqueries are used to a) count the number of instances for each invoice number, or b)determine if any interest invoices have been created at all.
>
>This solution is extremely flexible. If the customer changes the duration of the initial 37 day period that information is stored in a table and can be changed by the user. There is NO mucking about determining where months begin or end, fitting invoices within a period, etc. Better yet - if the process is run twice in one month, so what? It's self limiting. That's one less error message to irritate the user.
>
>SO - I'd like to publicly say "Well done Nick!", and here's the code:
>
>

>* Create__lv_IntCalcSource
>* CQA Consulting  19990119 mthompson & nneklioudov
>* Nick Neklioudov provided the inspiration and the initial code
>* breaking out and counting the periods.
>* Miles Thompson then grubbed around with subqueries
>* until they worked correctly.
>
>create sql view lv_intcalcsource as ;
>select InvHead.cinvoi_key, ;
>	InvHead.cInvoiceNo, ;
>	InvHead.cInvoiceType, ;
>	Company.cCompanyName, ;
>	InvHead.cVesselName, ;
>	InvHead.dCompletionDate, ;
>	InvHead.lInterest, ;
>	InvHead.lDummy, ;
>	InvHead.yTotal, ;
>	InvHead.cCompa_Key, ;
>	InvHead.cTaxAcc, ;
>	InvHead.cAdminAcc, ;
>	InvHead.cPenRevAcc, ;
>	(InvHead.dDatePaid - InvHead.dCompletionDate) DaysToPay, ;
>	(ceiling( (( InvHead.dDatePaid - InvHead.dCompletionDate ) ;
>		- IntCalc.ic_days) / 30 ) + 1) InvReqd ;
> from hea!InvHead inner join hea!Company ;
>   on  InvHead.cCompa_Key = Company.cCompa_Key ;
> where ;
>    ( (InvHead.dDatePaid - InvHead.dCompletionDate) > IntCalc.ic_days .and. .not. lFullyPaid ) ;
>     .or. ;
>    ( lFullyPaid .and. ;
>      ceiling( (( InvHead.dDatePaid - InvHead.dCompletionDate ) - IntCalc.ic_days) / 30 ) + 1 > ;
>                   (select count(*) from IntDet where InvHead.cInvoi_Key = cDet_InvoiKey) ) ;
>     .or. ;
>     ( lFullyPaid .and. ;
>     ( ceiling( (( InvHead.dDatePaid - InvHead.dCompletionDate ) - IntCalc.ic_days) / 30 ) + 1 > 1 ) .and. ;
>      not exists (select * from IntDet where InvHead.cInvoi_Key = cDet_InvoiKey) ) ;
>  order by Company.cCompanyName, InvHead.dCompletionDate
>

>
>I probably went overboard with brackets, but I was getting a bit paranoid. lDummy is used in the grid this beastie feeds to check off whether or not the invoice should be charged with interest. The fields DaysToPay and InvReqd are there for diagnostic purposes. With a few changes this would also be suitable for one time calculations of compound interest.
>
>Nick and I hope this has been of interest and may be of use to someone who is facing a similar situation.
>
>Lessons? Keep things modular, when stymied go back to basics, never be reluctant to swallow your pride and go talk something over.