Home » SQL & PL/SQL » SQL & PL/SQL » Created schedule based on date conditions (12.1.0.1 )
Created schedule based on date conditions [message #689390] Wed, 06 December 2023 02:28 Go to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Dear All,

I have the following case related to information about loans and the way to create a forecasted withdrawal schedule for each loan based on its dates and next year forecast:
1- WTD Expiry is the last date withdrawals can be made and the loan should be completely withdrawn before or on this date.
2- Each loan has 2 dates for withdrawal installments annually. If the Bi-Annual Month is 2 (February), it means that withdrawals are on Feb and Aug every year until the WTD Expiry.
3- Y1 expected WTD, is an equation based forecast determining the expected withdrawals for the coming year.
4- Period after Y1: is the number of Bi-annual periods after the coming year and until the WTD expiry. In case of loan 2, there are 12 periods (6 years). In case of loan 3, there is 0 years left as the coming year is the last year before WTD Expiry. In case of loan 4, there is only one period after the coming year before WTD expiry (half a year).

What I need is to come up with the forecasted withdrawal schedule for each loan according to the following rules:
A- The coming year withdrawals are based on Y1 expected WTD and typically each loan should have 2 withdrawal dates in the coming year depending on its Bi-Annual Month as long as the WTD Expiry has not been exceeded. (case of loans 1, 2,4)
B- For the period after the coming year, the Period after Y1 is used to divide the remaining undisbursed amount after the coming year on the remaining periods equally. (case of loans 1, 2, 4)
C- In case the coming year is the last year until WTD Expiry, the 1Y Expected WTD is ignored and the undisbursed amount is divided on the remaining Bi-annual months until WTD Expiry. (case of loan 3, 5)
D- Period After Y1 can be negative indicating the the coming year is the last year until Expiry and that there is only 1 period left (-ve half a year) which means that the whole undisbursed amount should be placed in the next coming Bi-Annual month. (case of loan 5).

My case:
CREATE TABLE TEST_LOANS
(
  Loan_NO	NUMBER(3),
  Amount	NUMBER(15,3),
  undisbursed	NUMBER(15,3),
  Y5_WTD	NUMBER(15,3),
  Y1_WTD	NUMBER(15,3),
  WTD_Expiry	DATE,
  Bi_Annual_Month	NUMBER(1),
  Y1_expected_WTD	NUMBER(15,3),
  Period_after_Y1 NUMBER(3)
);

INSERT ALL 
INTO TEST_LOANS(Loan_NO, Amount, undisbursed, Y5_WTD, Y1_WTD, WTD_Expiry, Bi_Annual_Month, Y1_expected_WTD, Period_after_Y1) VALUES (1, 10000000,3000000,7000000,2000000, TO_DATE('01-01-2026','DD-MM-YYYY'),2,1760000,2)
INTO TEST_LOANS(Loan_NO, Amount, undisbursed, Y5_WTD, Y1_WTD, WTD_Expiry, Bi_Annual_Month, Y1_expected_WTD, Period_after_Y1) VALUES (2, 12000000,10005000,2000000,2000000, TO_DATE('01-09-2030','DD-MM-YYYY'),3,1360000,12)
INTO TEST_LOANS(Loan_NO, Amount, undisbursed, Y5_WTD, Y1_WTD, WTD_Expiry, Bi_Annual_Month, Y1_expected_WTD, Period_after_Y1) VALUES (3, 15000000,5000000,500000,4500000, TO_DATE('01-12-2024','DD-MM-YYYY'),6,2740000,0)
INTO TEST_LOANS(Loan_NO, Amount, undisbursed, Y5_WTD, Y1_WTD, WTD_Expiry, Bi_Annual_Month, Y1_expected_WTD, Period_after_Y1) VALUES (4, 1000000,750000,100000,650000, TO_DATE('01-01-2025','DD-MM-YYYY'),1,398000,0.5)
INTO TEST_LOANS(Loan_NO, Amount, undisbursed, Y5_WTD, Y1_WTD, WTD_Expiry, Bi_Annual_Month, Y1_expected_WTD, Period_after_Y1) VALUES (5, 15000000,5000000,500000,4500000, TO_DATE('01-06-2024','DD-MM-YYYY'),6,2740000,-0.5)

SELECT * FROM DUAL;

My expected result:
Quote:

Loan_no WTD_date Amount
1 01-02-24 440000
1 01-08-24 440000
1 01-02-25 1060000
1 01-08-25 1060000
2 01-03-24 680000
2 01-09-24 680000
2 01-03-25 720416.6667
2 01-09-25 720416.6667
2 01-03-26 720416.6667
2 01-09-26 720416.6667
2 01-03-27 720416.6667
2 01-09-27 720416.6667
2 01-03-28 720416.6667
2 01-09-28 720416.6667
2 01-03-29 720416.6667
2 01-09-29 720416.6667
2 01-03-30 720416.6667
2 01-09-30 720416.6667
3 01-06-24 5000000
3 01-12-24 5000000
4 01-01-24 199000
4 01-06-24 199000
4 01-01-25 352000
5 01-06-24 5000000
Thanks,
Ferro
Re: Created schedule based on date conditions [message #689391 is a reply to message #689390] Wed, 06 December 2023 15:52 Go to previous messageGo to next message
mathguy
Messages: 105
Registered: January 2023
Senior Member
I wasn't able to reconcile the "desired output" with the description you gave; please help me understand the problem - or, if you made mistakes, fix them.

For starters, let's look at the first year.

Look at Loan number 1. The Y1_EXPECTED_WTD is 1760000. This has to be withdrawn in two tranches in 2024, so that should be 880000 in each tranche. But your "desired output" shows 440000 in each tranche. Why is the Y1_EXPECTED_WTD divided by 4, and not by 2?

Loan number 2 seems OK: 1360000/2 = 680000 which is what your desired output shows.

Loan number 3 looks wrong. Coming year is its last year, so Y1_EXPECTED_YTD is to be ignored. The UNDISBURSED amount is 5000000. This should be disbursed in two tranches, so it should be 2500000 each. Your output shows 5000000 each - why?

I stopped there - let's see if I am understanding this 100% wrong, or if you made mistakes in the output (or the inputs), or in describing the problem.
Re: Created schedule based on date conditions [message #689392 is a reply to message #689391] Wed, 06 December 2023 21:57 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
@MathGuy

Good morning. Your understanding is correct as well as your comments. I corrected my sample as the expected results in both loans were incorrectly shown.


Loan_no WTD_date Amount
1 01-02-24 880000
1 01-08-24 880000
1 01-02-25 620000
1 01-08-25 620000
2 01-03-24 680000
2 01-09-24 680000
2 01-03-25 720416.6667
2 01-09-25 720416.6667
2 01-03-26 720416.6667
2 01-09-26 720416.6667
2 01-03-27 720416.6667
2 01-09-27 720416.6667
2 01-03-28 720416.6667
2 01-09-28 720416.6667
2 01-03-29 720416.6667
2 01-09-29 720416.6667
2 01-03-30 720416.6667
2 01-09-30 720416.6667
3 01-06-24 2500000
3 01-12-24 2500000
4 01-01-24 199000
4 01-06-24 199000
4 01-01-25 352000
5 01-06-24 5000000

Thanks
Re: Created schedule based on date conditions [message #689393 is a reply to message #689392] Thu, 07 December 2023 09:43 Go to previous messageGo to next message
mathguy
Messages: 105
Registered: January 2023
Senior Member
Here's one way to do this. The solution assumes that the numbers calculated in the PERIOD_AFTER_Y1 column are correct (they are in your sample data, assuming "current" date is in 2023).

select t.loan_no, t.wtd_expiry, t.bi_annual_month, t.undisbursed, t.y1_expected_wtd,
       l.wtd_date,
       case when l.wtd_no <= 2 then case when period_after_y1 <= 0 then undisbursed / (period_after_y1 + 2)
                                         else y1_expected_wtd / 2 end
            else (undisbursed - y1_expected_wtd) / period_after_y1 end as amount                             
from   test_loans t cross join lateral
       (
         select  level as wtd_no,
                 add_months(trunc(sysdate, 'yyyy'), bi_annual_month + 5 + 6 * level) as wtd_date
         from    dual
         connect by level <= period_after_y1 + 2
       ) l
order by loan_no, wtd_date    --  if needed
;
Re: Created schedule based on date conditions [message #689399 is a reply to message #689393] Fri, 08 December 2023 00:41 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
@MathGuy

Thanks a lot for your response.

I have the following comments:

1- Thanks for reminding people like me of the epic importance to plan the query and remember golden breakthroughs like the use of Lateral in joins to avoid multiple levels of queries to reach the same result. My trials were based on generating numbers from all_abjects and then filter them using another query not to mention the performance. Thanks again.
(SELECT ROWNUM  YEAR_NO FROM ALL_OBJECTS  WHERE  ROWNUM  <=  EXTRACT(YEAR FROM add_months(sysdate, 50*12)))

2- Quote:
assuming "current" date is in 2023
Correct but in case the current month in less than one of the bi-annual months, we have to start from the current year. I added the case below but please tell me if you have a better suggestion.
select t.loan_no, t.wtd_expiry, t.bi_annual_month, t.undisbursed, t.y1_expected_wtd,
       l.wtd_date,
       case when l.wtd_no <= 2 then case when period_after_y1 <= 0 then undisbursed / (period_after_y1 + 2)
                                         else y1_expected_wtd / 2 end
            else (undisbursed - y1_expected_wtd) / period_after_y1 end as amount                             
from   test_loans t cross join  lateral
       (
         select  level as wtd_no,
                 add_months(trunc(sysdate, 'yyyy'), bi_annual_month + 5 + 6 * level - (case when extract(month from sysdate) <=  bi_annual_month then 12 when extract(month from sysdate) <= bi_annual_month +6 then 6 else 0 end)) as wtd_date
         from    dual
         connect by level <= period_after_y1 + 2
       ) l 
order by loan_no, wtd_date    --  if needed
;
Thanks,
Ferro
Re: Created schedule based on date conditions [message #689400 is a reply to message #689399] Fri, 08 December 2023 11:03 Go to previous messageGo to next message
mathguy
Messages: 105
Registered: January 2023
Senior Member
in case the current month in less than one of the bi-annual months, we have to start from the current year.

Is this a new requirement? Or did you know about it from the outset? What's the point of posting "some of" the problem, if in the end you will need to share "all of" the problem anyway?

If you need help with the "general case", please state the problem in plain English, as you did in the original post. If I understand correctly, if the second payment month for a loan number has already passed in the current year, then we must do what you described in the original post. You need to state what must be shown if the "current date" is before even the FIRST payment month for a loan number in the current year, and what needs to be shown if the current date is after the first payment month but before the second payment month in the current year. Are there additional columns that must be considered (which were not in your original post)? Are the payments for the "current" year equal to those projected for the "next" year (Y1_EXPECTED_WTD)? Then, however they are calculated, do they need to be subtracted from the UNDISTRIBUTED amount for years two and later?

The way you described the problem in the original post was perfect (except for the errors in the output - which you fixed, no big deal - and except for being incomplete). If you can describe the general problem in the same way, that would be most helpful.

Also for clarification, since now SYSDATE may be before or after a payment date (in the current year - something we didn't have to worry about earlier), please state very clearly what "before" and "after" mean. Namely: if a payment month is August and SYSDATE is August 3, I assume the withdrawal is considered to have occurred already, and reflected already in the values in the table (for example in the UNDISTRIBUTED column). What if SYSDATE is August 1 though? And in that case, will it matter if the time-of-day is 00:00:00 vs. any time after midnight?
Re: Created schedule based on date conditions [message #689402 is a reply to message #689400] Sat, 09 December 2023 09:03 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
@MathGuy

Thanks for your care. It is a new requirement and I had similar frustration, however I carry part of it as I assumed that "coming year" means the next calendar year while what was meant is the next year starting from the first available Bi-Annual month. More analysis and questioning should've taken place (similar to the ones you raised).

So in case the first Bi-annual months is after current date (all dates are value date truncated without the need for time and for example 3rd of August means that no withdrawals can occur in August 1st as it already has passed), the first withdrawal takes place in the current year (with the same rule of distributing (Y1_EXPECTED_WTD) mentioned in the original post. In case the current date is between the first and the second Bi-Annual Months, the the first year withdrawal will also follow the same original rules but will start from the second bi-annual month (i.e. August 2023) and the second withdrawal of the first year will be Feb 2024.

For your comment:
Quote:
If you can describe the general problem in the same way, that would be most helpful.
What I am trying to do is to build a cash-flow forecast based on expected withdrawals. The general problem (if your mean the next step) is to combine the required output (cash-out) with installments and interest rate to calculate the cash-in part. I was planning to post this part as a separate case.
Re: Created schedule based on date conditions [message #689403 is a reply to message #689402] Sun, 10 December 2023 10:37 Go to previous messageGo to next message
mathguy
Messages: 105
Registered: January 2023
Senior Member
OK, let's see if I understand this correctly.

In the inputs, the following columns play no role in the problem as you described it. (They may still be needed for other things, but not for the problem at hand.)

AMOUNT (total amount of the loan, before any withdrawals were ever taken).
Y5_WTD
Y1_WTD

Moreover, PERIOD_AFTER_Y1 might be useful, but it is best for it not to be stored; it should be calculated in the query, because it clearly depends on when the query is run (if the query depends on SYSDATE).

The requirement is: Using the other columns in the table, and referencing SYSDATE (or some other reference date), create a schedule of withdrawals for each loan. If the reference date is past the EXPIRY date for a loan, don't include that loan in the output (even if there is still an UNDISBURSED amount greater than zero, etc. - that can't be correct). Actually, a loan may not appear in the output even if SYSDATE is before the expiry date, as long as between SYSDATE and the expiry date there's no loan withdrawal date for that loan. (Example: SYSDATE is in December 2023, expiry is 01-March-2024, but the bi-annual-month is 5, meaning that withdrawals are in May and November; the loan SHOULD have been withdrawn in its entirety by 01-November-2023, given these inputs.) As an aside, it's not clear to me why such cases are even possible; why the expiry date isn't always one of the withdrawal dates for the loan. You may want to consult with your business users again to understand this - to make sure they won't change the problem requirements again.

The schedule rules are similar to what you described initially, but the first withdrawal date is not necessarily "next year"; it is the first withdrawal date for that loan, based on BI_ANNUAL_MONTH and SYSDATE. And Y1_EXPECTED_WTD simply means "for the first two withdrawals" - unless there are two or fewer withdrawals remaining before expiry date, in which case Y1_EXPECTED_WTD is ignored and instead we only use UNDISBURSED.

With this formulation, I came up with the following solution.

I added a subquery in the WITH clause to create the "reference date" used everywhere in the main query. This can be SYSDATE as in the code shown below, but it can be changed to something else. This is useful for testing, if not for other things. (It can perhaps also be used for projections for fixed dates, rather than SYSDATE.)

Also in the WITH clause I pre-calculate the first withdrawal date for each loan (depending on the reference date and on BI_ANNUAL_MONTH).

In the main query you will notice that I changed the alias for the lateral query to L (from l); this is to avoid any confusion between l (lower-case "ell") and 1 (the digit "one").

with
  user_inputs (ref_date) as (
    select sysdate from dual
  )
, prep (loan_no, undisbursed, wtd_expiry, bi_annual_month, y1_expected_wtd, first_wtd_date) as (
    select loan_no, undisbursed, wtd_expiry, bi_annual_month, y1_expected_wtd,
           add_months(trunc(ref_date, 'yyyy'), bi_annual_month +
               case when extract(month from ref_date) < bi_annual_month     then -1
                    when extract(month from ref_date) < bi_annual_month + 6 then  5
                    else                                                         11
               end
           )
    from   test_loans cross join user_inputs
  )
select p.loan_no, p.wtd_expiry, p.bi_annual_month, p.undisbursed, p.y1_expected_wtd,
       L.wtd_date, L.wtd_no,
       case when L.wtd_no <= 2 then case when L.ct <= 2 then p.undisbursed/L.ct
                                         else                p.y1_expected_wtd/2
                                    end
            else                    (p.undisbursed - p.y1_expected_wtd)/(L.ct - 2)
       end as amount                             
from   prep p cross join lateral
       (
         select  level as wtd_no, count(*) over () as ct,
                 add_months(p.first_wtd_date, 6 * (level - 1)) as wtd_date
         from    dual
         connect by level <= months_between(wtd_expiry, first_wtd_date)/6 + 1
       ) L
order  by loan_no, wtd_date
;


Re: Created schedule based on date conditions [message #689407 is a reply to message #689403] Tue, 12 December 2023 04:42 Go to previous message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Thanks a lot @MathGuy you've been of great help.

For your comment:
Quote:

In the inputs, the following columns play no role in the problem as you described it. (They may still be needed for other things, but not for the problem at hand.)

AMOUNT (total amount of the loan, before any withdrawals were ever taken).
Y5_WTD
Y1_WTD
Correct, they are part of the parameters of the equation that calculates the next year withdrawals and are also part of the general case of building the cash-flow, but none are used in this case.

[Updated on: Tue, 12 December 2023 04:47]

Report message to a moderator

Previous Topic: sql Qurey
Next Topic: Help need in writing query to get JSON format output
Goto Forum:
  


Current Time: Thu Feb 29 13:36:09 CST 2024