Can I use SQL to plot actual dates based on schedule information?

Question

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 some sort of CROSS JOIN, 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
1       5        05-Jan-2007  5000.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

I'm using MS SQL Server 2005 (no hope for an upgrade soon) and I can already do this using a table variable and while loop, but it seemed like some sort of CROSS JOIN would apply but I don't know how that might work.

Your thoughts are appreciated.

EDIT: I'm actually using SQL Server 2005 though I initially said 2000. We aren't quite as backwards as I thought. Sorry.

Answer 1

I cannot test the code right now, so take it with a pinch of salt, but I think that something looking more or less like the following should answer the question:

with q(SchedId, PaymentNum, DueDate, RunningExpectedTotal) as
    (select SchedId,
            1 as PaymentNum,
            StartDate as DueDate,
            PaymentAmt as RunningExpectedTotal
     from PaymentScheduleTable
     union all
     select q.SchedId,
             1 + q.PaymentNum as PaymentNum,
             DATEADD(month, s.Frequency, q.DueDate) as DueDate,
             q.RunningExpectedTotal + s.PaymentAmt as RunningExpectedTotal
     from q
          inner join PaymentScheduleTable s
                  on s.SchedId = q.SchedId
     where q.PaymentNum <= s.Term / s.Frequency)
select *
from q
order by SchedId, PaymentNum

Answer 2

Try using a table of integers (or better this: http://www.sql-server-helper.com/functions/integer-table.aspx) and a little date math, e..g. start + int * freq