Can I use Oracle SQL to plot actual dates from Schedule Information?

Question

I asked this question in regard to SQL Server, but what's the answer for an Oracle environment (10g)?

If I have a table containing schedule information that implies particular dates, is there a SQL statement that can be written to convert that information into actual rows, using something like MSSQL's Commom Table Expressions, perhaps?

Consider a payment schedule table with these columns:

• StartDate - the date the schedule begins (1st payment is due on this date)
• Term - the length in months of the schedule
• Frequency - the number of months between recurrences
• PaymentAmt - the payment amount :-)

SchedID  StartDate    Term  Frequency  PaymentAmt
-------------------------------------------------
1        05-Jan-2003  48    12         1000.00 
2        20-Dec-2008  42    6          25.00

Is there a single SQL statement to allow me to go from the above to the following?

                              Running
SchedID Payment  Due          Expected
        Num      Date         Total
--------------------------------------
1       1        05-Jan-2003  1000.00
1       2        05-Jan-2004  2000.00
1       3        05-Jan-2005  3000.00
1       4        05-Jan-2006  4000.00
2       1        20-Dec-2008  25.00
2       2        20-Jun-2009  50.00
2       3        20-Dec-2009  75.00
2       4        20-Jun-2010  100.00
2       5        20-Dec-2010  125.00
2       6        20-Jun-2011  150.00
2       7        20-Dec-2011  175.00

Your thoughts are appreciated.

Answer 1

Oracle actually has syntax for hierarchical queries using the CONNECT BY clause. SQL Server's use of the WITH clause looks like a hack in comparison:

    SELECT t.SchedId,
           CASE LEVEL
             WHEN 1 THEN
               t.StartDate
             ELSE
               ADD_MONTHS(t.StartDate, t.frequency)
             END 'DueDate',
           CASE LEVEL
             WHEN 1 THEN
               t.PaymentAmt
             ELSE
               SUM(t.paymentAmt)
           END 'RunningExpectedTotal'
      FROM PaymentScheduleTable t
     WHERE t.PaymentNum <= t.Term / t.Frequency
CONNECT BY PRIOR t.startdate = t.startdate
  GROUP BY t.schedid, t.startdate, t.frequency, t.paymentamt
  ORDER BY t.SchedId, t.PaymentNum

I'm not 100% on that - I'm more confident about using:

    SELECT t.SchedId,
           t.StartDate 'DueDate',
           t.PaymentAmt 'RunningExpectedTotal'
      FROM PaymentScheduleTable t
     WHERE t.PaymentNum <= t.Term / t.Frequency
CONNECT BY PRIOR t.startdate = t.startdate
  ORDER BY t.SchedId, t.PaymentNum

...but it doesn't include the logic to handle when you're dealing with the 2nd+ entry in the chain to add months & sum the amounts. The summing could be done with GROUP BY CUBE or ROLLUP depending on the detail needed.

Answer 2

I don't understand why 5 payment days for schedid = 1 and 7 for scheid = 2?

48 /12 = 4 and 42 / 6 = 7. So I expected 4 payment days for schedid = 1.

Anyway I use the model clause:

create table PaymentScheduleTable 
( schedid   number(10)
, startdate date
, term      number(3)
, frequency number(3)
, paymentamt number(5)
);

insert into PaymentScheduleTable 
values (1,to_date('05-01-2003','dd-mm-yyyy')
, 48
, 12
, 1000);

insert into PaymentScheduleTable 
values (2,to_date('20-12-2008','dd-mm-yyyy')
, 42
, 6
, 25);

commit;

And now the select with model clause:

select schedid, to_char(duedate,'dd-mm-yyyy') duedate, expected, i paymentnum
from   paymentscheduletable
model  
partition by (schedid)
dimension by (1 i)
measures (
  startdate duedate
, paymentamt expected
, term 
, frequency) 
rules 
( expected[for i from 1 to term[1]/frequency[1] increment 1] 
  = nvl(expected[cv()-1],0) + expected[1]
, duedate[for i from 1 to term[1]/frequency[1] increment 1] 
  = add_months(duedate[1], (cv(i)-1) * frequency[1])
)
order by schedid,i;

This outputs:

   SCHEDID DUEDATE      EXPECTED PAYMENTNUM
---------- ---------- ---------- ----------
         1 05-01-2003       1000          1
         1 05-01-2004       2000          2
         1 05-01-2005       3000          3
         1 05-01-2006       4000          4
         2 20-12-2008         25          1
         2 20-06-2009         50          2
         2 20-12-2009         75          3
         2 20-06-2010        100          4
         2 20-12-2010        125          5
         2 20-06-2011        150          6
         2 20-12-2011        175          7


11 rows selected.

Answer 3

I didn't set out to answer my own question, but I'm doing work with Oracle now and I have had to learn some new Oracle-flavored things.

Anyway, the CONNECT BY statement is really nice--yes, much nicer than MSSQL's hierchical query approach, and using that construct, I was able to produce a very clean query that does what I was looking for:

SELECT DISTINCT
     t.SchedID
    ,level as PaymentNum
    ,add_months(T.StartDate,level - 1) as DueDate
    ,(level * t.PaymentAmt) as RunningTotal
FROM SchedTest t
CONNECT BY level <= (t.Term / t.Frequency)
ORDER BY t.SchedID, level

My only remaining issue is that I had to use DISTINCT because I couldn't figure out how to select my rows from DUAL (the affable one-row Oracle table) instead of from my table of schedule data, which has at least 2 rows. If I could do the above with FROM DUAL, then my DISTINCT indicator wouldn't be necessary. Any thoughts?

Other than that, I think this is pretty nice. Et tu?