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've used table-valued functions to achieve a similar result. Basically the same as using a table variable I know, but I remember being really pleased with the design.

The usage ends up reading very well, in my opinion:

/* assumes @startdate and @enddate schedule limits */

SELECT
   p.paymentid,
   ps.paymentnum,
   ps.duedate,
   ps.ret
FROM
   payment p,
   dbo.FUNC_get_payment_schedule(p.paymentid, @startdate, @enddate) ps
ORDER BY p.paymentid, ps.paymentnum

Answer 2

A typical solution is to use a Calendar table. You can expand it to fit your own needs, but it would look something like:

CREATE TABLE Calendar
(
     calendar_date DATETIME NOT NULL,
     is_holiday BIT NOT NULL DEFAULT(0),
     CONSTRAINT PK_Calendar PRIMARY KEY CLUSTERED calendar_date
)

In addition to the is_holiday you can add other columns that are relevant for you. You can write a script to populate the table up through the next 10 or 100 or 1000 years and you should be all set. It makes queries like that one that you're trying to do much simpler and can give you additional functionality.

Answer 3

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

Answer 4

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